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.
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:
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
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 provides an excellent overview of the checkpointing procress.
A checkpoint can be manually forced by a database superuser with the
CHECKPOINT command, however in normal operation this will usually not be required.
Routine checkpoints are not logged by default. Set
log_checkpoints to "
on" to enable checkpoint logging (see that entry for an example).
Typical log output for a checkpoint triggered by a checkpoint timeout:
[2021-03-29 05:45:49 UTC] LOG: checkpoint starting: time [2021-03-29 05:46:04 UTC] LOG: checkpoint complete: wrote 155 buffers (0.1%); 0 WAL file(s) added, 0 removed, 0 recycled; write=15.810 s, sync=0.013 s, total=15.904 s; sync files=63, longest=0.003 s, average=0.000 s; distance=783 kB, estimate=2315 kB
- PostgreSQL 14
- PostgreSQL 9.6
- PostgreSQL 9.5
- PostgreSQL 9.2
- PostgreSQL 8.3
- PostgreSQL 7.4
- PostgreSQL 7.1