Write-Ahead Log (WAL)

The first time I tried to explain to a junior engineer why fsync matters, I drew a picture of memory and disk on a whiteboard and asked: "If we commit a write, return success to the client, and then the power dies before the data hits disk — was the write committed?" They paused, said "I guess not?", and that pause is the entire reason write-ahead logs exist.

Every modern OLTP database — Postgres, MySQL, Oracle, SQL Server, SQLite, MongoDB, every Kafka broker — stays durable through crashes by following one rule: write your intent to a log before you mutate the actual data. If the process dies mid-transaction, the log tells you what was about to happen and what already did. Without the WAL, a crash partway through a write could leave the database in a corrupted state nobody could recover from. With it, the database reads the log on startup, replays the changes that should be there, rolls back the in-flight ones, and reaches a known-good state. The "D" in ACID is delivered by this exact mechanism. There's not a backup plan. There's not a fallback. It's the WAL or nothing.

The protocol, walked through

The five-step dance every OLTP database performs on every committed write looks like this, and the order matters:

1. App sends a write — INSERT INTO orders ....
2. DB constructs a WAL record describing the change ("REDO: insert tuple X with values Y at page Z").
3. DB appends the record to the WAL buffer and calls fsync() to force the buffer to physical disk. This is the step that costs real time — 1-10 ms on SSD, 10-50 ms on HDD, sometimes much more on cheap cloud storage.
4. Only after fsync returns does the DB tell the client "committed". From the client's perspective, this is the moment the write becomes durable.
5. Later, in the background, the actual modified data page in memory gets written to its proper location in the data file. "Later" might be seconds, might be minutes — the data file is the lazy mirror; the WAL is the eager truth.

After a crash, recovery walks the WAL from the last successful checkpoint forward, applying every committed record (REDO) and rolling back any record from a transaction that didn't commit (UNDO). The process can take anywhere from milliseconds to minutes depending on how much WAL is unreplayed. When it finishes, the database is provably in the same state as the last commit the client received.

The WAL is what makes "the database is durable" a true statement. Every other durability property — streaming replication, point-in-time recovery, backups consistent across multiple tables — is built on top of it.

Why "ahead" matters

The "write-ahead" name is precise: the log entry must hit disk before the corresponding data page does. If you allowed the data page to flush first, then crashed before the WAL entry made it, recovery would see a modified data page with no log entry telling it the change happened — and worse, no log entry telling it the change might need to be rolled back. The invariant "WAL fsync precedes data page write" is the entire correctness story for WAL-based recovery. Every WAL implementation enforces it, and the cost of enforcing it is the cost of WAL itself.

The latency tax, and how systems pay it

The fsync after every commit is what gives you durability, and it's also what limits OLTP throughput. A single SSD can fsync about 1,000-5,000 times per second; cheap cloud storage can be much slower (some EBS volumes do 100 fsyncs/sec under contention). If every commit needs its own fsync, your throughput ceiling per database is exactly that number.

Production systems use one of two techniques to break past this ceiling.

Group commit — also called commit coalescing — bundles many in-flight commits into a single fsync. If 100 connections all call commit within the same millisecond window, the database writes all 100 WAL records, calls fsync once, and acknowledges all 100 at once. Each individual commit waits a tiny bit longer (~1-2 ms extra), but throughput improves by 10-100×. Every modern OLTP supports this; it's the difference between 1,000 commits/sec (no batching) and 50,000 commits/sec (batched).

Asynchronous commit (Postgres's synchronous_commit=off) relaxes the rule entirely — the database appends to the WAL buffer and returns "committed" before the fsync completes. The fsync still happens, just shortly later. The trade-off is honest: a crash in the gap between "client got ack" and "fsync finished" loses up to a few hundred ms of recent commits. For workloads where durability of the last 500 ms isn't critical (analytics, sessions, logs), this turns 5,000 commits/sec into 50,000+ commits/sec with almost no other changes. For workloads where it IS critical (payments, ledgers), it's not an option.

Run the numbers

The throughput math here is not hand-waving — it falls straight out of how many times per second your disk can fsync. Say a volume sustains 2,000 fsyncs/sec and the workload is 8,000 commits/sec:

The middle row is the one most teams want and forget they already have: group commit costs each transaction ~1-2 ms of extra wait and buys an 8× throughput multiple with zero durability give-up. The bottom row buys 1,600×-style headroom by betting you can lose the last fraction of a second — fine for session state, fatal for a ledger.

Checkpoints — the "OK to forget" mark

The WAL grows forever if nothing prunes it. The pruning mechanism is the checkpoint: a periodic operation that flushes all dirty pages to the data file and records its position in the WAL. After a checkpoint, every WAL record before that position can be deleted, because the data file is now up-to- date with them — they're no longer needed for recovery.

The tuning of checkpoints is a real engineering decision. Too frequent and the database burns I/O bandwidth flushing pages constantly; foreground transactions slow down because they're competing for disk. Too infrequent and the WAL grows huge, recovery after a crash takes minutes (you have to replay everything from the last checkpoint), and disk usage explodes because old WAL segments can't be cleaned.

What the WAL is also used for

The slick thing about a WAL is that once you have it, it turns out to be useful for more than just crash recovery. The same stream of "here's what changed" records can be:

• Shipped to replicas for streaming replication. The replica receives WAL records, replays them, and ends up bit-for-bit identical to the primary. Postgres's streaming replication is exactly this. See [CONCEPT]replication.
• Decoded into business events via logical decoding. Tools like Debezium read Postgres's WAL, decode it into "row X was inserted with these column values", and ship to Kafka. This is how change-data-capture (CDC) pipelines work without the app having to know they exist.
• Used for point-in-time recovery. With a base backup plus every WAL segment since that backup, you can restore the database to any specific second. Lost a row at 14:32
  ? Restore the 14
  backup, replay WAL up to 14:32
  , recover.

In Kafka, the analogy goes further: there's no separate WAL because the log is the database. Producers append, consumers read, retention is configured by time or size, and the entire system is one giant durable WAL with consumers attached. The mental model from OLTP transfers directly.

A war story about a missing fsync

A team I worked with shipped a Postgres-backed service to a new cloud region. The first week's read benchmarks were beautiful — the database was noticeably faster than their original region on identical hardware. Then they ran the writes benchmark and noticed the same. Writes were 3× faster than expected. Nobody questioned this until a routine failover test simulated a power-loss scenario, and the database came up missing about 8 seconds of the most recent commits.

The root cause turned out to be cheap cloud storage with client-side write caching that lied about fsync. The storage was returning success on fsync() before the data actually hit the underlying medium; on real power loss, the cache was lost. The database was correctly fsync-ing per commit; the storage was correctly violating the contract.

The fix was migrating to a storage class that honored fsync honestly (about half the write throughput, exactly what they should have been getting all along). The deeper lesson is that WAL correctness depends on fsync correctness, and fsync correctness on commodity cloud storage is sometimes a vendor configuration question rather than an OS guarantee. Every production team running on cloud storage should at least know what their disk class promises and what it actually delivers.

When the WAL alone isn't enough

The WAL gives you single-node durability and a stream of changes. It doesn't, by itself, give you:

• Geo-distributed durability. A WAL on one machine survives process crash but not datacenter loss. You need replication ([CONCEPT]replication) for that.
• Cross-table transactional consistency across services. The WAL is per-database; if a transaction spans two services it needs distributed-transaction machinery on top ([CONCEPT]distributed-transactions).
• Logical schema evolution. WAL is physical — it records page changes, not semantic events. For cross-version replication or cross-system data sync, you need a logical decoder on top.

But for every one of those, the WAL is still the foundation underneath. Replication ships WAL records. CDC reads WAL. PITR replays WAL. The structure that started as "let's not lose data on crash" turned out to be the most important plumbing in the entire database.

[CONCEPT]lsm-vs-btrees both use a WAL — it's the layer below the on-disk structure, not above. [CONCEPT]replication is what extends WAL durability across multiple machines. [CONCEPT]transactions-isolation relies on WAL records being properly ordered for the isolation guarantees to hold.