Determining Checkpoint Frequency

Checkpoints should happen every few minutes. If they happen several times a minute, performance will suffer. To determine checkpoint frequency, you must first enable log file timestamps in postgresql.conf with:

log_timestamp = true

After you force the postmaster to recognize this postgresql.conf change with pg_ctl reload, you will see these lines in the PostgreSQL server log file:

2002-02-11 21:17:32 LOG: recycled transaction log file 0000000000000000  
2002-02-11 21:17:33 LOG: recycled transaction log file 0000000000000001  
2002-02-11 21:17:33 LOG: recycled transaction log file 0000000000000002  
2002-02-11 21:18:13 LOG: recycled transaction log file 0000000000000003  
2002-02-11 21:18:13 LOG: recycled transaction log file 0000000000000004  
2002-02-11 21:18:13 LOG: recycled transaction log file 0000000000000005

Measure the duration between checkpoints to determine if you are checkpointing too frequently. As you can see from the example above, checkpoints are happening ever fourty seconds, which is too frequent for good performance. Do not be alarmed if there are several log entries with identical timestamps, as shown above. Multiple messages are often generated by a single checkpoint.