Checkpoints

Checkpoints should happen every few minutes. If they happen several times a minute, performance will suffer. To determine if you are checkpointing too frequently, look in your server logs for a message emitted by checkpoint_warning. This message is emitted if you checkpoint more than once in a 30 second period.

Reducing checkpoint frequency involves increasing the number of write-ahead log files created in data/pg_xlog. Each file is 16 megabytes, so this does affect disk usage. The default setup uses a minimum number of log files. To decrease checkpoint frequency, you need to increase this parameter:

checkpoint_segments = 3

The default value is three. Increase it until checkpoints happen only every few minutes. Another log message that may appear is:

LOG: XLogWrite: new log file created - consider increasing WAL_FILES

This message indicates that the wal_files parameter should be increased in postgresql.conf.