What is WAL
A write-ahead log (WAL) is an append-only file where the database records every change before applying it to the actual data files. The rule is simple: the log entry must be on disk before the corresponding data change is considered committed. If the database crashes, it replays the log to restore committed transactions and discard uncommitted ones.
How it works
When a transaction writes data, the database:
- Appends the change to the WAL (what changed, in which page, what the old and new values are).
- Flushes the WAL to disk with fsync on commit. This is the durability guarantee — the change survives power failure.
- Applies the change to the in-memory page in the buffer pool. The modified page is now dirty.
- Flushes dirty pages to data files in the background, at its own pace.
Because the WAL is append-only, writes are sequential — the fastest pattern for both SSDs and spinning disks. Data file writes, by contrast, are random (updating pages scattered across the file). The WAL converts expensive random writes into cheap sequential writes.
On crash recovery, the database reads the WAL from the last checkpoint — a point where all dirty pages were flushed to disk. It replays committed transactions (redo) and rolls back uncommitted ones (undo). Recovery time depends on the interval between checkpoints.
PostgreSQL, SQLite, MySQL (InnoDB), and virtually every other serious database uses write-ahead logging. SQLite's WAL mode is a common configuration choice that improves concurrent read performance by allowing readers to access the database while a writer appends to the WAL.
Why it matters
The WAL is what makes the D in ACID possible. Without it, committed data could be lost on any crash. It also enables atomicity — the WAL knows which transactions committed and which did not, so it can cleanly undo partial work.
See How WAL Works for the full walkthrough of checkpoints, recovery, and WAL configuration.