When a database acknowledges a commit, it makes a promise: even if the power cable is pulled out of the server a millisecond later, that transaction will still be there after reboot. Delivering on that promise has nothing to do with how quickly the actual data files get updated. It rests on a single discipline, followed by virtually every storage engine built in the last forty years: write the log entry to stable storage before you touch the data the log describes. That discipline is write-ahead logging, and understanding it explains a surprising number of operational behaviors — why commits have a floor on latency, why checkpoints matter, and why a “torn page” can corrupt a database.
This post walks through how write-ahead logging works, using PostgreSQL as the concrete example. The same ideas show up in InnoDB’s redo log, SQLite’s WAL mode, and most event-sourced designs, so the concepts transfer even where the names differ.
The Rule: Log First, Then Change
The core invariant is simple to state: before the system modifies any page in a data file, the description of that modification must already be on durable storage. If the system crashes mid-write, recovery can replay the log to bring the data files back to a consistent state. The log, not the data files, is the source of truth at any instant.
This ordering matters because of a hardware reality: flushing a small sequential log record is orders of magnitude cheaper than randomly rewriting the 8 kB pages a transaction touches. A single UPDATE might modify rows scattered across dozens of pages. Writing every one of those pages synchronously before acknowledging the commit would make writes pay random-I/O prices. The WAL turns all of that into one sequential append, and the modified heap pages get written back later, lazily, in the background.
Records are appended to the log and identified by a log sequence number (LSN) — a byte offset into the WAL stream, usually displayed as two hex numbers like 0/16B3748. Every data page in PostgreSQL tracks the LSN of the most recent WAL record that affected it, which is how recovery later knows whether a page on disk is older or newer than a given point in the log.
What a Commit Actually Waits For
Consider a plain transaction:
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 42;
UPDATE accounts SET balance = balance + 100 WHERE id = 7;
COMMIT;At COMMIT time, PostgreSQL writes one commit record to its in-memory WAL buffers, then flushes them to disk. The client gets its “success” only after that flush completes. The heap pages holding the new balances may still be sitting untouched in shared_buffers — they will reach disk minutes later, during a checkpoint or a background write. Durability and visibility are decoupled from the physical layout of the data files entirely.
The synchronous_commit setting controls how long that wait is. The default, on, waits for the local WAL flush. Setting it to off returns success after the record is merely handed to the operating system, without waiting for the flush — a big throughput win for write-heavy workloads, with a specific and bounded cost: a crash can lose the most recent few hundred milliseconds of committed transactions, but it can never corrupt the database or leave it inconsistent. Transactions are either fully present after recovery or fully absent. For analytics ingestion or cache-like tables, that trade is often worth making per-role rather than globally:
ALTER ROLE ingest SET synchronous_commit = off;Commit latency is dominated by the flush, so high-concurrency workloads benefit from group commit: many transactions waiting to commit share a single WAL flush. PostgreSQL coalesces commits naturally under load, and the commit_delay parameter can add a deliberate micro-wait to widen the group — though at typical latencies on modern SSDs, the default of 0 is usually right.
Torn Pages and Full-Page Images
Here is the subtlety that catches people: the WAL protects the log itself with fsync, but data pages are written to disk without any such guarantee, in 4 or 8 kB units that the OS and storage may split arbitrarily. If the server (not just the database process — the machine) crashes while a data page is mid-write, the on-disk page can end up as a half-old, half-new hybrid. A torn page cannot be fixed by replaying the change record, because the record only describes a row-level delta against a page the disk no longer contains.
The fix is what full_page_writes (on by default) does: the first time a page is modified after each checkpoint, the entire page image is written into the WAL alongside the change record. Recovery then works in two tiers — restore the whole page from the full-page image if one exists, otherwise apply the delta. Either way, replay always starts from a known-good page state.
The cost is WAL volume. Full-page images are large, and a workload that churns every page of a buffer cache right after a checkpoint produces a WAL spike. The wal_compression setting compresses those images (methods include pglz, lz4, and zstd depending on build flags; it is off by default). If your WAL generation rate is dominated by full-page images — visible as large flushes right after checkpoints — enabling it with a fast method is a cheap win.
Checkpoints: Truncating the Replay Work
Recovery replays WAL from some starting point forward. If that starting point were the beginning of time, a database that had been up for weeks would take weeks to recover. Checkpoints bound this: at a checkpoint, the server forces all dirty pages out to disk and records an LSN in the WAL. Everything before that point is now redundant for crash recovery, and everything after it must be replayed.
That defines the fundamental checkpoint trade-off. Checkpoint more often and crash recovery is fast, but you pay repeated write bursts and more full-page images. Checkpoint less often and steady-state I/O is smoother, but a crash means replaying more WAL. PostgreSQL exposes this through checkpoint_timeout (default five minutes) and max_wal_size (default 1 GB, a soft limit that triggers an earlier checkpoint when WAL grows past it). Tuning is mostly about matching recovery-time expectations: if your runbook promises a two-minute crash recovery, a checkpoint interval far beyond that is a fiction.
WAL files themselves are recycled, not deleted: once past the last needed checkpoint, a 16 MB segment is renamed and reused for future records, keeping disk usage stable near min_wal_size (80 MB by default) in quiet periods.
Watching the WAL Yourself
The WAL is not opaque. Every record it contains can be decoded with the shipped pg_waldump utility, which renders records with their LSNs, transaction IDs, and resource descriptions:
pg_waldump -p pg_wal -s 0/1500000 -e 0/1600000Two practical uses come up constantly. First, when WAL disk usage races upward, decoding recent segments tells you which database and relation generated the volume — the usual suspects are large batch updates, freshly created indexes, or aggressive autovacuum. Second, replication is built directly on the WAL: the default wal_level = replica streams the same records that protect against crashes to standby servers and feeds WAL archiving for point-in-time recovery. A replication client that stalls does not just fall behind — it forces the primary to retain every WAL segment the client still needs, and unbounded retention on a busy primary is one of the classic ways a database fills its disk. Monitoring replication lag and WAL retention is not optional once a standby exists.
The Same Idea, Different Engines
Once you see the pattern, it is everywhere. SQLite’s WAL mode appends committed transactions to a side file, letting readers proceed against the original database file without blocking — the mechanism behind SQLite’s much-improved write concurrency since version 3.7.0. InnoDB’s redo log works like PostgreSQL’s WAL down to the torn-page logic: a log-before-data rule, full-page-style logging after checkpoints, and a fuzzy checkpointing scheme that smooths flushes. LSM-tree engines such as RocksDB also keep a write-ahead log per memtable, since newly written data sits in memory and would be lost without one.
The vocabulary differs but the contract is identical: an ordered, durable record of changes, written sequentially, from which the real state can always be reconstructed. Event-sourced application architectures make the same bet deliberately — the event log is the database, and projections are just replay.
Practical Takeaways
- Commit latency is a durability cost, not a compute cost — it is the WAL flush. On
offfor roles that can tolerate bounded loss. - Spiky WAL right after checkpoints means full-page images; try
wal_compression = lz4if your build supports it. - Recovery time is a function of checkpoint interval and WAL volume since the last checkpoint — tune
checkpoint_timeoutandmax_wal_sizeagainst your recovery objectives, not folklore. - Watch WAL retention when standbys or archives exist. A stalled replication client is a disk-full incident in slow motion.
- When debugging mysterious write amplification, decode the WAL with
pg_waldumpinstead of guessing.
Write-ahead logging is one of those ideas that looks like an implementation detail and turns out to be the architecture. The next time a commit takes longer than expected or a standby falls behind, the question to ask is not “what is the database doing?” — it is “where in the WAL pipeline is this stuck?” The answer is usually right there in the log.