Write-ahead logging and recovery
A database changes pages in memory and writes them back later, so how does a commit survive a power cut? Follow two transactions through PostgreSQL's write-ahead log (WAL): every change is logged before its page may reach disk, COMMIT waits only for the log, and after a crash, replay from the last checkpoint brings back every committed change while the unfinished one stays invisible.
5 quick questions
Pick an answer to see why. Your score saves with your progress.
01T1 runs UPDATE and then COMMIT. What must be on disk before PostgreSQL answers COMMIT?
With synchronous_commit on (the default), COMMIT flushes the WAL up to its commit record and waits for the disk. The data page can be written much later; the log is enough to redo it after a crash.
02Page 1 in shared buffers has page LSN 4, but the WAL on disk ends at record 3. PostgreSQL wants to write page 1 to the data file. What happens?
The write-ahead rule: a page may reach disk only after the WAL records that changed it. The page LSN says which record that is, so the WAL is flushed up to 4 first.
03After the power cut, where does crash recovery start reading the WAL?
A checkpoint guarantees that every change logged before it is already in the data files, so replay can start from its redo point and roll forward.
04Why does the first change to a page after a checkpoint put a full copy of the page in the WAL (full_page_writes)?
A torn page cannot be repaired from a small row-level record. Replay puts the whole logged copy back, then applies later records on top.
05T2 updated Holes, and its new row version even reached the data file, but T2 never committed. What do readers see after recovery?
PostgreSQL needs no undo pass: an aborted transaction's row versions stay in the page but MVCC never shows them, and VACUUM clears them later.
Glossary
- Write-ahead log (WAL)
- An append-only file of records, each describing one change. A change is logged here before the changed page may be written to the data files.
- LSN and page LSN
- Log sequence number: the position of a record in the WAL (a byte offset that only grows). Each page's header stores the LSN of the last record that changed it; the page may not reach disk before the WAL does.
- Shared buffers
- PostgreSQL's pool of 8 kB page slots in memory, where pages are read and changed. A changed page not yet written back is called dirty.
- Row version (MVCC)
- Multiversion concurrency control: an UPDATE adds a new version of the row instead of overwriting it, and each reader sees only the versions its snapshot allows, so uncommitted or aborted versions stay hidden.
- VACUUM
- PostgreSQL's clean-up command. It removes row versions that no transaction can see any more, such as those left behind by an aborted transaction.
- Checkpoint
- A point where every dirty page has been written to disk and a checkpoint record is logged. Crash recovery replays from here.
- Redo (roll-forward)
- Replaying WAL records after a crash to bring the data pages up to date.
- Full-page write
- A copy of a whole page stored in the WAL with the first change to that page after a checkpoint, so a half-written (torn) page can be restored.
Notes and sources
- Real LSNs are byte positions in the WAL, printed like 0/16B3748, and a page stores the position just past its last record. The figure numbers the records 1, 2, 3, 4 to keep them readable.
- In a table this small, both rows would share page 0. The figure puts Holes on page 1 so each transaction changes its own page.
- Real recovery starts at the checkpoint’s redo point, which can come a little before the checkpoint record itself, because work goes on while a checkpoint runs.
- With full_page_writes on (the default), replay puts back each page copy whatever the page on disk says. Without page copies it compares LSNs: had full_page_writes been off here, record 2 would be redone (page 0 says 0) and record 4 skipped (page 1 already says 4).
- Many other databases (those following ARIES, as in most textbooks) also log enough to undo an unfinished transaction and roll it back after redo. PostgreSQL needs no undo pass: row versions from an aborted transaction stay invisible and VACUUM removes them.
- COMMIT waits for the WAL flush because synchronous_commit is on by default. Turned off, COMMIT returns sooner and a crash can lose the last few commits, but never leaves the data inconsistent.
- SQLite’s WAL mode works differently inside (it appends whole changed pages to a -wal file and copies them back at a checkpoint), but the promise is the same. The Python demo kills a process, not the power, so it shows recovery, not a real power cut.
- PostgreSQL documentation: Write-Ahead Logging (WAL)
- PostgreSQL documentation: WAL Configuration (checkpoints)
- PostgreSQL documentation: WAL Internals (LSN, recovery)
- PostgreSQL documentation: Write Ahead Log settings (full_page_writes, synchronous_commit)
- PostgreSQL documentation: Database Page Layout (pd_lsn)
- PostgreSQL documentation: Routine Vacuuming
- PostgreSQL documentation: Concurrency Control, Introduction (MVCC)
- PostgreSQL source: src/backend/access/transam/README
- Silberschatz, Korth and Sudarshan, Database System Concepts (7th ed.), chapter 19: Recovery System
- Mohan et al., ARIES: A Transaction Recovery Method (ACM TODS, 1992)
- SQLite: Write-Ahead Logging
- Python Library Reference: sqlite3
Quiz Unlocks after the last step.