Checkpoints

The mechanics of how checkpoints worked were covered in the previous chapter, along with the principle tunables involved. Discussion here will focus mainly on common practice for initially setting these values.

checkpoint_segments

Each WAL segment takes up 16 MB. As described at http://www.postgresql.org/docs/current/interactive/wal-configuration.html the maximum number of segments you can expect to be in use at any time is:

(2 + checkpoint_completion_target) * checkpoint_segments + 1 

Note that in PostgreSQL versions before 8.3 that do not have spread checkpoints, you can still use this formula, just substitute the following code snippet for the value you'll be missing:

checkpoint_completion_target=0

The easiest way to think about the result is in terms of the total size of all the WAL segments that you can expect to see on disk, which has both a disk cost and serves as something that can be used to estimate the time for recovery after a database crash. The expected...