The write-ahead rule has two halves
Nothing else has to reach disk at commit. Dirty pages wait in memory and are written whenever convenient.
64×
fewer bytes flushed than writing the pages
A wallet transfer changes four 8 KB pages, 32 KB of random writes. Its log records total about 500 bytes, appended to one file, flushed once.
What reaches disk, and when?
Bad: Pages at commit, and only then
Random writes on every commit; big transactions pin memory.
Good: Steal, no-force
Pages leave whenever, even uncommitted. Recovery redoes committed work, undoes the rest.
Every page remembers the last LSN applied to it
Replay a record only if the page’s LSN is lower. It needs no memory of the crash, so recovery can crash and simply restart.
Crash recovery runs three passes
- Analysis: no commit record means a loser
- Redo: repeat history, losers included
- Undo: roll losers back, logging each reversal
- PostgreSQL skips undo: losers simply stay invisible
Checkpoints trade recovery time for page images
After each checkpoint, a page’s first change logs a full 8 KB image. Checkpoint often for quick replay and a bloated log; rarely, the reverse.
Standbys, point-in-time recovery and CDC each need every change, in order. Where from?
The trick
The same WAL, each reader at its own position. The slowest one decides how much log you keep.
Remember
Log first, pages later. LSNs make replay idempotent, checkpoints bound it, and one log feeds replicas, PITR and CDC.