Write-Ahead Logging & Crash Recovery
The problem
A database can lose power or crash at any instant, including mid-write to its main data files — without a way to know exactly what was and wasn't durably completed at the moment of a crash, a restart could return corrupted or inconsistent data with no way to tell.
Why now
Transactions and ACID already promised durability — a committed write survives a crash — but didn't explain the mechanism. Write-ahead logging is that mechanism: file systems and I/O already established that writes to durable storage are the only guarantee that survives power loss, and WAL is the disciplined protocol for using that guarantee correctly.
Mental model
Write-ahead logging's rule is simple and absolute: never modify the actual data file until the intended change has been durably written to an append-only log first. On restart after a crash, the database replays the log from the last known-good point — anything logged but not yet applied gets redone, anything logged as part of an uncommitted transaction gets undone — turning an unpredictable crash into a deterministic, always-recoverable replay.
Requires
- Transactions & ACIDDatabase Engineering
Write-ahead logging exists specifically to implement the durability and atomicity guarantees ACID already promises; you need those guarantees as the goal before the logging mechanism that achieves them is motivated.
- File Systems & I/OOperating Systems
The WAL itself is a durable, sequentially-written file relying on the exact persistent storage guarantees file-systems-and-io already covered; you need to trust that a completed disk write survives a crash before a recovery protocol built on that assumption makes sense.
Used in
- Every production relational database's crash recovery mechanism (PostgreSQL's WAL, MySQL's redo log)
- Database replication, which is often implemented by streaming the WAL itself to replicas
- Point-in-time recovery, replaying the WAL up to a specific moment before a mistake occurred
- Understanding why a database can safely acknowledge a write the instant it's logged, before it's applied to the main data files
Projects
- Simulate a crash mid-transaction (kill the database process after a WAL entry is written but before the data file is updated) and observe the database correctly recover on restart
- Explain, step by step, the redo and undo phases a database performs when replaying its WAL after an unclean shutdown
Examples
- A database can tell a client 'your write is durable' the instant it's appended to the WAL, without waiting for the (much slower) main data file update to complete
- PostgreSQL's streaming replication works by shipping WAL segments to replicas, which replay them exactly as crash recovery would — replication and crash recovery are the same mechanism applied to two different problems
Resources
Mastery checklist
- I can state the write-ahead logging rule and explain why order (log before data) is the entire guarantee
- I can explain the redo and undo phases of crash recovery
- I can explain why WAL enables both fast durability acknowledgment and replication
- I can trace through a crash scenario and predict what recovery will redo or undo