Checkpoints and the Cost of Durability

A checkpoint writes every dirty page in shared_buffers out to the data files, forces them to durable storage, and records the position from which crash recovery may start. It bounds how long that recovery takes, it is what allows old WAL segments to be recycled, and it is a periodic burst of I/O that you either spread out deliberately or trip over on a Saturday morning.

Most reports of "the database freezes for twenty seconds every few minutes" are a checkpoint configuration rather than a query problem. Chapter 10 owns the configuration file as a subject, memory and pooling included; these five settings are argued here instead because the reasoning behind every one of them is durability and recovery time, not throughput.

What a Checkpoint Does

The sequence is short. Write out every buffer that has been modified since the last checkpoint, force the data files to durable storage, write a checkpoint record into the WAL, and update pg_control with the position recovery should begin from. Once that is done, the log written before the checkpoint's redo point is no longer needed for crash recovery, and the segments holding it can be recycled. min_wal_size, 80 MB by default, is the floor below which old files are renamed for reuse rather than deleted, so a steady workload stops churning the filesystem with creation and removal.

The manual names two costs: writing out all currently dirty buffers, and the extra WAL traffic that follows. On pg-primary, shared_buffers is 16 GB, so even a fifth of it dirty is 3.2 GB that has to reach the disks. Configuration does not change that total; it decides whether the 3.2 GB arrives over five seconds or over thirteen minutes.

The Two Triggers

A checkpoint begins every checkpoint_timeout seconds, or when max_wal_size is about to be exceeded, whichever comes first. The defaults are five minutes and 1 GB. A handful of events force one outside that schedule as well: a clean shutdown, an explicit CHECKPOINT, and the start of a base backup. The distinction between the two automatic triggers has a name in the statistics: a checkpoint is either timed, meaning the clock reached it, or requested, meaning the WAL volume did, and that pair of counters is the whole diagnosis.

At Cartwheel's Saturday peak of 3,000 orders a minute, plus the delivery_events ingest, 1 GB of WAL takes about two minutes to produce. So a cluster left on the defaults checkpoints roughly every two minutes rather than every five, and Postgres says so: when checkpoints fall closer together than checkpoint_warning seconds, 30 by default, it writes a message to the server log recommending that max_wal_size be raised. A large COPY produces a short run of those messages and then stops; at Cartwheel's write rate they arrive every two minutes, all week.

SELECT num_timed, num_requested, num_done, buffers_written,
       round(write_time/1000) AS write_s,
       round(sync_time/1000)  AS sync_s
  FROM pg_stat_checkpointer;

 num_timed | num_requested | num_done | buffers_written | write_s | sync_s
-----------+---------------+----------+-----------------+---------+--------
      1184 |          3971 |     5155 |       412884390 |   38412 |    902

Nearly four thousand requested checkpoints against twelve hundred timed ones is the 77% that Chapter 10's dashboard reported and left for this page. The volume trigger is firing more than three times as often as the clock, which says max_wal_size is too small for this write rate and nothing else. The target to steer toward is a requested count near zero over a normal week, with the occasional spike during a bulk load. The two time columns matter separately: write_time being large is the spreading working as intended, while a large sync_time is the flush at the end stalling, which is what checkpoint_flush_after exists to keep bounded at its default of 256 KB on Linux and disabled elsewhere.

Spreading the Write

checkpoint_completion_target tells the checkpointer to pace itself so that it finishes after that fraction of the interval has elapsed rather than as fast as the disks allow. The default has been 0.9 since 14; before that it was 0.5, which is worth knowing because a cluster carried forward from an older major often still has the old value written into its configuration file where nobody has looked at it since. At 0.9 with a fifteen-minute timeout, the checkpointer has about thirteen and a half minutes to move its 3.2 GB, and the I/O reads as background load rather than an event.

checkpoint_timeout = 15min           # up from 5min: fewer, larger checkpoints
max_wal_size = 16GB                  # big enough that checkpoints are timed
min_wal_size = 2GB                   # recycle segments instead of churning
checkpoint_completion_target = 0.9   # spread the writes over 90% of it
wal_compression = lz4                # compresses the full-page images

Those five lines are one decision, not five. Raising the timeout while leaving max_wal_size at 1 GB accomplishes nothing at all, because the volume trigger simply keeps firing first and the clock never gets a turn. Raising both is what converts requested checkpoints into timed ones. And the pg_wal volume has to be sized well beyond max_wal_size, because that setting is a soft target rather than a cap: a slow archive destination or a replication slot holding segments back will push real usage past it without any of these numbers changing.

Why More Checkpoints Mean More WAL

Here is the part that reverses most people's instinct. The first change to a page after each checkpoint carries the whole 8 KB page into the log. Halve checkpoint_timeout and you double the number of intervals, and each interval re-images every hot page on its first touch — same workload, same rows, substantially more bytes of WAL. So checkpointing more often costs you more steady-state I/O and a larger log, and buys a shorter replay after a crash that may never happen. The only thing pushing the interval back down is the recovery time you have promised somebody.

Reading the Evidence

log_checkpoints has defaulted to on since 15, which means most clusters already have the raw material. Each checkpoint logs its own statistics, including how many buffers it wrote and how long it spent writing them, so a five-minute cadence in the log is visible without any query at all. pg_stat_checkpointer is the cumulative view of the same thing, and it is new enough to trip people up: it was created in 17 by moving the relevant columns out of pg_stat_bgwriter, so a monitoring dashboard written against an older cluster reads zeros or errors rather than reporting a problem.

Two numbers cover the subject between them. The first is the requested-to-timed ratio, sampled weekly rather than instantaneously, because it moves when the write rate moves and a number that was right in March can be wrong by August. The second is wal_fpi as a share of wal_records from the previous topic, which says whether a change to checkpoint spacing reduced full-page images or only changed how often they arrive.

Checkpoint Spacing and the Recovery Clock

Every setting on this page trades steady-state I/O against replay time, and only one of the two is measurable in advance. Longer intervals mean smoother writing, less WAL, and more log to get through when the server comes back up; the replay is performed by a single startup process working forward through the segments, so its speed is a property of the machine and the storage rather than something concurrency improves. A fifteen-minute timeout on a cluster generating 500 MB of WAL a minute means up to several gigabytes of replay after an unclean stop.

The honest way to choose the number is to find out what that replay actually costs on this hardware, then set the interval against the recovery time you have promised somebody. Copying 15min out of a tuning article gets you a value that is probably better than the default and definitely unmeasured. Topic 66 runs that replay against a stopwatch, on this hardware, and comes back with a number.