Write-Ahead Logging (WAL)

How databases make commits durable and fast: fsync, group commit, checkpoints, replication and CDC.

How does a database promise that a committed write survives a crash, without making every write slow? The answer, in almost every database and many other systems, is the write-ahead log (WAL): before changing any data, append a record of the change to a log on disk. If the machine dies, replaying the log restores everything that was committed. Knowing how it works explains durability, replication and change data capture all at once.

The rule

Write the log record describing a change to durable storage before the change itself is written to the data files.

Why it's fast

Updating data in place means random writes all over the disk: a table page here, three index pages there. Appending to a log is a single sequential write, which is far faster on both spinning disks and SSDs. So a commit only waits for the log; the actual data pages are updated in memory and written out later, in the background.

client = client "Client" @0,1
db = service "Database" @1,1
wal = store "WAL\n(append-only)" @2,0
mem = cache "Buffer Pool\n(dirty pages)" @2,1
data = store "Data Files" @3,1
client -> db : COMMIT
db -> wal : 1. append + fsync
db -> mem : 2. update in memory
db --> client : 3. OK
mem --> data : 4. later: checkpoint

Durability and fsync

Writing to a file isn't enough: the operating system may hold the bytes in memory. A commit is only durable once the log has been fsynced to disk. That flush is the real cost of a commit, which leads to two common optimizations:

Group commit: many concurrent transactions share one fsync. Throughput goes up sharply, at the cost of a tiny wait.
Relaxed durability: some systems let you acknowledge before the fsync (Postgres synchronous_commit = off, Redis appendfsync everysec). It's much faster, and you can lose the last moment of writes on a crash — though the data stays consistent.

Checkpoints and recovery

The log can't grow forever. Periodically, a checkpoint writes all dirty pages to the data files and records the log position up to which everything is safely applied. After a crash, recovery:

Starts from the last checkpoint.
Redoes every logged change after it.
Undoes (or ignores) changes from transactions that never committed.

Log segments older than the checkpoint can then be recycled — unless a replica or backup still needs them. More frequent checkpoints mean faster recovery but more background writing.

More than crash recovery

Because the WAL is an ordered record of every change, it does a lot of other jobs:

Replication: the primary streams its WAL to replicas, which replay it. This is how Postgres and MySQL replication work.
Point-in-time recovery: restore a base backup, then replay archived WAL up to any moment, such as just before someone dropped a table.
Change data capture: tools such as Debezium read the log to stream row changes into Kafka, feeding search indexes, caches and analytics.

The same idea, everywhere

LSM-tree stores (Cassandra, RocksDB) write to a commit log and a memtable before flushing to SSTables (Database Indexing).
Kafka *is* a log: a partition is an append-only file, replicated across brokers (Kafka).
Consensus protocols (Raft, Paxos) agree on the order of entries in a replicated log.
Event sourcing stores the log of events as the source of truth and derives the current state from it (Event Sourcing & Read Models).

[!INTERVIEW] Use it to explain trade-offs You'll rarely design a WAL from scratch, but it's the clean answer to "what happens to an acknowledged write if the node crashes right after?": "It was fsynced to the write-ahead log before we acknowledged it, so recovery replays it; with async replication, the replica could still be a few milliseconds behind."