Database checkpoints with WAL
balancing recovery time and runtime I/O.
a checkpoint flushes dirty pages and records a known-good point so recovery can start later in the log; frequent checkpoints shorten recovery but add I/O spikes, infrequent ones lengthen…
What's really being asked
The interviewer checks that you connect checkpoints to recovery time, WAL retention, and the I/O cost of flushing dirty pages.
The full answer
With WAL, committed changes live in the log while data pages can stay dirty in memory. Without intervention, the WAL grows forever and crash recovery would have to replay from the very beginning. A checkpoint solves both: it flushes the dirty pages accumulated up to a point out to the data files, then writes a checkpoint record into the log recording that the data files are consistent up to that log position. Recovery can then start from the last checkpoint rather than the start of time, bounding replay work, and WAL segments older than the checkpoint can be recycled or archived. The trade-off is frequency. Frequent checkpoints keep recovery fast and WAL small but force repeated bursts of random write I/O, contending with foreground work and creating latency spikes. Infrequent checkpoints reduce that runtime overhead but lengthen crash recovery and let WAL grow, risking disk pressure. Engines spread the flushing over an interval to smooth the spikes.
The mistakes people make
Saying a checkpoint flushes or truncates the WAL itself; it flushes dirty data pages and marks a position. Believing checkpoints are only for cleanup, missing their recovery-bounding role. Assuming more frequent is always better, ignoring I/O cost.
What usually comes next
What triggers a checkpoint; how does spreading flushes reduce spikes; how does checkpoint frequency interact with recovery SLAs; what is a fuzzy checkpoint.
A concrete example
A system set to checkpoint every thirty minutes crashes after writing twenty-eight minutes of WAL; recovery must replay nearly all of it, taking minutes. Lowering the interval to five minutes means recovery replays at most five minutes of log, but the database now flushes dirty pages more often, raising steady background write I/O that must be tuned not to disrupt peak traffic.
Interview question
What is the main trade-off when increasing checkpoint frequency?
- a.Slower recovery but lower disk usage
- b.Faster recovery but more frequent dirty-page flush I/O during normal operationCorrect
- c.It truncates the WAL more aggressively, risking data loss
- d.It disables durability to gain speed
Why? this is the answer
More frequent checkpoints bound replay work so recovery is faster, but they flush dirty pages more often, adding write I/O and latency risk at runtime. Checkpoints do not truncate the WAL or weaken durability.
Just read this? Test yourself on what you have been reading.
Read the original → postgresql.org
- #databases
- #wal
- #checkpoint
- #recovery
- #io-tuning
Put your scrolling time to good use
Learn one idea, try a quiz and save useful cards for revision. Tezvyn makes it easy to learn and stay current in your tech field, a few minutes at a time.
The iPhone app is on the way
We are building it. Until it lands, nothing here is held back from you: every interview card, your saved cards, streaks and the job board all work in Safari, plus hundreds of free practice quizzes of thirty questions each. Sign in and it all carries over to the app the day it arrives.
Want it as an icon? Tap Share at the bottom of Safari, then Add to Home Screen. It opens full screen and the cards you have read stay available offline.
We are hiring for this. Open roles that interview on databases — each one lists the topics its interview covers.
See open roles