Database checkpoints with WAL

WHAT THIS TESTS The interviewer checks that you connect checkpoints to recovery time, WAL retention, and the I/O cost of flushing dirty pages.

A GOOD ANSWER COVERS 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.

COMMON WRONG ANSWERS 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.

LIKELY FOLLOW-UPS What triggers a checkpoint; how does spreading flushes reduce spikes; how does checkpoint frequency interact with recovery SLAs; what is a fuzzy checkpoint.

ONE 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.