vault

Checkpoints and Fuzzy Checkpointing

12 min read

Checkpoints and Fuzzy Checkpointing

A checkpoint is a periodic action that lets a database bound how far back crash recovery must go. Because a write-ahead-logged engine keeps committed changes in the log while their data pages linger dirty in the buffer pool, without checkpoints the log would have to be replayed from the very beginning after a crash — recovery time would grow without limit. A checkpoint writes a marker into the log that says, in effect, “the state of the system is known as of here,” which becomes the point where the ARIES Analysis pass begins and, via the recorded Dirty Page Table, determines where the Redo pass begins. The naïve way to do this — a sharp (consistent) checkpoint — quiesces the whole database and flushes every dirty page, which stalls the system for the duration. The technique every production engine actually uses is the fuzzy checkpoint: it records the transaction and dirty-page bookkeeping without forcing all dirty pages to disk and without stopping transactions, trading a slightly more complex recovery scan for the elimination of the stall.

This note is about the checkpoint mechanism specifically. For the full recovery algorithm it feeds, see The ARIES Recovery Algorithm; for the LSN machinery it relies on, see Log Sequence Numbers and the Durability Guarantee; for the buffer-pool side of “which pages are dirty and when do they flush,” see Dirty Pages and Checkpoints.

Mental Model — A Checkpoint Advances the Redo Start Line

Picture the WAL as a long tape. Every so often the database drops a checkpoint marker on it. When the machine crashes, recovery does not rewind to the start of the tape — it rewinds only to the last marker (for Analysis) and then, guided by the marker’s record of which pages were dirty, forward-scans from the oldest still-dirty change (for Redo). The more recently the marker was dropped, the less tape there is to re-read.

flowchart LR
  START["log start"] --- C1["checkpoint₁"] --- C2["checkpoint₂"] --- MINREC["min recLSN<br/>(oldest dirty change)"] --- CRASH["💥 crash"]
  C2 -.->|"Analysis starts here"| A["ATT + DPT rebuilt"]
  MINREC -.->|"Redo starts here"| R["repeat history"]

Diagram: a checkpoint advances how far back recovery must read. What it shows: after a crash, Analysis begins at the most recent checkpoint (checkpoint₂), not at the log start, and Redo begins at the minimum recLSN — the oldest change to any page still dirty at the crash — which the checkpoint’s Dirty Page Table records. The insight: the interval between checkpoints is a direct dial on recovery time; a checkpoint’s whole purpose is to push the Analysis and Redo start lines rightward, closer to the crash (CMU 15-445 L21; Mohan et al. 1992).

Why the Naïve Checkpoint Stalls: Sharp Checkpoints

The simplest correct checkpoint is a blocking or sharp (also called consistent) checkpoint. The database (CMU 15-445 L20; CMU 15-445 L21):

  1. Halts the start of any new transactions.
  2. Waits until all active transactions finish executing.
  3. Flushes all dirty pages in the buffer pool to disk.
  4. Writes a CHECKPOINT record to the log and flushes it.

After such a checkpoint the on-disk database is a transactionally consistent snapshot: everything before the marker is guaranteed on disk. Recovery becomes almost trivial — there is nothing dirty to redo from before the checkpoint. But the cost is exactly the problem: the database must stop until every in-flight transaction drains and every dirty page is written. On a busy system with a large buffer pool, that is a multi-second (or worse) freeze during which the database appears hung to every client. “This process impacts runtime performance” and “is bad for runtime performance but makes recovery easy” (CMU 15-445 L20/L21). No high-throughput engine can accept periodic world-stops.

A slightly better blocking scheme halts new transactions and merely pauses (rather than drains) the active ones long enough to record the internal state — the Active Transaction Table and Dirty Page Table — at the checkpoint’s start. It still pauses, though, so it is only a stepping stone toward the real answer.

Fuzzy Checkpoints — Recording State Without Stopping

A fuzzy checkpoint “is where the DBMS allows other transactions to continue to run. This is what ARIES uses in its protocol” (CMU 15-445 L21). “Fuzzy” is the operative word: because transactions keep modifying pages during the checkpoint, the set of dirty pages is a moving target and the on-disk image is not a clean, single-instant snapshot — it is smeared across the checkpoint’s duration. The trick is to record enough bookkeeping that recovery can reconstruct the truth anyway.

ARIES delimits a fuzzy checkpoint with two log records (CMU 15-445 L21):

  • <CHECKPOINT-BEGIN> — marks the start. “At this point, the DBMS takes a snapshot of the current ATT and DPT, which are referenced in the <CHECKPOINT-END> record. Transactions that start after the checkpoint initiation are not included in the ATT.”
  • <CHECKPOINT-END> — marks completion; it “contains the ATT + DPT, captured just as the <CHECKPOINT-BEGIN> log record is written.”

Critically, writing these records does not force the buffer pool’s dirty pages to disk. The checkpoint captures which pages were dirty (the DPT, with each page’s recLSN) and which transactions were active (the ATT, with each transaction’s lastLSN) — metadata only. Normal transactions run throughout; the background page cleaner flushes dirty pages continuously and independently of the checkpoint.

One more subtlety governs correctness: “Upon the completion of the checkpoint, the LSN of the <CHECKPOINT-BEGIN> record is recorded in the MasterRecord” (CMU 15-445 L21). Recovery’s Analysis pass therefore starts at the CHECKPOINT-BEGIN LSN, not the CHECKPOINT-END LSN — because any transaction or page change that occurred between begin and end is not captured in the snapshot and must be re-learned by scanning forward from begin.

How a Fuzzy Checkpoint Feeds ARIES Recovery

The payoff is entirely in recovery (The ARIES Recovery Algorithm):

  • Analysis starts at the CHECKPOINT-BEGIN LSN (from the MasterRecord). It initializes its ATT and DPT from the checkpoint’s recorded snapshot, then scans forward to the crash, adding transactions and dirty pages it discovers along the way and removing transactions that reached TXN-END.
  • Redo then starts at the minimum recLSN across all DPT entries. The checkpoint’s recorded DPT is what seeds this: if a page was already dirty at the checkpoint, its recLSN (the LSN that first dirtied it, possibly long before the checkpoint) is preserved in the snapshot, so Redo correctly reaches back far enough to reapply that page’s oldest missing change. This is why recovery’s redo start can legitimately be earlier than the checkpoint marker.
sequenceDiagram
  participant App as Transactions
  participant Log as WAL
  participant BP as Buffer pool / page cleaner
  App->>Log: normal UPDATE records (keep running)
  Log->>Log: write <CHECKPOINT-BEGIN> (snapshot ATT + DPT)
  Note over BP: dirty pages flush continuously,<br/>NOT forced by the checkpoint
  App->>Log: more UPDATE records (still running)
  Log->>Log: write <CHECKPOINT-END> (embeds the snapshot)
  Log->>Log: MasterRecord := LSN of <CHECKPOINT-BEGIN>

Diagram: the timeline of a fuzzy checkpoint. What it shows: transactions never pause; the checkpoint only writes begin/end records embedding an ATT+DPT snapshot, and the MasterRecord is set to the begin LSN. Dirty-page flushing is a separate, continuous background activity. The insight: a fuzzy checkpoint decouples “record where we are” (cheap, instantaneous) from “flush dirty pages” (expensive, continuous) — the sharp checkpoint’s stall came from coupling them (CMU 15-445 L21).

Configuration and Real-World Tuning

PostgreSQL

PostgreSQL runs fuzzy checkpoints continuously in the background. The governing knobs (PostgreSQL — WAL Configuration):

  • checkpoint_timeout — “Maximum time between automatic WAL checkpoints.” Default 5min; valid range 30 s to 1 day. “Increasing this parameter can increase crash recovery time.”
  • max_wal_size — “Maximum size to let the WAL grow during automatic checkpoints.” Default 1GB. It is explicitly “a soft limit; WAL can exceed [it] under special circumstances” such as heavy load or a failing archive_command. A checkpoint is triggered whenever either the timeout elapses or WAL volume crosses this size.
  • checkpoint_completion_target — “the target of checkpoint completion as a fraction of total time between checkpoints.” Default 0.9, chosen to spread the checkpoint’s write I/O across 90% of the interval and thereby “[provide] fairly consistent I/O load while leaving time for checkpoint completion overhead.” Reducing it “is not recommended” because it bunches the flush into a short window followed by an idle one — an I/O spike.

The interaction with full-page writes matters here (see Log Sequence Numbers and the Durability Guarantee): with full_page_writes on (the default), PostgreSQL “writes the entire content of each disk page to WAL during the first modification of that page after a checkpoint” (PostgreSQL — WAL Configuration). A checkpoint therefore resets the full-page-image cycle: immediately after a checkpoint, WAL volume surges as each touched page’s first post-checkpoint modification carries a full image. This is a reason not to checkpoint too frequently — each checkpoint pays a burst of full-page-image WAL.

MySQL InnoDB

InnoDB likewise checkpoints continuously (a fuzzy scheme). “When doing a checkpoint, InnoDB stores the checkpoint LSN in the header of the file which contains this LSN. During recovery, all redo log files are checked and recovery starts at the latest checkpoint LSN” (MySQL 8.4 — InnoDB Redo Log). Rather than a simple time interval, InnoDB paces checkpointing off the redo log’s fill level via innodb_redo_log_capacity (default when unset; since MySQL 8.0.30 it supersedes the older innodb_log_file_size): “If the redo log files occupy less space than the specified value, dirty pages are flushed from the buffer pool to tablespace data files less aggressively … If the redo log files occupy more space than the specified value, dirty pages are flushed more aggressively” (MySQL 8.4 — InnoDB Redo Log). In other words, the redo log’s capacity is the budget: the closer the redo log gets to full, the harder the page cleaner works to advance the checkpoint LSN and free redo space. A too-small redo capacity forces aggressive, latency-spiking flushing; a larger capacity smooths write I/O at the cost of longer recovery.

Failure Modes and Common Misunderstandings

Checkpoint storms. If checkpoints are too infrequent (or checkpoint_completion_target is set low), a huge backlog of dirty pages must be flushed at once. The resulting I/O storm saturates the storage device, foreground query latency spikes, and the database appears to freeze — even though it never explicitly stopped. The fix is to let dirty pages drain continuously (checkpoint_completion_target near its 0.9 default; a well-tuned background writer / page cleaner) so no single checkpoint faces a mountain of dirty pages.

The recovery-time vs. steady-state-I/O trade-off. Frequent checkpoints shorten recovery (less WAL to replay) but cost more steady-state data-file I/O, because a page dirtied and re-dirtied between two close checkpoints gets flushed at each, and each checkpoint re-arms full-page-image logging. Infrequent checkpoints cut steady-state I/O but lengthen worst-case recovery and grow the on-disk log. The right setting is workload-specific; most production systems target a few seconds of worst-case recovery.

“A checkpoint makes the on-disk database consistent.” Only a sharp checkpoint does. A fuzzy checkpoint deliberately leaves the on-disk image inconsistent (smeared) — which is fine, because recovery reconstructs consistency from the log. Treating a fuzzy checkpoint’s data files as a self-consistent backup is a mistake; a consistent backup needs the data files plus the WAL from the checkpoint onward (see Write-Ahead Log on base backups and PITR).

Confusing the checkpoint LSN with the redo start LSN. They differ: Analysis starts at the checkpoint (CHECKPOINT-BEGIN), but Redo starts at the minimum recLSN, which can be older. A page that was dirtied long before the checkpoint and never flushed still needs its oldest change redone.

Alternatives and Variations

  • Sharp / consistent checkpoints remain appropriate for tiny, low-concurrency, or embedded stores where a brief quiesce is tolerable and the simpler recovery is worth it.
  • Incremental / continuous checkpointing — what InnoDB and PostgreSQL effectively do — has no single “checkpoint event”; the checkpoint LSN advances smoothly as the page cleaner flushes. The CHECKPOINT-BEGIN/END records are then more like periodic notarizations of an already-continuous process than a discrete stop-the-world action.
  • Log-structured engines (LSM-trees) reach the same goal differently: when a MemTable is flushed to an immutable SSTable, the WAL segments covering it can be discarded — the SSTable flush is the checkpoint for that data, retiring the corresponding log.

Production Notes

Checkpoint misconfiguration is a recurring source of production incidents. In PostgreSQL, a common symptom is periodic latency spikes correlated with checkpoint completion — usually traced to max_wal_size being too small (forcing frequent checkpoints) or to a workload that dirties pages faster than the background writer drains them. The recommended posture is a generous max_wal_size, checkpoint_timeout in the 5-15 minute range, and checkpoint_completion_target left at 0.9 so the flush is spread. In InnoDB, the analogous lever is innodb_redo_log_capacity: a redo log sized too small causes “furious flushing” as the checkpoint LSN scrambles to keep up, visible as write-latency spikes and stalled commits. In both engines the tuning is a negotiation between the recovery-time objective (favoring frequent checkpoints) and steady-state write throughput (favoring infrequent ones).

See Also

Graph View

Backlinks