Module 6 · Logging and recovery

Episode 41 Write-Ahead Logging

10 min · SQL · Postgres

Title card of Episode 41: Write-Ahead Logging

▶ This video is coming to YouTube soon.

Why can't a database just write its pages? This episode plays out steal/no-steal and force/no-force, builds a write-ahead log record by record, enforces the WAL rule, and shows group commit, then reads a real UPDATE out of Postgres's WAL with pg_walinspect: the record, the full-page image, the page LSN, and COMMIT waiting for the flush.

Chapters

  1. 0:00Intro
  2. 0:14COMMIT, then the power fails
  3. 0:34Pages alone can't keep both promises
  4. 1:50Steal or not, force or not
  5. 2:30Log first: every change becomes a record
  6. 3:43The WAL rule: the log reaches disk first
  7. 4:49Write-ahead logging in three rules
  8. 5:15Group commit: one flush, many commits
  9. 6:04The code: log, then page; flush, then commit
  10. 6:37Postgres: the same rules, its own records
  11. 7:14Proof: one UPDATE in the WAL
  12. 8:16Proof: COMMIT is the flush
  13. 9:14Trade-offs
  14. 9:38Recap
  15. 10:06Next: Checkpoints and ARIES