Dirty Pages in Memory
After a transaction commits its WAL record, the changed data pages often still sit only in the buffer pool as dirty pages. Durability comes from the log, not yet from the data files. The log therefore keeps growing.
What a Checkpoint Does
A checkpoint brings the data files up to date and bounds recovery work.
- It writes dirty pages from the buffer pool to the data files.
- It issues an fsync so those writes are truly on durable storage.
- It records a marker saying the log up to this point is fully reflected in the data files.
Why It Matters
- The WAL before the checkpoint can be trimmed, keeping the log from growing without limit.
- After a crash, recovery only needs to replay log written since the last checkpoint, so restart is fast.
Sharp Versus Fuzzy
A sharp checkpoint stalls writers while it flushes, giving a clean point but a pause. A fuzzy checkpoint flushes gradually in the background, avoiding a stall but recording a range that recovery must reconcile.
Key idea
A checkpoint flushes and fsyncs dirty pages, then records a recovery marker so the WAL can be trimmed and crash recovery only replays recent log.