The write-ahead log makes commits cheap by allowing pages to lag behind, and the price is a recovery step at startup. A checkpoint is the mechanism that decides how expensive that step is allowed to be.

The problem a checkpoint solves

Without one, recovery would have to read the log from the beginning of time, because any page on the device might be missing any change ever made. Startup time would grow without limit.

Stating it as unbounded growth rather than as slowness is what makes the urgency clear. A system that has run for a year would need to replay a year of log, so the failure is not that recovery is slow but that it becomes impossible long before anybody notices it is getting worse.

What a checkpoint establishes

A position in the log such that every change before it is already in a page on the device. Recovery can begin there rather than at the beginning, and everything earlier can be discarded.

This single guarantee is the whole purpose, and both halves of it matter. One bounds recovery time and the other bounds log space, which is why checkpoints appear in both this lesson and the previous one as the answer to apparently unrelated problems.

The naive version

Stop accepting work, write every dirty page, write a record naming the position, resume. It is obviously correct and takes as long as it takes to write every dirty page.

Starting from the naive version is worth the paragraph because every refinement is a response to one of its two defects. It stops the system, and it produces a burst of writes sized by the buffer pool rather than by anything about the workload.

Why stopping is unacceptable

A buffer pool of sixty-four gigabytes holding mostly dirty pages would take minutes to write, during which the system accepts nothing. Nobody will accept a minutes-long pause every few minutes.

Putting a number on it is what rules the design out rather than merely disfavouring it. The pause scales with memory size, so the design gets worse as hardware improves, which is a reliable sign that the design is wrong rather than merely old.

The fuzzy checkpoint

Record which pages are dirty and which transactions are active, write that down, and let the pages be written over the following period while work continues. The checkpoint becomes a span rather than an instant.

Understanding why this is still correct is the key idea in the section. The record does not claim the pages are written, it claims which pages still need writing, so recovery can start from the oldest of them and reach the same state without the system ever having paused.

What the checkpoint record contains

The list of pages dirty at that moment with the position of the oldest change each is missing, and the list of transactions in flight with their most recent record. Two tables, and nothing else.

Knowing the contents demystifies recovery, because those two tables are exactly what the first phase of recovery reconstructs. Writing them down at a checkpoint is a shortcut that lets recovery skip rebuilding them from the log.

Why the dirty page table is the important half

The smallest position in it is where redo must begin, because that is the oldest change not guaranteed to be in any page. Everything before it is safely on the device.

This is the arithmetic that turns the checkpoint from a vague marker into a bound. Recovery time is proportional to the log written since that minimum, which means it is controlled by how long the oldest dirty page has been allowed to stay dirty.

Why the transaction table is needed too

Recovery must know which transactions were in flight so it can reverse them, and the checkpoint record saves it from inferring that by scanning the log from the beginning.

It is the smaller half but it is not optional. A transaction that began long before the checkpoint and was still running at the crash has to be discovered somehow, and without the table the only source is a scan back to wherever it started.

Why the background writer is the real mechanism

The checkpoint only makes a promise about a position. What actually moves that position forward is the continuous background writing of dirty pages, which happens whether or not a checkpoint is in progress.

Seeing the writer as primary and the checkpoint as a record of its progress is the more accurate picture. A system whose writer keeps up has checkpoints that complete instantly, and a system whose writer falls behind has checkpoints that take forever, which is where most checkpoint problems actually live.

Why checkpoints are triggered by log volume rather than by time

Recovery time depends on how much log must be replayed, so the sensible trigger is an amount of log written. A time-based trigger makes recovery time depend on how busy the system happened to be.

This is a small design choice with a visible consequence. Under a time trigger, a traffic spike lengthens recovery exactly when the system is least able to tolerate a long restart, and under a volume trigger the spike produces more frequent checkpoints instead.

Why the checkpoint is also a page write ordering event

Every dirty page named in the checkpoint must be written before the next checkpoint can advance past it, so the checkpoint imposes a deadline on pages the replacement policy might otherwise have left alone indefinitely.

This is the mechanism by which a rarely read but frequently modified page gets written at all. Left to the replacement policy it would stay in memory and stay dirty, and the checkpoint is what eventually forces it out, which is why hot small tables produce steady background write traffic.

Why a checkpoint is not a backup

It writes pages that are already in the database's own files and establishes a position in its own log. Nothing is copied anywhere else and no earlier state is retained.

The confusion is common enough to be worth addressing directly. A checkpoint protects against the loss of memory, a backup protects against the loss of the files, and a system with perfect checkpointing and no backups has no protection against the failure people actually fear.

Why the log is the same idea as a journal in a file system

A file system faces the identical problem and takes the identical approach, writing intentions sequentially and reconciling them at mount time, which is why both have a notion of a clean shutdown.

The convergence suggests the design is forced rather than chosen. It also explains a real cost, since running a database on a journalling file system can mean the same change is logged twice, once in each layer, with each layer unaware of the other's guarantees.

Why a clean shutdown is a special case

A deliberate shutdown writes every dirty page and records that it did, so startup finds a complete checkpoint with nothing to replay. The fast startup after a clean stop is not a different mechanism, it is this one at its limit.

Recognising it as the same mechanism is useful diagnostically. A system that starts slowly after a clean shutdown did not actually shut down cleanly, which usually means it was killed rather than stopped, and that distinction is often the first thing worth establishing.

What to take from checkpoints

Three things. Without a checkpoint, recovery would have to replay the log from the beginning, so startup time would grow without limit rather than merely being slow. A checkpoint establishes one position such that every earlier change is already in a page, which bounds recovery time and log space at once. And the fuzzy checkpoint achieves this without stopping the system by recording which pages still need writing rather than claiming they are written, which makes the background writer the real mechanism and the checkpoint a record of its progress.