As @mvp notes, if COMMIT slow, the usual reason is slow fsync() , because each transactional commit should clear the data on disk - usually with fsync (). However, this is not the only possible reason for slow commits. You can:
- have slow fsync () s, as already noted
- have slow control points stopping I / O
- has a set of
commit_delay - I have not yet confirmed that delayed commits are logged as long statements, but it seems reasonable.
If fsync () is slow, the best option is to restructure your work so that you can run it through smaller transactions. A reasonable alternative would be to use the commit_delay to group; this will group to improve overall throughput, but will actually slow down individual transactions.
Better yet, fix the root of the problem. Go to a RAID controller with a backup battery write-back cache or high-quality solid state drives that are safe for power. See, regular drives can usually run less than one fsync () per turn, or between 5400 and 15,000 per minute, depending on the hard drive. With a lot of transactions and a lot of commits, this will significantly reduce your throughput, especially since the best case if all they do is trivial flushes. In contrast, if you have a reliable write cache on a RAID controller or SSD, the OS does not need to check whether the data is really on the hard drive, you just need to make sure that it has reached the long-term write cache - massively faster, because usually it just some RAM protected RAM.
Perhaps fsync () is not a real problem; It can be slow checkpoints . The best way to see is to check the logs to see if there are any complaints about security checkpoints that take place too often or too long. You can also enable log_checkpoints to record how long and how often breakpoints pass.
If breakpoints take too long, consider setting up a bgwriter completion target (see docs). If they are too frequent, increase checkpoint_segments .
See Configuring your PostgreSQL server for more information.