Checkpoint

A point at which all altered data is flushed to disk

A checkpoint is an operation which flushes all "dirty" (altered or new) data pages in memory to the on-disk datafiles. It is guaranteed that all changes logged in the WAL up until the checkpoint are present in the datafiles.

Should a server crash occur, the crash recovery procedure will restore changes from WAL, starting immediately after the most recent checkpoint, the location of which is recorded in pg_control.

Automatic checkpoints

Checkpoints are executed automatically, usually at regular intervals, by the checkpointer process (however they will be skipped if the system has been idle since the last checkpoint).

Checkpoints cause spikes in I/O load, so checkpoint configuration is an important consideration when tuning PostgreSQL server performance.

The following parameters influence how often a checkpoint is carried out:

Additionally, checkpoint_completion_target can be adjusted to spread out the checkpoint process, reducing the amount of I/O generated by the checkpoint.

Note that it is not possible to disable the checkpointer, not would that ever make sense; however by setting checkpoint_timeout to 1d (one day, the maximum), and max_wal_size to a very large value, it's possible to delay regular checkpoints for a very long time, e.g. for testing purposes.

The documentation section WAL Configuration has a good overview of the checkpointing procress.

Manual checkpoints

A checkpoint can be manually forced by a database superuser with the CHECKPOINT command, however in normal operation this will usually not be required.

Logging

By default, routine checkpoints are not logged. Set log_checkpoints to on to enable checkpoint logging (see that entry for more details).

Categories

Management / adminstration, Performance, PostgreSQL concept, PostgreSQL internals

See also

Background writer, CHECKPOINT (SQL command), checkpoint_segments, checkpointer, restartpoint, log_checkpoints