Checkpoint Tuning
Checkpoints periodically flush all dirty pages and record a recovery point; tuning their frequency trades steady I/O spread against recovery time and write spikes.
Definition
A checkpoint is the operation that forces all dirty buffer pool pages to disk and writes a checkpoint record to WAL, bounding crash recovery time. Spacing between checkpoints controls whether flushes spread smoothly or arrive as IO storms. Checkpoint completion targets and max checkpoint distance are the main tuning levers.
Context and scope
Tuned on write-heavy PostgreSQL and MySQL servers, especially on storage with limited IOPS, where checkpoint storms manifest as latency spikes every N minutes.
Common misconceptions
- Checkpoints are backups — they are crash recovery boundaries in the WAL stream, not data backups
- Frequent checkpoints always smooth I/O — they write the same pages repeatedly and waste I/O via page churn
- Checkpoint tuning is irrelevant on fast storage — even NVMe saturates under storm-aligned flush bursts on write-heavy databases
Anti-patterns
- Leaving defaults so checkpoints complete far ahead of schedule and concentrating flushes into spikes
- Setting very long checkpoint intervals and accepting long crash recovery and huge WAL retention
- Tuning checkpoints without measuring dirty page write distribution first, guessing at the wrong lever
Related concepts
Related examples
Authoritative source
https://www.postgresql.org/docs/current/wal-configuration.html
What it is
Between checkpoints, the database writes changes to WAL and dirties pages in memory. At checkpoint time, all dirty pages flush and WAL marks the recovery start. Recovery replays only WAL after the checkpoint — so checkpoint spacing bounds both replay time and flush pattern.
How it works
PostgreSQL checkpoint_completion_target spreads flushing across the interval toward a fraction of checkpoint distance; values near 0.9 keep I/O flat but require enough time budget. MySQL InnoDB flushes pages based on the oldest modification age, with innodb_io_capacity as the pacing knob. Too-frequent checkpoints cause page eviction churn on pages that dirty again.
When to tune
Periodic latency spikes aligned with checkpoint intervals are the signature. Compare pg_stat_bgwriter or InnoDB flush stats against checkpoint timing, then widen intervals or raise completion targets to flatten the curve.
Pitfalls
Full-page writes after the first modification of each page following a checkpoint inflate WAL volume — longer intervals reduce that overhead but raise recovery time. Interaction with replication lag matters: standby replay must keep up with the WAL the intervals generate. Change one lever at a time and watch latency percentiles, not averages.