Write-Ahead Logging

By Follow a Write Through the Engine the picture is: COMMIT has returned, heap page 2093 in the buffer pool says 400, page 2093 on storage says 500, and the frame will be written in a few minutes. Power fails now. RAM is gone; storage says 500; the client has a receipt for a transaction that says 400. Durability — the D in ACID — is the promise that the client's receipt is correct. The engine must therefore have made *something* durable before returning OK, and on restart it must be able to find it and act on it.

The constraints make this hard. Writing an 8 KB page is a random write to wherever that page lives — for a transfer touching two heap pages and two index leaves, four writes to four places. Random writes are the slow operation on every storage medium; a disk does perhaps 200 per second, an SSD tens of thousands, and each is 8 KB for a change of 16 bytes. And a page write is not atomic: 8 KB spans several physical sectors, and a crash mid-write leaves a page half old, half new — a torn page — that is worse than either version.

The obvious design: COMMIT writes every page the transaction dirtied, fsyncs them, and only then returns OK. Storage is always the committed state; a crash loses only uncommitted work; restart needs no special handling. Three things break. Write amplification: 16 bytes changed, 32 KB written, four seeks; a workload of 5,000 small commits per second is 20,000 random page writes per second, which exceeds what most storage can sustain. No batching: a hot page updated by a thousand transactions in a second is written a thousand times instead of once. Torn pages: a crash during one of the four writes leaves a page that is neither version, and there is no other copy to repair it from.

There is a fourth, subtler problem. A page can hold rows from several transactions. Flushing it for T1's commit also writes T2's uncommitted change on the same page; if T2 then aborts, storage holds a change that never committed and nothing records how to undo it. "Flush at commit" is not merely slow; without a log it is not even correct.

Instead of writing the page, write a description of the change: "transaction 7412, page 2093, slot 7, balance 500 → 400". Eighty bytes. Append it to a file that is only ever appended to — sequential writes are the fast operation on every medium — and make *that* durable at COMMIT with one fsync. The page stays dirty in the pool and is written whenever the background writer gets to it; if the machine dies first, the record is enough to reconstruct the change: read the page from storage (500), apply the record (400), done. The log is the durable truth; the data files are a cache of it that lags behind.

Every change goes through the same door. UPDATE, INSERT, DELETE, index inserts, page splits, even the allocation of a new page: each is a record. A transaction's records are chained by a back-pointer, and its last record is COMMIT (or ABORT). The sequence for the transfer is: BEGIN → UPDATE page 7 → UPDATE page 3 → COMMIT, then fsync, then OK to the client, then — later — the two pages.

The LSN (log sequence number) is a record's byte position in the log stream. It is monotonic, so it is also a clock: "everything before LSN 169" is well defined. Three LSNs matter. The durable LSN (flushed LSN): the log has been fsynced through here. The page LSN, stamped in each page header: the last record applied to this page. And the checkpoint LSN: recovery starts here. The write-ahead rule ties the first two together: a page may be written to storage only if durable LSN ≥ page LSN. The background writer checks it before every page write and, if necessary, flushes the log first. The rule guarantees that anything on storage is described by durable log — which is exactly what redo needs, and it is why a page image on disk never runs ahead of the log.

COMMIT's cost is the fsync, and fsync does not get cheaper with smaller writes: it is a round trip to the device's durable medium, 50–200 µs on a datacenter NVMe with power-loss-protected cache, 1–10 ms on consumer SSDs and spinning disks. Group commit amortises it: while one fsync is in flight, other transactions reaching COMMIT append their records and wait; the next fsync covers all of them. A hundred concurrent committers pay one fsync between them, so throughput scales with concurrency while each commit still waits roughly one fsync of latency. Relaxing the wait (synchronous_commit = off) returns before the fsync and accepts losing the last few hundred milliseconds of commits on a crash — a legitimate choice for data that can be regenerated, and a silent data-loss bug otherwise.

The log grows with every change and recovery replays it from the beginning — unbounded log, unbounded restart time. A checkpoint bounds both. The checkpointer notes the current LSN, writes every page dirtied before it to storage (honouring the write-ahead rule), then writes a CHECKPOINT record and fsyncs. From that moment every change before the checkpoint LSN is present in the data files, so recovery can start at the checkpoint and the log before it can be recycled. Real engines spread the page writes over the interval between checkpoints (checkpoint_completion_target) rather than writing them in a burst — a burst of thousands of random page writes is the "checkpoint storm" that makes latency spike every five minutes on a badly tuned server.

A sharp checkpoint stops the world until all pages are written; a fuzzy checkpoint (what every engine actually does) lets transactions continue and records, in the checkpoint, the list of active transactions and the earliest LSN of any still-dirty page, so recovery knows how far back to start. The trade-off is the interval: frequent checkpoints mean a short log and fast recovery but more page writes (each hot page is written once per checkpoint, and each first-touch after a checkpoint costs a full-page image); rare checkpoints mean fewer writes and a long restart.

PostgreSQL's WAL lives in pg_wal/ as 16 MB segment files named by timeline and LSN; the LSN is printed as two hex words (0/16B3D48). Records are written by backends into a shared wal_buffers area, flushed by whoever commits or by the WAL writer process. synchronous_commit selects what COMMIT waits for: on (fsynced locally, the default), remote_apply/remote_write/on with synchronous replicas, or off (return immediately; up to ~3× wal_writer_delay of commits can be lost on a crash, never corrupting the database). commit_delay and commit_siblings tune group commit explicitly.

Full-page writes (full_page_writes = on) solve torn pages: the first record that modifies a page after a checkpoint carries the entire 8 KB page image, so recovery can overwrite a possibly-torn page with a known-good copy before replaying later records. The cost is that WAL volume spikes right after each checkpoint — one reason not to checkpoint too often — and the reason wal_compression exists. pg_current_wal_lsn(), pg_stat_wal and pg_stat_bgwriter show how much WAL is generated and how checkpoints are behaving; checkpoint_timeout, max_wal_size and checkpoint_completion_target govern when and how smoothly they run.

InnoDB's redo log is a fixed-size ring (innodb_redo_log_capacity, formerly innodb_log_file_size × innodb_log_files_in_group). The LSN is a byte count since the beginning of time; the ring position is LSN modulo capacity. Because the ring wraps, the checkpointer must have flushed pages up to the LSN about to be overwritten — if it cannot keep up, writers stall until it does, which is the classic "the redo log is too small" symptom under write bursts. innodb_flush_log_at_trx_commit chooses durability: 1 (fsync at every commit, the default and the only fully durable setting), 2 (write to the OS at commit, fsync once per second — survives a MySQL crash but not a power loss), 0 (write and fsync once per second).

Torn pages are handled by the doublewrite buffer: before a data page is written to its place in the tablespace, it is written to a contiguous doublewrite area and fsynced; then it is written to its real location. A crash during the second write is repaired from the doublewrite copy on restart. It doubles page-write bytes but keeps them sequential, and it keeps redo records small — InnoDB never has to log full page images. Redo records themselves are physiological (page number + a logical operation within the page), and the undo log is a separate structure in the system or undo tablespaces, itself protected by redo.