WAL & Durability: Why Committed Data Survives

A few lessons ago we pulled the power on a running database. A row that had committed but existed only in memory came back after the restart — from the log alone — and we promised to explain why that replay is guaranteed to be correct. Here's the promise, kept.

But first, understand what's actually frightening about a crash. A clean crash between transactions is easy. The nightmare is a crash halfway through — the moment after your bank account was debited ₹500 and before the other account was credited. Or a crash while a single 8 kB page is being written, leaving it half old bytes and half new. When the machine boots back up, memory is blank, the data files are in some unknown in-between state, and the engine has to answer an impossible-sounding question: what, exactly, had happened? Not approximately. Exactly — down to the last committed transaction, with every half-finished one erased as if it never began.

It answers with one idea, and the idea is a log. This lesson is how that log turns a catastrophe into a boot delay.

Scope: this is the write-ahead log itself — the durability guarantee and crash recovery. How concurrent versions of a row coexist is MVCC: Readers Don't Block Writers; how replicas consume the log is Replication Topologies.

The whole edifice rests on one rule, and the rule is in the name: write the log first. Before any modified data page is allowed to reach the disk, the log record describing that change must already be on the disk. And a transaction is not considered committed until all of its log records — including its COMMIT record — are safely on durable storage. Log first, apply later. Always.

The reason this works is a shift in what you treat as the source of truth. Think of a log record as a durable promise of a change, and the data page as the eventual fulfillment. If the promise is written down first, then no matter when the crash hits, the engine can look at the promises and sort out reality. A change that was promised (a committed transaction) but never made it to the data file? Keep the promise — reapply it. A change that leaked onto a data page but whose transaction never actually promised anything (never committed)? Break it — take it back. Every possible crash state resolves to one clean question: which promises were kept, and which were still pending? The data files can be a mess; the log is the truth, and the log is append-only, so a crash can only ever cut it short — never corrupt what's already there.

So what's actually in this log? Each record carries an LSN — a Log Sequence Number, its unique, ever-increasing address in the log (in Postgres, literally a byte offset). It names the transaction it belongs to, the page it changed, and the change itself: often both a before-image (the old bytes, so the change can be undone) and an after-image (the new bytes, so it can be redone). Mixed in are the control records that run the whole system — COMMIT, ABORT, and CHECKPOINT.

That LSN does quiet, essential work. Every data page also remembers the LSN of the last change written into it — its pageLSN — so during recovery the engine can glance at a page, compare its pageLSN to a log record, and instantly tell has this change already been applied? If yes, skip it. That single comparison is what makes replaying the log idempotent — safe to run twice, safe even if the machine crashes in the middle of recovering.

And a single logical transaction is not one record — it's a chain of them. Here's a real one from pg_waldump, a plain INSERT plus UPDATE:

Five records for one little transaction — the row, the index bookkeeping, the update, and finally the COMMIT. Each record also points back to the one before it, so the WAL is a single append-only chain you can walk in order. And notice the shape of it: the COMMIT is the last link. Everything above it is just intentions written down. No COMMIT record, and as far as the database is concerned, none of it ever happened.

Look again at that record chain and notice where the drama is. The INSERT record, the index records, the UPDATE record — none of them commits anything. They're just described intentions sitting in the log. The transaction becomes real at exactly one instant: when the COMMIT record is flushed to disk and the fsync returns. That flush is the commit. Before it, a crash erases the transaction completely — no harm done, nobody was told it succeeded. After it, the transaction is guaranteed to survive any crash, because the log now contains everything needed to rebuild it.

This is why a commit is fast even though durability sounds expensive. The engine doesn't scatter your changed pages across the disk at commit time — it appends a handful of records to one sequential file and fsyncs. And when a hundred transactions commit at once, they can share a single fsync — group commit — so the fixed cost of forcing the disk is amortized across all of them. One durable append stands in for a storm of random page writes. (The data pages themselves are flushed lazily, later, by the background writer and checkpointer — the same cast from Inside a Database.)

Now the payoff — what actually happens when the machine boots after a crash. It's two moves, and the first one is counter-intuitive.

Repeat history. The engine reads the last checkpoint record to find the redo point, then walks the log forward from there and reapplies every change it finds — not just the committed ones, all of them — dragging the data files back to the exact state they were in at the instant of the crash. Reapplying blindly is safe because of those pageLSNs: any change a page already has is skipped, so redo is idempotent. This feels backwards — why redo work from transactions that were doomed? Because it's simpler and provably correct to first rebuild reality precisely as it was, and then clean up.

Take back the rest. With history repeated, the engine now undoes anything belonging to a transaction that never reached a COMMIT record — using the before-images in the log to roll those changes back. When it's done, exactly the committed transactions remain and every half-finished one is gone, as if it never began. Atomicity and durability, reconstructed from one append-only file. We proved it on a real Postgres — committed a row, opened a second transaction without committing, and killed the process:

v2 — two real flavors of the same idea: classic ARIES explicitly REDOes then UNDOes (SQL Server, DB2, InnoDB via undo logs); Postgres is redo-only — it replays forward and lets MVCC make the uncommitted row invisible (VACUUM reclaims it later). Same guarantee, two engineering routes. And because replay is idempotent, recovery is safe even if the machine crashes DURING recovery.

Reading the algorithm is one thing; running it is another. Below, the database is yours to wreck. Build a write-ahead log — set some keys across a few transactions, commit some and leave others open, drop a checkpoint wherever you like — then crash the machine and step through recovery one phase at a time. Watch REDO drag the store back to the exact instant of the crash (losers and all), then UNDO peel the uncommitted transactions away until what's left is precisely the committed state.

Try this first: crash the seeded log as-is and step REDO, then UNDO — notice that z=999 reappears during REDO (repeat history) and only vanishes during UNDO. Then hit recover again and watch it land on the identical state: recovery is idempotent, which is why it's safe even if the power fails in the middle of recovering. That single property is what makes crash recovery trustworthy rather than terrifying.

There's a loose end in that story. If recovery replays the log, where does it start? The log stretches back to the day the database was created — replaying all of it on every reboot would be absurd. This is what checkpoints are for.

Periodically, the engine does a checkpoint: it flushes all the currently-dirty pages to the data files and then writes a checkpoint record into the log that says, in effect, everything before this point is already safely in the data files. That position is the redo point. On our real Postgres, the checkpoint record spells it out — CHECKPOINT_ONLINE … redo 0/15741A8 — and recovery reads exactly that field to know where REDO begins. Nothing before the redo point ever needs replaying, because it's already on disk.

So the checkpoint interval is a dial you'll meet again and again: checkpoint often and a crash replays only a few seconds of log (fast recovery) but you pay steady I/O and the full-page-image tax more frequently; checkpoint rarely and steady-state is cheap but a crash can mean minutes of replay. Recovery time versus running cost — you're choosing your worst-case downtime in advance.

Here's the twist that turns a recovery mechanism into an architecture. Once you have a single, ordered, append-only record of every change the database ever made, recovery is only the first thing you can do with it.

A replica is just another machine replaying your WAL. Stream the same log records to a second server and have it apply them, and it stays a faithful copy — that's how streaming replication works, and it's why the setting that controls how much detail the WAL carries is literally called wal_level (ours was replica). Turn it up to logical and a change-data-capture pipeline can tail the log the same way, streaming every insert and update into a search index, a cache, a data warehouse, a Kafka topic — without the application ever knowing. The database's private crash-recovery journal becomes the public event stream for half your system.

One append-only log; many readers. Recovery reads it to heal, replicas read it to keep up, CDC reads it to fan out. (The mechanics of keeping replicas consistent — sync versus async, failover, lag — are their own lessons in Replication & Consistency.) Hold onto this idea: it's one of the most reused patterns in all of system design.

The whole guarantee — committed means durable — has one load-bearing assumption: that when the engine asks the operating system to force data to disk with fsync, and fsync returns success, the data is really, truly on the platter. For twenty years, Postgres believed that. In 2018 the community discovered it isn't necessarily true, and the story — now known as fsyncgate — is the best lesson in this whole topic.

When a buffered write fails deep in the storage stack (a flaky disk, a cloud volume hiccup), the Linux kernel would discard the failed page and, on the next fsync, cheerfully return success — reporting that data safely written which had, in fact, vanished. Silent loss, hidden behind a green light. The fix, shipped in Postgres 12 and backported for years, is startling and correct: on an fsync failure Postgres now deliberately crashes itself (a PANIC) rather than trust the situation — because crash recovery will replay the WAL from the last checkpoint and reconstruct the lost writes, since the log still describes them. MySQL's InnoDB and MongoDB's WiredTiger made the same change. When the data files can no longer be trusted, the right answer is: crash, and believe the log. The lesson eats its own tail.

The rest of the fine print is quieter but real. synchronous_commit = off makes commits return before the WAL fsync — memory-speed commits in exchange for a small window (a couple hundred milliseconds) of committed transactions a crash can still erase; fine for a metrics firehose, never for a payment. Torn pages are handled by the full-page images you met in Inside a Database — the reason the first change to a page after a checkpoint is so much larger. And the WAL isn't free-floating: segments pile up until a checkpoint lets them be recycled or archived, so a stuck replica or a misconfigured interval can fill a disk with log nobody is reading. Durability isn't a switch that's simply on — it's a set of dials, and knowing where they're set is the difference between assuming your data is safe and knowing it.

You can see the log, then watch it save your data, in about fifteen minutes. This is Postgres in Docker; every output below is pasted from the real run.

1. Watch one transaction become a chain of log records. Do a small transaction, then dump the WAL it produced with pg_waldump:

2. Crash it mid-transaction and predict. Commit one row, open a transaction with a second row but don't commit, then kill the process with no warning. Before you restart, write down which rows you expect to survive:

Row 2 came back from the WAL although the process was killed before its page was flushed. Row 3 — inside an open transaction with no COMMIT record — is simply gone. That's atomicity and durability, reconstructed from one file. Clean up with docker rm -f wal.

• "COMMIT writes my data into the table on disk." COMMIT flushes the COMMIT log record and fsyncs it. Your changed data pages are still in memory and reach the table files later; recovery rebuilds them from the log if a crash gets there first.
• "The WAL is a backup." It isn't. The WAL is for crash recovery and for feeding replicas and CDC. Archived WAL enables point-in-time recovery on top of a base backup, but the log alone is not disaster recovery — you still need real backups.
• "Crash recovery restores the database from a file." It doesn't copy anything back. It runs an algorithm: replay the log forward from the redo point to repeat history, then discard whatever never committed. Recovery re-derives the state; it doesn't restore it.
• "WAL means zero data loss, period." Only with synchronous commit and an honest fsync. synchronous_commit=off leaves a loss window, and fsyncgate proved fsync itself can lie. "Committed is durable" is true — with fine print you should know is there.
• "The WAL is just overhead / a debug log." It's the opposite of overhead: it's why commits are fast (one sequential append instead of scattered page writes), why your data survives a crash, and what every replica and CDC consumer in your architecture reads from. Turn it off and you don't have a faster database — you have a toy.

• Write the log first. Before a changed data page reaches disk, the record describing it must already be on disk — and a transaction isn't committed until its COMMIT record is fsynced. The log is the durable promise; the data files are the eventual fulfillment.
• A transaction is a chain of WAL records ending in COMMIT (you saw a real one: INSERT → index records → UPDATE → COMMIT). No COMMIT record, no transaction.
• Crash recovery repeats history, then takes back the rest: replay every change since the redo point (idempotent, thanks to per-page LSNs), then discard anything uncommitted. We killed a live database and watched the committed row survive and the uncommitted one vanish — atomicity and durability from one append-only file.
• Checkpoints bound the replay by recording a redo point; the checkpoint interval trades steady-state I/O against worst-case recovery time.
• One log, many readers: the same WAL that heals a crash also streams to replicas and feeds CDC pipelines — a pattern you'll meet all over system design.
• Durability has fine print: fsync can lie (fsyncgate → PANIC-and-replay), synchronous_commit is a dial with a loss window, torn pages need full-page images. "Committed is durable" is a guarantee you configure, not one you assume.

That's the storage engine, end to end: how a write travels, how it's found, how it's stored under read- and write-optimized structures, and now how it survives anything. Next we go back up a level — to the indexes you choose and the tax they levy: Indexes Deep-Dive.