Checkpoint timing

Checkpoints are valuable because they reduce the amount of time required to recover from a crash, so you want to perform them as often as you can stand. However, the overhead of both the checkpoint writes and the subsequent full page writes to the WAL is expensive, which means you don't want checkpoints to happen too often.

You can adjust how often checkpoints occur by modifying two database tunables that control the two primary ways by which a checkpoint starts:

• Each time a 16 MB WAL segment is filled, and a new WAL segment is allocated, the database counts how many WAL segments have been used since the last checkpoint. When this number reaches the checkpoint_segments tunable, another checkpoint is requested. Checkpoints triggered this way are counted by incrementing the pgstat_bgwriter.checkpoints_req statistic.
• When checkpoint_timeout of time has passed since the last checkpoint, another one will occur regardless of how many segments have been used. This is...