Ensuring Durability: The Write-Ahead Log (WAL)

The Danger of Buffering

We just established that writes go to the buffer pool (RAM) first, and are flushed to disk later by the checkpointer. But RAM is volatile. If the power cuts out before the checkpointer runs, all those dirty pages are gone. This violates the Durability guarantee of ACID.

How can we have the speed of writing to RAM but the safety of writing to disk?

The Golden Rule: Log First, Data Later

The answer is the Write-Ahead Log (WAL) — called the redo log in MySQL/InnoDB and the redo log in Oracle.

The rule is: before modifying a data page in the buffer pool, the database must first write a description of that change to the WAL on disk.

The WAL is a sequential, append-only file. Writing sequentially to the end of a file is extremely fast — even on spinning disks, sequential writes are orders of magnitude faster than random writes. This is why WAL writes are fast enough to not be a bottleneck.

The Commit Protocol

Here is the exact sequence for a committed transaction:

1. Transaction begins.
2. Data page is loaded into the buffer pool (if not already there).
3. The change is made to the page in RAM (dirty page).
4. A WAL record describing the change is written to the WAL buffer (in RAM).
5. At commit time, the WAL buffer is flushed to disk with fsync(). This is the critical moment — the data is now on persistent storage.
6. The database returns "COMMIT" success to the client.
7. Eventually, the checkpointer flushes the dirty data page to disk. This can happen seconds or minutes later.

The client receives success at step 6. Even if the server crashes immediately after, the WAL on disk (step 5) contains everything needed to reconstruct the change.

Crash Recovery

On restart after a crash, the database performs REDO recovery:
1. Find the last checkpoint (the last point where all dirty pages were flushed to disk).
2. Read the WAL forward from that checkpoint.
3. Replay every WAL record to bring the data files up to date.
4. The database is now consistent and can accept connections.

This is why PostgreSQL startup after a crash says "database system was not properly shut down; automatic recovery in progress."

WAL Beyond Durability: Replication

The WAL serves a second critical purpose: streaming replication. Standby servers receive the WAL stream from the primary and replay it continuously, keeping themselves in sync. This is how PostgreSQL streaming replication works — the standby is essentially doing crash recovery in real time, forever.