The Runtime Theory
mediumApplicationDSA#wal#durability#crash-recovery

Explain the write-ahead log and crash recovery

The interviewer is probing whether you understand why the log exists — durability without flushing random pages — and what actually happens at startup: redo, undo, and checkpoints.

The Runtime Theory Team2 min readasked at cockroachlabs · microsoft · amazon

The write-ahead log exists because of a contradiction: a transaction must be durable — on disk — when COMMIT returns, but the pages it touched live scattered across the data files, and flushing them all at commit time would cost dozens of random writes per transaction. The WAL resolves it with one rule: the log must reach disk before the data page it describes. That single ordering rule is the entire architecture.

Changes are appended to the log as sequential records — page ID, offset, old bytes, new bytes, transaction ID, and a log sequence number (LSN) that orders everything. A COMMIT is one more log record: the commit record with this transaction's LSN. The commit path is: append records (in-memory, microseconds), then fsync the log file (1-10ms), then report "committed." The data pages remain dirty in the buffer pool indefinitely — they get flushed later, in the background, in whatever order is convenient, because the log already protects them.

Crash recovery is the log being put back to work. At startup the database finds the last checkpoint — a record of "everything up to LSN X is already on disk." It then replays every log record after X in LSN order, reapplying changes to pages — that's redo, and it's what makes committed transactions survive a crash. The records also carry old values, which powers the undo side: a transaction whose log shows writes but no commit record gets rolled back during recovery — either by redo-time checks (Postgres) or by undoing from the log. Either way, the invariant after recovery is: committed work is present, uncommitted work is gone, and the database looks like it ran transactions in exactly one order.

Three mechanics make recovery fast and safe. Checkpoints bound it: recovery time equals the log written since the last checkpoint, so checkpoints trade background write work for bounded startup time. Torn pages — a crash mid-page-write leaving a half-written 8KB page — are handled with page checksums plus full-page images in the log: the first log record touching a page after a checkpoint stores the whole page, so redo can overwrite the torn remnant. And group commit batches many transactions' log records into one fsync, because the commit record only needs the log flushed up to its LSN — everything behind it comes along free.

The tradeoff to name: synchronous_commit = off skips the commit fsync, trading durability — the last ~0.5s of commits can be lost on power failure — for an order of magnitude less commit latency. The WAL is the reason that knob exists: without the log, there'd be nothing to skip.

This answer walks

Follow-ups they'll push on

  1. 01What does a checkpoint actually do and why does recovery need it?
  2. 02How does the database survive a torn page write?
  3. 03What's the difference between losing power and the process crashing?

More interviews in this topic

One dispatch a week

The trace behind each question, the tradeoff that explains it, and one technical dispatch per week — no noise.

One technical dispatch per week. No noise.