Checkpoints

## The setup A relational engine does not write changed pages to their data files at commit time. It writes a compact **log record** describing each change, flushes the log at commit, and leaves the modified page **dirty** in memory. This is what makes commits fast: one sequential log write rather than scattered random page writes, and many updates to the same page collapse into a single eventual write. The consequence is a permanent gap. The log knows about changes the data files have never seen. Something must periodically close that gap, and that something is the **checkpoint**. ## What a checkpoint does Conceptually a checkpoint has three parts: 1. **Write out dirty pages** from the buffer pool - at minimum, all pages dirtied before some cut-off point in the log. 2. **Force the log** up to that point so log and data are consistent with each other. 3. **Record the checkpoint**: write a checkpoint record into the log and, crucially, store a pointer to it in a well-known place (a control file or header) that recovery reads first. After it completes, the engine can assert: every change logged before position X is present in the data files on disk. ## Problem 1: bounding recovery time When an engine restarts after a crash, it must find every change that was logged but might not have reached the data files, and replay it. The only way to know where to start is the last checkpoint - the checkpoint record says 'everything before here is already on disk', so recovery begins there and scans forward to the end of the log. The length of that scan is the dominant term in recovery time. Checkpoint every 30 seconds and recovery reads roughly 30 seconds' worth of log; checkpoint every hour and it reads an hour's worth, which on a write-heavy system can be many gigabytes and many minutes. Because the checkpoint interval sets an upper bound on how much log recovery must process, this is the knob operators tune when a recovery-time objective exists: 'we must be back within 60 seconds' translates directly into a checkpoint target. Note what checkpoints do *not* do: they do not make committed data durable. Durability comes from flushing the log at commit. A checkpoint is about *how long it takes to reconstruct* state after a crash, not about whether the state survives. ## Problem 2: reclaiming log space Log files are finite. A segment may only be recycled or archived once nothing will ever need to read it again - which means once every change it contains is guaranteed present in the data files. That guarantee is exactly what a checkpoint establishes. With no checkpoints, no segment can ever be released and the log grows until the filesystem is full, at which point the database typically stops accepting writes. This failure mode appears in real systems whenever something *blocks* checkpoint progress or holds the reclaim point back - a stuck long-running transaction, a replication slot no consumer is reading, an archiving command that keeps failing. The symptom is a log directory growing without limit, and the diagnosis is always 'what is preventing the reclaim point from advancing?' ## The cost side Checkpoints are not free. Flushing dirty pages is random write I/O, and doing it in a burst can visibly slow every concurrent query. Checkpointing more often also means the same hot page is written to disk more times - a page updated a thousand times between two checkpoints costs one write, but with ten times more frequent checkpoints it may cost ten. This is **write amplification**, and it is the direct price of shorter recovery. Hence the tradeoff every engine exposes in some form: frequent checkpoints give fast recovery and small logs at the cost of more steady-state write I/O; infrequent checkpoints give cheap steady state at the cost of slow recovery and large logs. ## Related mechanisms worth naming - **Fuzzy checkpoints**: production engines do not stop the world to take a checkpoint; they let transactions continue while pages are flushed, and record enough information for recovery to cope with the resulting inconsistency. - **Spreading the I/O**: rather than dumping all dirty pages at once, engines pace the writes across the interval so the burst becomes a hum. - **Shutdown checkpoints**: a clean shutdown takes a final checkpoint so that startup has essentially nothing to replay - which is why a clean restart is fast and a crash restart is not. ## The one-line answer A checkpoint synchronizes the data files with the log so that recovery can start from a recent, known-good position and old log can be thrown away; its frequency trades recovery time against write I/O.