Flatten checkpoint I/O spikes by tuning checkpoints
checkpoint_completion_target changes the pacing window for dirty-buffer writes. The page now requires real counter samples; it no longer seeds invented checkpoint curves.
Problem
What you're actually looking at
The symptom as it shows up on a real server.
A checkpoint has to write every dirty buffer in the pool. It can do that as fast as the storage allows, or it can pace itself across the checkpoint interval. The fast version is invisible on a graph of totals and extremely visible to anyone whose query happened to need the disk at that moment. checkpoint_completion_target is the knob, and its effect only shows up if you look at the write rate rather than the write count.
ckpt_heatmap is 75 MB against 128 MB of shared buffers, so its working set stays resident and the checkpointer, not the backend ring buffer, is what ends up writing it out. A steady stream of scattered updates keeps re-dirtying the same pages.
Simple terms
A checkpoint has to write every dirty page in memory out to disk. It can do that in one hard burst as fast as the storage allows, or pace itself gently across the interval. The burst is invisible on a chart of daily totals but painfully visible to whichever query needed the disk at that instant. checkpoint_completion_target is the knob that spreads the writes out, and its effect only shows up if you look at the write rate over time, not the total number of writes.
Full runbook for this incident
- The full identify checklist, the exact signals that tell you it's this incident
- Every diagnostic query; lab output is attached only to the steps we actually captured
- The resolution path and the pitfalls that make it worse
- Mitigation steps to stop it recurring, plus a verify-you're-done query
Card required. Cancel before day 7 and you are not charged.
More in this category
Other WAL & replication runbooks
Neighbouring incidents that share the same diagnostic surface.
Connected
How this connects to the rest of the library
A live view of this page's real cross-references, what explains it, what fixes it, what to tune, and where to go next. Every link is an authored relationship, not a guess.
Fixes these errors
Need the full procedure?
Pro runbooks finish the incident path
Free runbooks teach the shape. Pro opens the full step transcript, edge cases, and prevention depth.