The write-ahead log: what COMMIT actually promises

Lesson 1 left a question hanging: pages are modified in RAM and written to disk later: so what happens when the power dies in between? The answer is the oldest, most load-bearing idea in database engineering. Write-ahead logging: before any change touches a data page on disk, a record describing that change must already be safely on disk in an append-only log. The data files can then be lazy, torn, or stale: because the log can always reconstruct the truth. COMMIT stops meaning "the data is written" and starts meaning something better: "the story of this transaction is durably written, and stories can be replayed."

Crash it yourself

What you should have observed at the two decisive crash sites: crash after the WAL fsync but before anything else → recovery replays the log and the update survives: correctly, because the client was told "committed." Crash before the fsync → the update vanishes: also correctly, because the client never got its acknowledgment. The fsync is the exact instant the promise becomes real; everything else is choreography around it. The protocol's two rules, formally: (1) a page may not be written to disk before the log records that produced it (write-ahead); (2) a commit may not be acknowledged before its log records are fsynced. Everything else: when data pages actually get written: becomes a pure performance decision.

Why the log makes everything else fast

Here's the beautiful inversion: the WAL isn't a tax on performance, it's the source of it. Because the log holds the truth, data pages can be flushed lazily, in bulk, sorted by disk location, during quiet moments: instead of forcing a random 8KB page write on every commit, the engine does one small sequential append. Sequential I/O is the one thing every storage device loves (Lesson 1's ladder; Lesson 3's entire LSM thesis: and notice an LSM database is essentially a system that decided the log should just be the database). One problem remains: at high concurrency, even tiny fsyncs become the bottleneck, because an fsync costs the same whether it carries one commit or forty:

Group commit is why databases get faster per transaction under load: the batch fills up, and each fsync amortizes across more commits. Every serious engine does it (PostgreSQL's commit_delay, MySQL's binlog group commit), and the same trick reappears at every layer of the stack: Kafka producer batching, Raft leaders shipping entries in batches (replication course), even your network card coalescing interrupts. When the fixed cost per operation dominates, make the operation carry passengers.

Checkpoints: keeping recovery short

If the log is the truth, why keep data files at all? Because replaying a month of WAL takes a month. A checkpoint periodically flushes all dirty pages to disk and records "everything before LSN X is now reflected in the data files": so recovery only replays the tail since the last checkpoint. The tuning tension writes itself: frequent checkpoints → fast recovery, constant flush I/O competing with your workload; rare checkpoints → smooth running, long recovery (and every page carries the LSN from Lesson 1's page header so recovery knows exactly which records it can skip: that planted detail, cashed). Postgres's checkpoint_timeout/max_wal_size, MySQL's fuzzy checkpointing: same dial. And the full industrial-strength version of all this (handling crashes during recovery, undo for uncommitted changes that did reach disk) is the ARIES algorithm: three rules deep, decades wide.

Check yourself