From time to time, we get complaints on the pgsql-performance mailing list about a PostgreSQL database that - for the most part - performs reasonably well, but every once in a while it becomes unresponsive for some number of seconds or even minutes; and then eventually recovers. What's going on?
These kinds of problems are almost always called by a temporary spike in disk I/O, which can usually be tracked back to one of two root causes: either (1) the system is under heavy virtual memory pressure, and therefore has begun swapping, or (2) checkpoint activity is trying to force too much data out to disk in one go, crowding out all other system activity. A good place to start troubleshooting is to assume that you have one of these two problems and try to figure out which one.
Fortunately, that's not usually too hard. Most operating systems provide an easy way to check swap or page file utilization (free -m works on Linux) and, if you see that value spiking upwards during the problem times, you've found the culprit. Also, with this type of issue, you'll typically have problems only during periods of high usage.
Checkpoints, on the other hand, tend to cause problems at regular intervals. Checkpoint frequency under PostgreSQL is controlled by the checkpoint_segments and checkpoint_timeout settings (use SHOW checkpoint_segments or SHOW checkpoint_timeout to find the values currently in effect). If you're having a problem every 30 minutes, for example, and you happen to have checkpoint_timeout set to 30 minutes, that's pretty strong circumstantial evidence. To build an air-tight case, you can edit postgresql.conf and set log_checkpoints=on (make sure to do pg_ctl reload after making the change). You can then compare the times at which checkpoint messages show up in the logs with the times during which the server is unresponsive.
So, once you've figured out which kind of problem you have, how do you fix it?
Problems with swapping are usually fairly straightforward to fix. The most common culprit is an overly aggressive setting for work_mem or maintenance_work_mem. Dropping work_mem can reduce query performance by forcing sorts to be performed on disk or hash joins to be performed in multiple batches, or by choosing alternate query plans which have inferior execution time but use less memory. While not ideal, it's certainly better than having your machine grind to a halt. In some cases, you might want a higher value for work_mem when, say, running your daily reports in the dead of night than you do for routine queries during normal business hours. This can often be conveniently accomplished using ALTER USER .. SET (ALTER DATABASE .. SET can sometimes be useful, too). It's less common to have a problem with maintenance_work_mem, because it is only used for maintenance operations such as VACUUM and CREATE INDEX, but it can happen. Query pg_stat_activity during problem times to get a feeling for what's running; and don't forget also to check the memory consumption of any processes other than PostgreSQL that may be running on the same machine, such as a web or application server.
Problems with checkpoint activity are harder to fix. Checkpoints are the mechanism by which the database ensures that all pages which have been modified by database activity eventually find their way to the disk. The write-ahead log ensures that, even if a crash occurs, we'll be able to recover back to a consistent state. But without checkpoints, we'd have to replay the entire write-ahead log from the time the database was created up until present day, which is obviously not very practical - especially since that would also mean needing to store the entire write-ahead log. When a checkpoint occurs, the database flushes all dirty pages to disk and advances its "redo" pointer, so that a crash will only replay activity subsequent to the start of the most recently completed checkpoint.
PostgreSQL 8.3 made significant improvements to the checkpoint process. If you are running PostgreSQL 8.2 or older, and you are having checkpoint problems, you should upgrade. It is very difficult to get the same level of performance any other way. If you are already running PostgreSQL 8.3 or higher, see Greg Smith's article on checkpoints and the background writer, or his new book PostgreSQL High Performance.