CSE Roadmap

Common tier · about 2 hours · 5 parts

Part 4 of 5 · 2 min read

The checkpoint frequency trade-off

More frequent checkpoints shorten recovery but cost more during normal operation; less frequent checkpoints do the opposite.

The checkpoint frequency trade-off

Think of how often the diary keeper should actually make that archival note. Doing it after every single sentence would be exhausting and slow down the writing itself. Doing it only once a decade would make any single reconstruction extremely long.

In plain words: checkpointing more frequently shortens the amount of log recovery ever needs to replay after a crash, since the safe starting point is always recent. However, checkpointing itself has a real cost during normal operation: flushing every dirty page takes real I/O, covered in Level 4, which can briefly affect the performance of ongoing transactions while it happens. Checkpointing less frequently reduces that ongoing cost but lengthens potential recovery time, since more log entries would need replaying if a crash happens right before the next scheduled checkpoint.

A worked example. A database checkpointing every five minutes keeps recovery fast after any crash, at the cost of brief, regular I/O spikes every five minutes during normal operation; checkpointing only once a day would reduce those spikes but risk a much longer recovery if a crash happens late in that day.

The key lines. No new SQL; checkpoint frequency is typically a configurable setting, chosen by weighing this specific trade-off for the workload at hand.

Checkpoint frequency trade-off

The checkpoint frequency trade-off weighs shorter crash recovery time against more frequent checkpoints, against the real I/O cost each checkpoint imposes during normal operation, with the right balance depending on the specific workload.

Example: choosing how often a diary keeper archives, between after every sentence and once a decade, mirrors the checkpoint frequency trade-off.

Remember this

A checkpoint is what keeps recovery time from growing without bound as a database ages. The frequency chosen is a direct trade-off between normal-operation cost and worst-case recovery time, not a setting with one universally correct value.

Quick notes

  • Without a checkpoint, recovery would need to replay the log all the way back to its very beginning.
  • A checkpoint flushes every dirty page to disk and records that fact in the log.
  • Recovery only needs to replay log entries from the most recent checkpoint onward.
  • More frequent checkpoints shorten recovery time but cost more I/O during normal operation.
  • Checkpoint frequency is a configurable trade-off, not a universally fixed setting.