Chapter 22: Write-Ahead Log and Crash Recovery
The WAL (Write-Ahead Log) is how databases achieve both durability and performance. It's the most critical component for data safety.
The Problem
Writing data pages to disk is slow and random. If you crash mid-write, pages are corrupted. Solution: write a sequential log FIRST.
How WAL Works
Transaction: UPDATE accounts SET balance=900 WHERE id=1
Step 1: Write change to WAL (sequential, fast)
WAL: [LSN=1001, txn=42, page=5, offset=3, old=1000, new=900]
Step 2: Modify page in buffer pool (RAM only)
Buffer pool: page 5 now has balance=900 (DIRTY)
Step 3: Return "COMMIT OK" to client
(WAL is on disk = durable, even though data page isn't)
Step 4: Eventually, checkpoint flushes dirty pages to disk
(Background process, not in critical path)
Crash Recovery
// On startup after crash:
// 1. Find last checkpoint (known-good state)
// 2. Replay WAL from checkpoint forward:
// - REDO committed transactions (apply changes)
// - UNDO uncommitted transactions (roll back)
// 3. Database is consistent again. Ready to accept connections.WAL Configuration
# postgresql.conf
wal_level = replica # minimal, replica, or logical
fsync = on # NEVER turn off in production!
synchronous_commit = on # wait for WAL flush before confirming commit
wal_buffers = 16MB # WAL write buffer size
checkpoint_timeout = 5min # max time between checkpoints⚠️ Never Disable fsync
Setting fsync=off means the OS can lie about writes being on disk. A power failure will corrupt your database. Only disable for disposable test databases.
Key Takeaways
- WAL = sequential log of all changes, written BEFORE data pages
- Enables durability: COMMIT is safe once WAL is fsynced
- Enables crash recovery: replay WAL to restore consistent state
- Also enables replication: stream WAL to replicas
- Checkpoints periodically flush dirty pages, allowing old WAL to be recycled