Write-ahead logging (WAL)

The rule that makes a crash survivable, stated by the PostgreSQL documentation in one sentence:

"WAL's central concept is that changes to data files (where tables and indexes reside) must be written only after those changes have been logged, that is, after WAL records describing the changes have been flushed to permanent storage."

Write the intention before the act. Then a crash can only ever catch the system in a state the log can explain:

"If we follow this procedure, we do not need to flush data pages to disk on every transaction commit, because we know that in the event of a crash we will be able to recover the database using the log: any changes that have not been applied to the data pages can be redone from the WAL records. (This is roll-forward recovery, also known as REDO.)"

Together with MVCC, this is the second half of how a single-node OLTP engine keeps its promises: MVCC is the answer to concurrency, WAL is the answer to durability. The spoke had the first written up from two engines' documentation and the second missing entirely.

The bargain is sequential writes against random ones

The clause that gets skipped is the one that explains why WAL is a performance design and not only a safety one:

"Using WAL results in a significantly reduced number of disk writes, because only the WAL file needs to be flushed to disk to guarantee that a transaction is committed, rather than every data file changed by the transaction. The WAL file is written sequentially, and so the cost of syncing the WAL is much less than the cost of flushing the data pages."

A transaction touching six pages scattered across three tables and their indexes would otherwise owe six random flushes at commit. Instead it owes one append to one file. And the append scales in a way the flushes never could:

"when the server is processing many small concurrent transactions, one fsync of the WAL file may suffice to commit many transactions."

That sentence is the reason OLTP throughput is what it is. The workload this whole spoke is defined around — many small concurrent writes — is precisely the workload where the durability cost gets amortised across everyone committing in the same instant. Durability stops being paid per transaction and starts being paid per disk sync.

Everything downstream is a WAL reader

The log exists for crash recovery, and then turns out to be the most useful artifact in the system, because it is the database's own serialized record of what happened in what order. The Postgres docs name the first consequence:

"WAL also makes it possible to support on-line backup and point-in-time recovery… By archiving the WAL data we can support reverting to any time instant covered by the available WAL data: we simply install a prior physical backup of the database, and replay the WAL just as far as the desired time."

From there the same stream feeds nearly every operational capability this spoke pages:

• Point-in-time recovery — a physical base backup plus every segment since. pgbackrest is built around shipping those segments faster than the primary produces them.
• Replication — streaming the log to a standby that replays it, which is what patroni arranges and supervises. Its maximum_lag_on_failover is measured in bytes of WAL, because "how far behind is that replica" has no other honest unit.
• The bottleneck at scale — planetscale-768-servers lists WAL alongside replicas and backups among the things that stop scaling before query load does. One primary means one log, and the log is serial by construction.

(My reading rather than any source's claim: the shape here is the same one event-sourcing proposes as an application architecture — an append-only log as the truth, current state as a replay of it. Every relational OLTP engine already works this way internally. The argument rethinking-data-client-event-sourcing and local-first-architecture are making is not that the log should exist, but that it should be the public interface rather than a private implementation detail. The two camps this spoke records as being in tension are running the same data structure at different altitudes.)

Not the same in every engine

mysql's InnoDB reaches the same guarantee through a redo log plus an undo log, with the undo log doing double duty for MVCC — where Postgres keeps old row versions in the heap and vacuums them. Same principle, different accounting, and the divergence is already recorded on multiversion-concurrency-control.

Open, and named as such

The engine documentation quoted here is the introduction, not the internals. Undefined in this corpus: checkpoints (how far back recovery must replay, and what forces a data-page flush after all), full-page writes and torn-page protection, synchronous_commit and the durability levels between "committed" and "on disk somewhere else", and WAL write amplification. Closed 2026-08-30 — every item on that list is now written from the engine's own documentation: checkpoint as a mechanism page, postgres-wal-configuration for the dials and postgres-wal-reliability for the torn-page failure the full images defend against.

The finding worth carrying up from it: write amplification and recovery time are the same dial pulled in opposite directions. Checkpointing more often shortens replay and simultaneously enlarges the log, because each page pays a full 8 kB image again on the far side of every checkpoint. This page described WAL as trading sequential writes for random ones; the fuller picture is that the trade is paid back partly in log volume, and the size of the repayment is set by how often you checkpoint.

The comparison that growth edge 6 actually wants is still missing — the B-tree-plus-WAL design described here against the LSM tree used by the analytical siblings (clickhouse, duckdb) and by embedded key-value stores, where the log and the storage structure are the same object.

Related

postgresql · mysql · multiversion-concurrency-control · checkpoint · postgres-wal-configuration · postgres-wal-reliability · pgbackrest · patroni · planetscale-768-servers · event-sourcing · online-transaction-processing · operational-databases