The Problem: Crashes in the Middle of a Write

You transfer ₹5,000. The database must subtract it from your account and add it to your friend's. Halfway through, the power goes out. When the database restarts, did the money vanish? Was it created twice?

Databases solve this with one of the most important ideas in systems engineering: the write-ahead log (WAL). Before changing the real data, write down what you're about to do in an append-only log, and make sure that log is safely on disk.

🎯 Quick Answer (30-Second Read)

  • Rule: log the change first, apply it to data files later.
  • Commit = log record is on disk (fsync). Data pages can be updated afterwards, in the background.
  • After a crash, the database replays the log to redo committed changes and ignore incomplete ones.
  • Why it's fast: appending to one log file sequentially is much cheaper than updating many random data pages.
  • Bonus uses: replication (ship the log to replicas), point-in-time recovery, and change data capture.
  • Used by PostgreSQL (WAL), MySQL/InnoDB (redo log), SQLite (WAL mode), and the same idea powers Kafka and Redis AOF.

How It Works

sequenceDiagram participant C as Client participant DB as Database participant W as WAL (on disk) participant D as Data files C->>DB: UPDATE balance (transfer ₹5,000) DB->>W: Append: "acct 1 −5000, acct 2 +5000" DB->>W: fsync (flush to disk) DB-->>C: COMMIT ✓ Note over DB,D: Later, in the background DB->>D: Write changed pages (checkpoint)

The client gets "committed" as soon as the log is durable. The data files may be behind, and that's fine, because the log has everything needed to bring them up to date.

Crash Recovery

flowchart LR CR["💥 Crash"] --> RS["Restart"] RS --> CP["Find last checkpoint
(data known safe up to here)"] CP --> RP["Replay WAL records
after the checkpoint"] RP --> OK["Committed changes restored,
uncommitted ones discarded"]
  1. Find the last checkpoint: the point where all data pages were known to be written.
  2. Replay every log record after it.
  3. Changes from transactions that never committed are not applied.

Result: every committed transaction survives, and no half-done transaction leaks into the data. That's the D (durability) and much of the A (atomicity) in ACID.

Key takeaway: the log is the source of truth. Data files are just a cached, convenient view of it that can always be rebuilt from the log.

Why Logging First Is Faster

Without WAL
Every commit must write all changed data pages, scattered across files, to disk before replying. Many random writes per transaction.
With WAL
Every commit appends a small record to the end of one file. Sequential writes, and many commits can share one flush (group commit).

Checkpoints: Keeping the Log Small

If the log grew forever, recovery would replay years of history. Periodically, the database runs a checkpoint: it writes all changed pages to the data files and records that point. Log segments before the checkpoint are no longer needed for crash recovery and can be recycled or archived.

Setting (Postgres) What it controls
checkpoint_timeout Maximum time between checkpoints
max_wal_size How much WAL can build up before a checkpoint is forced
synchronous_commit Whether commit waits for the WAL flush (durability vs latency)
wal_level How much information is logged (needed for replication / logical decoding)

The Log Does More Than Recovery

flowchart LR P["Primary database"] -->|"stream WAL"| R1["Replica 1"] P -->|"stream WAL"| R2["Replica 2"] P -->|"archive WAL"| AR[("Backup storage
point-in-time recovery")] P -->|"logical decoding"| CDC["Change data capture
(e.g. Debezium → Kafka)"]
  • Replication: replicas replay the primary's WAL to stay in sync.
  • Point-in-time recovery: a base backup plus archived WAL lets you restore to any moment, such as "just before someone ran the bad DELETE."
  • Change data capture: tools read the log to stream every change to other systems.

The Same Idea Everywhere

System Its log
PostgreSQL WAL
MySQL (InnoDB) Redo log (plus binlog for replication)
SQLite Rollback journal or WAL mode
Redis AOF (append-only file) for persistence
Kafka The log is the product: an append-only, replicated log
LSM-tree databases (RocksDB, Cassandra) Commit log written before in-memory tables

Frequently Asked Questions

What is a write-ahead log in a database?

A write-ahead log is an append-only file where a database records every change before applying it to its data files. A transaction is committed once its log records are safely on disk, so after a crash the database can replay the log to restore committed changes.

Why is WAL faster than writing data directly?

Appending to a single log file is a sequential write, which is much faster than updating many data pages scattered across disk. Databases can also group several commits into one flush. Data pages are written later in the background during checkpoints.

How does PostgreSQL use WAL for replication?

Replicas receive the primary's WAL records, either streamed continuously or from archived files, and replay them to stay in sync. Logical decoding can also turn WAL into a stream of row changes for other systems.

Conclusion

The write-ahead log is the quiet hero of databases: write intentions to an append-only log, flush it, then apply changes lazily. That one rule gives crash recovery, fast commits, replication, point-in-time recovery and change data capture, and the same idea powers systems from Postgres to Kafka.

Sources

Last verified: 2 November 2026.

Related reads: Bloom Filter Explained · Message Queue Explained · PostgreSQL vs MySQL · Event-Driven Architecture Explained

I explain the tech behind everyday products. Follow on LinkedIn or X, or invite me to speak.