Write-Ahead Logs

A transaction updates three rows on three different pages. The machine loses power after the second.

The database is now inconsistent, and worse, it has no way to know it. Half a transaction is on disk and nothing records that the other half was supposed to exist.

The rule

Write what you are about to do, before you do it.

The log is appended to and flushed first. Only then are the data pages modified, and those can happen later, in any order, in the background.

Recovery

On restart, the database reads the log from the last checkpoint:

• Redo every change belonging to a transaction with a COMMIT record.
• Undo every change belonging to one without.

That is why the crash in the demo is survivable: the commit record was durable, so the change is re-applied. And it is why a transaction that was mid-flight is rolled back cleanly rather than left half-applied.

What else falls out of it

This is the part worth internalising — one mechanism, four uses.

Replication. The log is already an ordered record of every change. Ship it to another machine and apply it, and you have a replica. Postgres streaming replication is literally this.

Point-in-time recovery. Restore last night’s backup, replay the log to 11:47, and you have the database as it was one minute before someone dropped the table.

Change data capture. Read the log to feed a search index, a cache, or a data warehouse — without polling the tables or adding triggers. Debezium is a WAL reader.

Event sourcing. Treat the log as the source of truth rather than as a recovery mechanism, and the tables become a derived view.

The durability knob

fsync is the expensive part — a millisecond or more, because it must actually reach the physical medium.

synchronous_commit = on       -- fsync before acknowledging. Nothing is lost.
synchronous_commit = off      -- acknowledge first, fsync within ~200 ms.

Turning it off makes commits several times faster and means a power loss can lose the last fraction of a second of acknowledged transactions.

That is sometimes the right call — for a table of analytics events, losing 200 ms is fine. It is never the right call for the table it defaults to being applied to, so set it per-transaction rather than globally.

Not just databases

Filesystems. ext4’s journal, NTFS’s log — the same idea, which is why a modern filesystem does not need fsck after an unclean shutdown.

Kafka is a write-ahead log offered as a product.

Raft replicates a log and applies it to a state machine; see consensus.

LSM-trees write to a WAL plus an in-memory table, then flush sorted files. The WAL exists purely so the in-memory part survives a crash.