Checkpoints: what they do and what they cost · PostgreSQL

A checkpoint writes every dirty buffer to disk and records the WAL position at which it started. Recovery can then begin from that position instead of from the beginning of time, and every WAL segment before it can be recycled.

Both of those consequences matter, and they pull in opposite directions.

What triggers one

Trigger	Setting	Reported as
Time elapsed	`checkpoint_timeout` (300 s)	`num_timed`
WAL volume	`max_wal_size` (1 GB)	`num_requested`
`CHECKPOINT` command	—	`num_requested`
Clean shutdown	—	`num_requested`
End of recovery	—	`num_requested`

The ratio that diagnoses the cluster

Read-only / Safepg_stat_checkpointer after two and a half hours of mixed load— Captured on 18.6. The first two numbers are the diagnosis.

$ psql -U postgres -x -c "SELECT * FROM pg_stat_checkpointer"

num_timed           | 26
num_requested       | 54
num_done            | 62
restartpoints_timed | 0
restartpoints_req   | 0
restartpoints_done  | 0
write_time          | 2205951
sync_time           | 9173
buffers_written     | 210247
slru_written        | 126

54 requested against 26 timed. More than twice as many checkpoints were forced by WAL volume as occurred on schedule.

A timed checkpoint is spread over checkpoint_completion_target × checkpoint_timeout — 270 seconds on defaults — so its writes are gentle. A requested one arrives when max_wal_size is reached, which on a busy cluster is far sooner, and the spreading window shrinks with it.

The consequences of frequent forced checkpoints:

More full page writes. Lesson XII-01 measured 50% more WAL in the round after a checkpoint. Doubling the checkpoint rate applies that penalty twice as often, which generates more WAL, which triggers checkpoints sooner. It is a genuine feedback loop.

Less write coalescing. A page dirtied ten times between checkpoints is written once. A page dirtied ten times across five checkpoints is written five times.

Sharper I/O spikes, because each checkpoint has less time to spread its writes.

The fix, and what it costs

ALTER SYSTEM SET max_wal_size = '16GB';
SELECT pg_reload_conf();

Size it so that a full checkpoint_timeout interval of WAL fits. From lesson XII-02’s measured rate of 4,359 kB/s and a 300-second timeout, that interval is about 1.25 GB — so 1 GB was indeed too small for this workload, and the ratio said so.

The cost is stated plainly: more WAL to replay after a crash. A 16 GB max_wal_size means recovery may have up to 16 GB to read.

Lesson XII-06 measures recovery speed, and the calculation to do is:

worst-case recovery time ≈ max_wal_size ÷ replay rate

That number is a service-level decision, not a tuning preference. Agree it before setting max_wal_size, and write it down where the on-call rota can find it.

num_requested should be small relative to num_timed. Zero is not the target — an explicit CHECKPOINT, a shutdown and end-of-recovery all count — but a ratio above one means WAL volume is driving the schedule.

Reading the log line

ALTER SYSTEM SET log_checkpoints = on;   -- on by default since 15

Read-only / Safetwo real checkpoint completions from this cluster— Captured on 18.6 with checkpoint_completion_target 0.9 and checkpoint_timeout 300 s.

$ grep 'checkpoint complete' /var/log/postgresql/postgresql.log

LOG:  checkpoint complete: wrote 2790 buffers (17.0%), wrote 3 SLRU buffers;
    0 WAL file(s) added, 0 removed, 33 recycled;
    write=269.303 s, sync=0.011 s, total=269.392 s;
    sync files=42, longest=0.005 s, average=0.001 s;
    distance=536114 kB, estimate=536114 kB;
    lsn=6/D6CE9F60, redo lsn=6/C8031640

LOG:  checkpoint complete: wrote 11585 buffers (70.7%), wrote 1 SLRU buffers;
    0 WAL file(s) added, 0 removed, 25 recycled;
    write=46.400 s, sync=0.020 s, total=46.476 s;
    distance=410246 kB, estimate=577182 kB

Field	Reading
`wrote N buffers (P%)`	How much of the pool was dirty. Above ~50% suggests the background writer is not keeping up
`write=`	Time spreading the writes. Should approach `completion_target × timeout`
`sync=`	Time in `fsync`. This is the number that indicates storage trouble
`distance=`	WAL generated since the last checkpoint
`estimate=`	Smoothed prediction used to size recycling
`recycled` / `removed`	Segments renamed forward for reuse, and deleted
`redo lsn=`	Where recovery would start from

write=269.303 s against a 300-second timeout is 0.898 — exactly checkpoint_completion_target. The writes were deliberately spread, and this is what a healthy checkpoint looks like.

sync=0.011 s against write=269.303 s. Almost all of a healthy checkpoint is spreading, not syncing. When sync_time becomes a significant fraction, storage is the constraint and no checkpoint setting will fix it.

The second line is the contrast: write=46.400 s and 70.7% of the pool dirty. That is a forced checkpoint, arriving early with a large backlog and less time to place it.

PostgreSQL 18 changed the log line format slightly, reporting shared buffers and SLRU buffers separately. From the release notes:

Add column pg_stat_checkpointer.slru_written to report SLRU buffers written … Also, modify the checkpoint server log message to report separate shared buffer and SLRU buffer values.

Alerting or log parsing written against the pre-18 format needs adjusting.

The trade, stated

	Frequent checkpoints	Infrequent checkpoints
WAL volume	Higher (full page writes)	Lower
I/O smoothness	Spikier	Smoother
Write coalescing	Worse	Better
Recovery time	Shorter	Longer
`pg_wal` size	Smaller	Larger

Everything except recovery time favours infrequent checkpoints. The usual settings that follow:

ALTER SYSTEM SET checkpoint_timeout = '15min';
ALTER SYSTEM SET max_wal_size = '16GB';
ALTER SYSTEM SET checkpoint_completion_target = '0.9';  -- default since 14
ALTER SYSTEM SET min_wal_size = '2GB';

CHECKPOINT by hand is heavier than it looks

CHECKPOINT;

It writes every dirty buffer immediately, with no spreading — the immediate force wait variant visible in the log. On a large shared_buffers that is a substantial burst of I/O all at once, and it competes with production traffic.

Legitimate uses: before a planned shutdown, before taking a filesystem snapshot, and immediately before a test that needs a known starting state — which is how the full-page-write measurement in lesson XII-01 was constructed.

Not legitimate: as a routine cron job, or as a response to pg_wal growing. In the second case it usually does nothing, because the reason WAL is being retained is a slot or the archiver, and a checkpoint does not release either.

Restartpoints, and why a standby recovers slowly

The restartpoints_timed, restartpoints_req and restartpoints_done columns are zero on a primary and are the whole story on a standby.

A standby cannot perform checkpoints, because it is not generating WAL — it is replaying someone else’s. Instead it performs restartpoints: the same flushing work, but only ever at a position that already appeared as a checkpoint in the WAL stream it is replaying.

Three consequences that surprise people during a failover.

A standby’s restartpoints are bounded by the primary’s checkpoints. It cannot create a recovery position the primary never recorded. So a primary with a 30-minute checkpoint interval gives its standbys a 30-minute floor on their own recovery distance, whatever their settings say.

checkpoint_timeout on the standby still applies, but only to choose among the checkpoint records that arrive. Setting it lower than the primary’s has no effect.

A standby that has been replaying for a long time without reaching a checkpoint record has a long restart ahead of it. After an unclean standby restart, recovery begins from its last restartpoint, which may be much further back than the last data it applied.

The practical consequence for failover planning: recovery time on a promoted standby is not governed by the standby’s configuration. It is governed by the primary’s checkpoint frequency, which is the same setting being tuned for the primary’s own WAL volume. Those two goals conflict, and the resolution is to measure both rather than to optimise one.

-- on the standby
SELECT restartpoints_timed, restartpoints_req, restartpoints_done
  FROM pg_stat_checkpointer;

SELECT pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn(),
       pg_size_pretty(pg_last_wal_receive_lsn() - pg_last_wal_replay_lsn()) AS replay_backlog;

What to take from this

num_requested exceeding num_timed means max_wal_size is too small. Measured here at 54 against 26.
Size max_wal_size so a full checkpoint_timeout of WAL fits, from the measured generation rate.
The cost of infrequent checkpoints is recovery time. Calculate it and agree it as a service level.
write= should approach completion_target × timeout. sync= becoming significant means storage is the constraint.
CHECKPOINT by hand is unspread and heavy, and does not release WAL held by a slot or the archiver.
On a standby, recovery distance is set by the primary’s checkpoint frequency.

Cross-course references

Linux for Production Sysadmins — Part XLI (Storage Performance) covers measuring the write burst a checkpoint produces, and Part XL (Memory Performance) covers the page cache it flushes through.
Observability for Production Sysadmins — Part XIII (Rates and counters) covers the timed-versus-requested checkpoint ratio, which is the number that says whether max_wal_size is sized correctly.