Skip to main content
databases

Write-ahead log (WAL)

10 min read
Fully authored

The append-only log that gives databases durability without slow fsyncs everywhere.

Every relational database has an append-only log that records every change BEFORE the change is applied to the main data files. That log is the WAL. It exists because fsync is slow (~10ms on SSD, ~100ms on network), but you can amortize one fsync across thousands of writes.

Without WAL (naive)

1. Client: UPDATE users SET x = 5 WHERE id = 42;
2. DB updates page 1024 in memory
3. DB writes page 1024 to disk (random 8KB write) — 10ms fsync
4. DB responds to client

Every write = 1 fsync. Max ~100 writes/sec on SSD. Random I/O is death.

With WAL (production)

1. Client: UPDATE users SET x = 5 WHERE id = 42;
2. DB appends log record to WAL (sequential write to end of file)
3. DB fsync on WAL [batched with 999 other writes!]
4. DB updates page 1024 in memory ONLY
5. DB responds to client
Later, checkpointer: flush dirty pages to data files (async, coalesced)

Batched fsync = ~10K writes/sec on same SSD. Sequential I/O is fast.

The 3 things WAL enables

  1. Durability — commit = “WAL record persisted”. Even if the DB crashes after step 4, replay the WAL on restart to recover.
  2. Replication — stream the WAL to replicas. Postgres's physical replication, MySQL's binlog, Aurora's log-only storage layer all do this.
  3. Point-in-time recovery — WAL + a base backup = recover to any moment. Enterprise DBAs configure this for compliance.

The tuning knob every senior engineer knows

synchronous_commit in Postgres. on = fsync on commit (safe, slow). off = fsync every 200ms (fast, up to 200ms of data loss on crash). Set it based on business tolerance for data loss during a crash — analytics can accept off; payments cannot.

Applied in these systems

URL Sh Ch 5.5 — Postgres durabilitySlack Ch 4 — message durability

Practice what you just read

Every foundation concept has a companion quiz to close the loop.

Take the URL Shortener quizOpen the flagship problem

More in databases

Database Types — SQL, NoSQL, NewSQLIndexes — the promise and the costB-treesLSM treesIsolation levels