PostgreSQL Checkpoints

The idea: bound the work of crash recovery

PostgreSQL changes pages in shared buffers and writes only the WAL (write-ahead log) at commit. The changed pages, the dirty buffers, reach the data files later. After a crash, the server replays WAL to redo the changes that were lost from memory. A checkpoint is the point that limits how far back that replay must go: once every page that was dirty at the checkpoint's start is safely on disk, WAL older than that start is no longer needed.

What starts a checkpoint

• Time: checkpoint_timeout (default 5 min) has passed since the last checkpoint started. The log says checkpoint starting: time. Demo: timed checkpoint.
• WAL volume: so much WAL was written since the redo point that the total kept on disk would pass max_wal_size (default 1 GB). Because WAL from about 1 + checkpoint_completion_target intervals must be kept, the trigger is near 1 GB / 1.9 ≈ 539 MB. The log says checkpoint starting: wal. Demo: max_wal_size reached.
• By hand: the CHECKPOINT command, and also pg_start_backup/pg_backup_start, CREATE DATABASE, a clean shutdown and the end of crash recovery.

What a checkpoint does

1. Records the redo point: the WAL insert position (LSN) at the start. Every change made after it is in WAL after it.
2. Marks every buffer that is dirty right now, and writes those buffers to the data files (into the OS cache). Pages dirtied later are not its job.
3. fsyncs the data files, so the writes are really on disk.
4. Writes a checkpoint record into WAL and stores its LSN and the redo point in pg_control, a small file in global/.
5. Removes or recycles WAL segment files older than the redo point.

The first change to a page after a redo point writes a full-page image (FPI) into WAL, not just the change (full_page_writes = on). A crash can tear an 8 KB page half-written; redo then restores the whole page from the image. That is why WAL grows faster just after a checkpoint starts.

Spreading the writes

Writing all dirty buffers at once would flood the disk and slow every query. A timed or WAL checkpoint is therefore throttled: it paces its writes so that it finishes when checkpoint_completion_target (default 0.9) of the interval has passed, measured in time or in WAL, whichever is further along. On the time axis that is a low, flat row of writes. A CHECKPOINT command or the end-of-recovery checkpoint runs at full speed: one tall spike. Demo: manual CHECKPOINT.

WAL segment files

WAL lives in pg_wal/ as 16 MB segment files named like 000000010000000000000012. Recovery needs only the WAL from the latest completed checkpoint's redo point on. When a checkpoint completes, the older files are recycled, renamed to future names so new WAL can reuse them without creating files, or deleted if more than about one interval's worth are spare. min_wal_size (80 MB) of files is always kept. max_wal_size is a soft limit: a burst can go past it until the next checkpoint finishes.

Crash recovery

On start after a crash, the startup process reads pg_control, finds the latest checkpoint record and its redo point, and replays every WAL record from there to the end of WAL. Pages it changes become dirty buffers again; then an end-of-recovery checkpoint writes them. A crash during a checkpoint is no problem: that checkpoint never updated pg_control, so redo starts from the previous one, which means more WAL to replay. Demo: crash during a checkpoint vs Demo: crash after a checkpoint.

Frequent or rare checkpoints

	Frequent (short checkpoint_timeout, small max_wal_size)	Rare (long timeout, large max_wal_size)
Crash recovery	short: little WAL after the redo point	longer: more WAL to replay
WAL volume	more full-page images (each page's first change in each interval)	fewer full-page images
Data file writes	a hot page is written once per checkpoint, so more often	repeated changes to a hot page are written once
Disk for pg_wal	small	up to max_wal_size

A common setting is a longer checkpoint_timeout (15–30 min) and a max_wal_size large enough that most checkpoints are time, not wal. With log_checkpoints = on (the default since PostgreSQL 15) every checkpoint logs its buffers written, files recycled and durations; the warning checkpoints are occurring too frequently means max_wal_size is too small. Demo: checkpoint_timeout = 1 min shows the extra full-page images.

See also PostgreSQL WAL internals, PostgreSQL shared buffers, PostgreSQL processes, Write-Ahead Logging and ARIES Crash Recovery and How PostgreSQL Runs a Query.

InnoDB checkpoints continuously from the oldest dirty page instead; see InnoDB Redo Log Internals.

What the page leaves out

The pool has 48 buffers instead of thousands, and WAL volumes are toy numbers chosen so a 5 minute interval fits on screen. The background writer and backends also write dirty buffers between checkpoints, and the checkpointer sorts its writes by file and block and asks the kernel to flush them in batches (checkpoint_flush_after). Real WAL recycling follows an estimate of the WAL needed for the next interval, and restartpoints on standbys work like checkpoints but use the primary's checkpoint records.

References

WAL Configuration: checkpoints (PostgreSQL documentation)

CHECKPOINT command

Write Ahead Log settings: checkpoint_timeout, max_wal_size

The Internals of PostgreSQL, chapter 9: WAL and checkpoints