Write-Ahead Logging (WAL)

Every database you've ever used — PostgreSQL, MySQL, SQLite — relies on the same fundamental trick to guarantee your data survives a crash: before changing anything on disk, write the change to a sequential, append-only log first. This is Write-Ahead Logging (WAL).

The idea is deceptively simple. If the system crashes after writing to the log but before updating the actual data files, it can replay the log on startup and finish the job. If it crashes before the log entry is complete, the partial entry is safely discarded. Either way, you never end up with half-written, corrupted data. WAL is the reason databases can promise durability — and understanding it unlocks how replication, LSM-trees, and streaming systems like Kafka all work under the hood.

The Problem WAL Solves#

Without WAL, a crash mid-write leaves data in a corrupted, half-written state — the torn page problem. Consider a simple bank transfer: debit $100 from Account A, credit $100 to Account B. If the system crashes after the debit but before the credit, the money vanishes. The database is now inconsistent, and there's no way to know what operation was in progress.

WAL eliminates this class of failure entirely. The full operation is logged before any changes are applied, so the system always knows exactly where it left off.

How WAL Works#

The write path follows a strict sequence designed to guarantee that the log is always ahead of the data:

1. Client sends a write request — an INSERT, UPDATE, or any mutation.
2. Write is appended to the WAL — this is sequential I/O, which is extremely fast even on spinning disks.
3. WAL entry is fsynced to disk — the fsync call forces the OS to flush the write from its buffer to the physical disk. This is the durability guarantee.
4. Change is applied to the in-memory data structure — the memtable (in LSM-trees) or buffer pool (in B-tree databases).
5. Acknowledgment sent to client — the write is now durable; even a crash at this point is recoverable.
6. Background process flushes in-memory data to disk — this is called a checkpoint (or compaction in LSM-trees).
7. WAL entries before the checkpoint are safe to discard — the data is now in the permanent data files.

Crash Recovery — Replaying the Log#

The core value of WAL is what happens after a crash. On startup, the system reads the log from the last checkpoint and re-applies every entry to reconstruct the state that was in memory when the crash happened.

The replay process works as follows:

1. System starts up after crash.
2. Locate the last checkpoint marker in the WAL — the point where all data was known to be flushed to disk.
3. Read WAL entries sequentially from that checkpoint forward.
4. For each entry, re-apply the operation to the in-memory data structure. This must be idempotent — re-applying the same entry twice must produce the same result.
5. Once all entries are replayed, the system is back to its pre-crash state.
6. Resume normal operation.

Idempotent Replay#

Each WAL entry must be safe to replay multiple times. If the system crashes during recovery itself, it simply replays from the same checkpoint again. This is why WAL entries typically contain the final value, not a delta — SET balance = 150 instead of balance += 50. Replaying SET balance = 150 ten times still leaves the balance at 150. Replaying balance += 50 ten times would not.

Log Sequence Numbers (LSN)#

Every WAL entry gets a monotonically increasing sequence number called an LSN. The checkpoint records the LSN it flushed up to. Recovery starts from that LSN and moves forward. This is how PostgreSQL, MySQL/InnoDB, and Kafka all track position in the log — the LSN is the universal "where am I?" marker.

Redo vs Undo Logs#

Not all logs work the same way. The two main approaches differ in what they record and how recovery works:

• Redo log (most common): records "what the new value should be." On crash, replay forward to restore committed changes. Used by PostgreSQL, RocksDB, and Kafka.
• Undo log: records "what the old value was." On crash, roll back uncommitted changes to restore consistency. InnoDB uses both redo and undo together.

Checkpoint and Log Truncation#

Without cleanup, the WAL grows indefinitely. A checkpoint solves this: flush all in-memory dirty pages to the permanent data files, record the LSN of the last flushed entry, then truncate (or archive) all WAL entries before that point.

The checkpoint frequency is a classic trade-off:

Most production databases default to checkpointing based on WAL size (e.g., PostgreSQL triggers a checkpoint when the WAL reaches max_wal_size, default 1 GB) or time interval, whichever comes first.

WAL in Practice#

Operational Considerations#

Disk I/O. WAL benefits from fast sequential I/O. In production, it's common to place the WAL on a separate disk or SSD from the data files so that WAL appends don't compete with random reads from queries.

fsync trade-offs. Calling fsync on every single write gives maximum durability but adds latency. Batching multiple writes into a single fsync (group commit) is faster but risks losing the last few writes on crash. PostgreSQL exposes this as the synchronous_commit setting — turning it off can give 2–3x write throughput at the cost of potentially losing the last ~10ms of transactions after a crash.

WAL archiving. For point-in-time recovery (PITR), WAL segments are archived to external storage before truncation. Combined with a base backup, this enables restoring the database to any moment in time — not just the last checkpoint but any arbitrary point.

Replication. WAL is the foundation of streaming replication. The primary ships WAL entries to replicas, which replay them to stay in sync. This is how PostgreSQL streaming replication, MySQL binary log replication, and Kafka consumer groups all work — the log is the replication protocol.

When You'll See WAL in Interviews#

WAL rarely appears as a standalone interview question, but understanding it is essential for explaining how databases achieve durability, how replication works, and why LSM-trees are fast for writes. When an interviewer asks "how does the database ensure writes aren't lost?" — the answer starts with WAL.

If you're designing a system that requires crash recovery, exactly-once processing, or write-optimized storage, you should be able to sketch the WAL write path and explain why sequential appends followed by background flushes outperform random in-place updates. That mental model applies to everything from a single PostgreSQL instance to a distributed Kafka cluster.