Write-Ahead Logging technical poster: append-only log, fsync on commit, checkpoint, REDO and UNDO recovery phases
System Design

Write-Ahead Logging

How a database survives being killed mid-write — by writing its intentions down first.

Core concept

Before a storage engine touches a data page, it appends a description of the change to a sequential log and forces that log to stable storage. The data pages themselves are updated later, lazily, in any order. The log is the truth; the pages are a cache of the truth.

The trade is precise: you buy durability with one sequential fsync per commit instead of paying for many random page writes on the critical path. A commit becomes a small append at the end of one file — the cheapest durable write a disk offers — and everything expensive is deferred to a background flusher that can batch and reorder freely.

Key components

The append-only log

A single growing file of records, each stamped with an LSN (Log Sequence Number), a transaction id, the page touched, and enough information to redo it — and often to undo it.

fsync at commit

The commit record must be on the platter, not merely in the OS page cache, before the client hears “committed.” Skip the flush and you have speed with a lie attached.

Dirty pages & buffer pool

Modified pages sit dirty in memory and are written out on their own schedule. Losing them to a crash is harmless — every change they held is already described in the log.

Checkpoints

A marker recording that everything before it is safely on disk. Recovery starts there instead of at the beginning of time, which is what makes restart time bounded rather than proportional to uptime.

How it works

  1. A transaction modifies a row. The engine builds a log record describing the change — enough to reapply it (redo), and usually enough to reverse it (undo) — and appends it to the log buffer.
  2. The page in the buffer pool is modified in memory and marked dirty. Nothing is written to the data file yet.
  3. On COMMIT, the engine appends a commit record and fsyncs the log up to that LSN. Only now does the client get an acknowledgement. This is the write-ahead rule: log first, pages later.
  4. A background writer flushes dirty pages whenever convenient. It may reorder, batch, or coalesce them because correctness no longer depends on their timing.
  5. Periodically a checkpoint is taken: dirty pages up to some LSN are flushed and that LSN is recorded. Log before it can be recycled.
  6. After a crash, recovery scans forward from the last checkpoint and REDOes every logged change, rebuilding the exact page state at the moment of death — including changes from transactions that never committed.
  7. Then it UNDOes those uncommitted transactions, using the undo information, so the database returns to a state containing all committed work and none of the unfinished work.
The rule underneath the rule. A page may not be written to disk before the log records describing it are durable. If you flush the page first and crash before the log reaches disk, recovery has no record of a change that has already happened — and cannot undo it. That ordering constraint, not the log file itself, is what WAL actually buys you.

Real-world applications

  • PostgreSQL WAL — the same log drives crash recovery, streaming replication, and point-in-time restore. One mechanism, three products.
  • SQLite in WAL mode — readers keep reading the old pages while a writer appends, which is how a single file supports concurrent reads during writes.
  • InnoDB redo log — MySQL commits into a fixed-size circular redo log; its size directly bounds how long restart takes.
  • etcd & Raft logs — consensus systems fsync the log before acknowledging a follower, for exactly the WAL reason: an acknowledgement you cannot honour after a reboot is a correctness bug.
  • LSM engines (RocksDB, Cassandra) — the in-memory memtable is protected by a WAL so an unflushed memtable survives a crash.
  • Filesystem journals (ext4, XFS) — the identical idea one layer down: journal the metadata operation, then perform it.

Checkpoint — answer before you move on

  1. Why is one sequential fsync of a log cheaper than writing the modified data pages directly at commit time? Think about where the disk head goes, and how many transactions can share a single flush.
  2. Recovery REDOes changes belonging to transactions that never committed, and only then UNDOes them. Why not simply skip the uncommitted ones during REDO? Consider what the engine knows about page state before it has replayed the log, and whether a partially-flushed page can be trusted.
  3. What specifically goes wrong if a dirty page reaches disk before its log record is durable, and the machine loses power right then? Ask what evidence recovery has that the change ever occurred.