Q13 — Why do databases use write-ahead logs (WAL)?

What happens when a database crashes mid-write?

A database keeps its data on disk — that's the whole point of persistence. But disk writes are not instantaneous. When you update a row, the database reads the page containing that row into memory, modifies it in memory, and then writes the modified page back to disk.

What if the machine crashes between the read and the write? The modified page never made it to disk. The data the user committed is gone.

Worse: what about complex operations that modify multiple pages? Suppose you're inserting a row that causes a B-tree node to split. The split requires writing the modified original node, a new sibling node, and the updated parent node — three separate disk writes. If the machine crashes after the first write but before the second and third, the B-tree is in a corrupt, inconsistent state. Not just missing data — structurally broken data.

The database needs a way to survive crashes with its data intact and consistent.

Why not just flush every change to disk immediately?

You might think: write every change directly to disk the moment it happens, with no in-memory buffering. Then there's no window for data loss.

This would work, but it's devastatingly slow. Each write operation would require a random disk write to update the exact page being modified. For a B-tree insert that touches 3 pages, that's 3 random disk writes just to insert one row. Under any reasonable write load, the database would spend all its time waiting for disk seeks.

The database needs both safety (no lost commits) and performance (don't wait for slow disk operations on every write). These goals seem in conflict — but the WAL resolves them.

What is the core insight of the write-ahead log?

Sequential writes are fast. Random writes are slow.

Instead of writing modified pages to their exact locations on disk (random writes), write a description of what you're about to do to a sequential log file — then do the actual page modifications in memory, and flush the modified pages to disk lazily, in the background.

The log is append-only — every new entry goes at the end. No seeking, no updating existing positions. Writing to the log is as fast as your disk's sequential write throughput.

"Write-ahead" means: write to the log before making the actual change. The log record must hit durable storage before the operation is considered complete. The actual page update can happen later.

How does the WAL enable crash recovery?

When the database starts up after a crash, it doesn't know which in-memory page modifications made it to disk and which didn't. The actual data files might be in any state.

But the WAL is append-only and sequential. Everything written to the WAL before the crash is intact (as long as it was flushed to disk, which fsync ensures on commit). Everything after the crash point is simply absent.

Recovery works by replaying the log: read through the WAL from the last known good checkpoint, and for each committed transaction's log records, re-apply the changes to the data files. Any transaction that was in progress when the crash happened (no commit record in the log) is rolled back by applying the inverse operations.

This is why the log must be written before the actual change — if the machine crashes before the log record is written, no change was visible to anyone (the transaction hadn't committed yet). If it crashes after the log record but before the actual page write, recovery will replay the log and apply the change. The outcome is always consistent.

What exactly is written to the WAL?

Each WAL record describes a specific change to the database:

The transaction ID it belongs to
Which page was modified
What was there before (the "undo" information)
What it should be after (the "redo" information)
The Log Sequence Number (LSN) — a monotonically increasing number identifying the position in the log

A transaction generates WAL records as it makes changes. On commit, a "commit" record is written to the WAL and the WAL is flushed to disk (fsync). Only after this flush returns successfully is the application told that the transaction committed.

The actual dirty pages in the buffer pool can be flushed to their permanent locations in the data files later — in the background, in batch, when convenient. This background process is called the checkpoint: periodically, the database flushes all dirty pages to disk and records in the WAL "everything up to LSN X has been written to the data files." On recovery, replay only needs to start from the latest checkpoint, not the beginning of time.

What else does the WAL enable beyond crash recovery?

The WAL turns out to be useful for far more than just crash recovery:

Replication. A replica can receive the WAL stream from the primary and replay the exact same changes on its own copy of the data. This is how streaming replication works in PostgreSQL — the replica stays current by consuming the WAL. No special replication logic needed: just replay what the primary logged.

Point-in-time recovery (PITR). By archiving the WAL continuously, you can restore a database to any specific moment in the past. Start from a base backup, then replay WAL records up to the desired timestamp. This is how you recover from accidental DELETE FROM users at 2:47pm — restore the 2am backup and replay the WAL up to 2:46pm.

Logical replication. Instead of replaying raw WAL at the page level, decode the WAL into logical operations ("INSERT INTO users VALUES...") and replay those. This enables replicating to different database versions, different database systems, or selective replication of specific tables.

Change data capture (CDC). Tools like Debezium read the database's WAL and emit a stream of change events to Kafka or other systems. Any insert, update, or delete in the database becomes an event other systems can react to — without polling the database.

The mental model

code

Problem: random writes to data files on every change = slow
         crash mid-write = corrupt data files

WAL solution:
  Write to log (sequential, append-only, fast) BEFORE changing data files
  Flush log to disk on commit → durability guaranteed
  Data files updated lazily in background (checkpoint)

Crash recovery:
  WAL is intact up to crash point
  Replay committed transactions from last checkpoint forward
  Roll back in-progress transactions

Write path:
  Change → WAL record appended → commit → WAL flushed (fsync) → "OK" to client
                                         ↓
                              Background: dirty pages written to data files (checkpoint)

WAL also enables:
  Replication → stream WAL to replicas
  PITR → archive WAL, replay to any point in time
  CDC → decode WAL into logical change events

The WAL exists because it converts the impossible combination of "fast writes" and "durable writes" into a tractable one: be fast by writing sequentially to the log, be durable by flushing the log before acknowledging the commit. The actual data files can catch up at their own pace.