Kill a database process mid-transaction — kill -9, a power cut, a kernel panic — and when it restarts, your committed data is intact and no half-finished transaction left a mess. That resilience is not luck and it’s not magic. It’s one boring, universal technique, and once you see it you’ll recognize it everywhere: the write-ahead log.
The problem: a write is not one write
Updating “the data” is rarely a single atomic act. A single logical change — move $100 between two accounts — might touch several pages on disk: the two rows, an index or two, maybe a free-space map. Those pages are scattered, and the disk applies writes one at a time.
So there’s always a window where some of the change is on disk and some isn’t. Crash in that window and you have a torn state: money debited but not credited, a row updated but its index still pointing at the old value. The database is corrupt, and worse, it may not even know it.
You might think: just flush everything before acknowledging. But flushing many random pages on every single commit is brutally slow — random I/O is the enemy, on spinning disks and SSDs alike — and it still doesn’t fix atomicity, because the crash can land between two of those flushes. Flushing harder doesn’t close the window; it just makes it slower.
The move: write your intent first
Write-ahead logging solves both problems — the slowness and the torn state — with one rule, the one in its name:
Before modifying the actual data, append a record of the change to a log, and make that log record durable on disk first.
The intent is written ahead of the change it describes. The sequence for a write becomes:
- Append the change to the WAL — a sequential write to the end of one file — and
fsyncit so it’s genuinely on disk. - Acknowledge the client. As of this instant the transaction is committed, because its record is durable.
- Apply the change to the real data pages later — lazily, in the background, in batches.
Look at what this buys. Commit latency is now one sequential fsync, not a scatter of random page writes — dramatically faster, because sequential I/O is what disks are good at. And durability no longer depends on the data pages being written at all, because the log already holds everything needed to reconstruct them.
Step 3 happening “later” is the part that feels wrong the first time and is actually the whole point. The data pages in memory are updated for readers; their flush to disk is deferred and coalesced. The log is the source of truth in the meantime.
Recovery: REDO forward, UNDO back
Now the crash. The database restarts, opens its WAL, and replays it. Every entry carries a log sequence number (LSN) — a monotonic position — so the log is a totally ordered history. Recovery does two things:
REDO — for transactions whose COMMIT record is in the log but whose data pages hadn’t been flushed yet, re-apply the changes. This is what makes durability real: you were told the write succeeded, so recovery reconstructs it. In the diagram, transaction T1 (LSNs 101–103, ending in COMMIT) is redone.
UNDO — for transactions that were in flight but never committed (no COMMIT record — LSN 104’s SET z=2, cut off by the crash at 105), roll back any effects. This is what makes atomicity real: a half-finished transaction leaves nothing behind. Not every engine runs this pass: PostgreSQL’s MVCC never makes an uncommitted transaction’s row versions visible, so its crash recovery is REDO-only.
REDO the committed, UNDO the uncommitted, and the result is guaranteed atomic and durable — the A and D of ACID, delivered by replay. Recovery doesn’t scan the entire log from the beginning of time; it starts from the last checkpoint (below).
Two properties make replay safe. It’s idempotent — re-applying an entry that was already applied is a no-op, so recovery can itself crash and restart without harm (LSNs let it skip anything already reflected on a page). And it’s forward-only in the sense that the log fully determines the outcome, no matter where the crash fell.
Checkpoints: keeping the log from being infinite
If the WAL only grew, it would eat the disk and make recovery replay years of history. Checkpoints bound both. Periodically the database flushes all currently-dirty data pages to their real homes and writes a checkpoint record. Everything before a completed checkpoint is now safely in the data files, so those WAL segments can be recycled or archived, and recovery only ever needs to start from the last checkpoint.
There’s a tension worth knowing operationally: frequent checkpoints keep recovery fast and the log small, but each one is a burst of the very random page I/O the WAL was avoiding, so it costs steady-state throughput. Rare checkpoints do the opposite. This is a real tuning knob (checkpoint_timeout, max_wal_size in Postgres), and a spiky write workload plus aggressive checkpointing can produce periodic latency humps that look mysterious until you know to look here. The compaction/flush events in LSM-tree engines are the same category of “background housekeeping that’s secretly a latency event.”
Two things you get almost for free
The WAL was built for crash recovery, but because it’s a complete, ordered record of every change, two more capabilities fall out of it — and this is why the concept is worth carrying around.
Replication. To keep a replica in sync, don’t copy data pages — ship the WAL and let the replica replay the same log to reach the identical state. That’s exactly how PostgreSQL streaming replication works — the log is the replication stream. MySQL applies the same idea one level up: replicas replay the binlog, a separate logical log, rather than InnoDB’s redo log. And this connects to consensus: systems like the ones built on Raft are, in essence, agreeing on the order of a replicated log — the same “durable ordered log drives identical state machines” idea, one layer up.
Point-in-time recovery. Keep a base backup plus all subsequent WAL, and you can replay forward and stop at any chosen LSN or timestamp — for instance, the moment just before a bad migration or an errant DELETE. Your backups stop being daily snapshots and become a continuous timeline you can rewind to any second. That capability is a WAL side effect.
The same shape shows up outside databases, too. A filesystem journal is a WAL for metadata. Event sourcing makes the log the primary record and treats state as a projection of it. Kafka is, from one angle, a durable append-only log you let many consumers replay. “Append the intent to a durable ordered log, derive everything else from it” is one of the most reused ideas in all of systems design.
What it costs, honestly
WAL isn’t free. Every change is written twice — once to the log, once to the data pages — which is write amplification, and it’s the price of durability. The fsync on the commit path is a real latency floor: group commit (batching many transactions’ flushes into one fsync) is how databases claw throughput back, at the cost of a little added latency per commit. And durability is only as honest as the hardware — if a disk or a virtualization layer lies about fsync and buffers the write in volatile cache, the WAL’s guarantee evaporates in exactly the crash it was meant to survive. On critical systems, whether fsync truly reaches stable storage is a question worth actually verifying rather than assuming.
The rule worth remembering
Write the intent to a durable, ordered log before you touch the data; then a crash is just a replay — REDO the committed, UNDO the rest. That one discipline delivers atomicity and durability, turns slow random commits into fast sequential ones, and hands you replication and point-in-time recovery as bonuses. When you see append-only logs at the center of a storage system, this is why they’re there.
Comments