The Write-Ahead Log

Before Postgres modifies a page in shared_buffers, it writes a record describing that modification into the write-ahead log, and before a transaction is allowed to report success it makes sure those records are on disk. Log first, then data. The pages themselves can sit dirty in memory for another ten minutes and the transaction is still safe, because the log already knows what has to happen to them.

That single ordering rule is why a crash costs you a replay rather than a database, and it is why this chapter exists at all. Cartwheel's 14:12 accident, a migration whose WHERE matched 90,000 more orders rows than anyone intended, is recoverable in principle, because every one of those deletions was described in a file on disk before it touched a data page. It is not recoverable in practice, because those files are recycled within the hour and Cartwheel keeps no copy of them. The rest of the chapter fixes that. This topic is what is in them.

Log First, Then Data

A transaction that updates one row of inventory and its two indexes dirties three pages scattered across a 900 MB relation. Flushing those three pages at commit would be three random writes to three different places on the volume, and a transaction touching forty pages would be forty. The write-ahead rule replaces all of it with one append to a file that is already open and already positioned: a few hundred bytes describing what changed, flushed once. The dirty pages are written later, in bulk, by the checkpointer and the background writer, at a moment nobody is waiting on.

Recovery is the other half of the bargain. Postgres records the position of the last completed checkpoint in pg_control; on startup after a crash it reads that file, reads the checkpoint record it points at, and replays the log forward from there. Changes that were committed get reapplied to whatever state the data files were left in, and changes from transactions that never committed are simply not in the picture. There is no repair tool to run and no consistency check to wait for; startup reads the log, replays it, and opens for connections.

LSNs and Segments

Every record in the log has a log sequence number — an LSN, a byte offset into the stream that increases monotonically and never goes backwards. Postgres exposes it as the pg_lsn type and prints it as two hexadecimal halves separated by a slash. It is the unit that every other operational number in the back half of this book is quoted in: how far behind a replica is, where a recovery should stop, how much log a replication slot is holding back.

SELECT pg_current_wal_lsn();      -- 3A/BC0001F8

UPDATE inventory SET on_hand = on_hand - 1
 WHERE product_id = 4471 AND warehouse_id = 2;

SELECT pg_current_wal_lsn() AS now_at,
       pg_wal_lsn_diff(pg_current_wal_lsn(), '3A/BC0001F8') AS bytes,
       pg_walfile_name(pg_current_wal_lsn()) AS segment;

  now_at    | bytes |         segment
------------+-------+--------------------------
 3A/BC000350|   344 | 000000010000003A000000BC

Subtracting one position from another gives the exact WAL cost of a statement in bytes, which is the honest way to answer "how much log does this migration generate" before running it against 40 million rows. The third column names the file that position lives in. The log is stored as 16 MB segment files under pg_wal, named with ever-increasing hexadecimal numbers whose first eight characters are the timeline id — a number that stays at one until a recovery branches the history, which is a detail that becomes load-bearing later in this chapter. The 16 MB is fixed at initdb time by --wal-segsize; it is not a knob you can reach for on a cluster that is already running.

wal_level

How much goes into the stream is decided by one setting with three values. minimal logs only what crash recovery needs, and it deliberately omits row-level records for transactions that create or rewrite a relation, which is cheap and rules out both archiving and standbys. replica is the default: enough information to archive the WAL, to feed a physical standby, and to let that standby answer read-only queries. logical adds what logical decoding needs to reconstruct row-level changes, at a further cost in volume that is worst on update-heavy tables and worse again under REPLICA IDENTITY FULL.

Changing it requires a restart, and archive_mode cannot even be turned on while the level is minimal. That makes it an install-time decision rather than an incident-time one, and the failure mode is specific: the cluster you set to minimal for write throughput is the cluster that cannot start archiving on the afternoon somebody deletes 90,000 rows. Cartwheel runs replica. Starting at logical instead costs volume from day one; arriving at it later costs a restart, scheduled against whatever change freeze is in force that month.

Full-Page Writes

The first modification of any data page after each checkpoint writes the entire 8 KB page into the log, not just the delta. The reason is torn writes. An 8 KB page reaches a device whose atomic unit is 512 or 4,096 bytes, so a power failure in the middle of writing it can leave a page that is part old and part new. A delta record applied to a page in that condition produces plausible-looking garbage rather than a correct row, so Postgres refuses to trust the page at all: the first log record after a checkpoint carries a whole image, replay overwrites the page wholesale, and every delta after that is applied to something of known provenance.

This is why WAL volume is not flat. It spikes immediately after each checkpoint, when every hot page is being imaged for the first time, and decays until the next one. pg_stat_wal counts the images in wal_fpi against total records in wal_records, and wal_compression, off by default with pglz, lz4 and zstd available depending on how the server was built, compresses those images specifically rather than the whole stream. Key order matters here too: a random key lands on a different index leaf almost every insert, so almost every insert triggers a fresh image, while a sequential key keeps returning to a page that has already been imaged this interval. That is the concrete argument behind the v7 UUID on orders.public_id.

SELECT wal_records, wal_fpi, pg_size_pretty(wal_bytes::bigint) AS total,
       round(100.0 * wal_fpi / wal_records, 1) AS pct_fpi
  FROM pg_stat_wal;

 wal_records |  wal_fpi  | total  | pct_fpi
-------------+-----------+--------+---------
   812447301 | 138115882 | 4110 GB|    17.0

Seventeen full-page images per hundred records is unremarkable for a write-heavy OLTP cluster. The number worth acting on is not the level but the direction it moves when checkpoint spacing changes, which is the next topic's whole subject. Pull the checkpoints closer together and this percentage climbs, because more checkpoints means more first-touches per unit of time.

Everything Else That Reads This Stream

The same records are read by three consumers for three different purposes. An archive copies each completed segment somewhere durable, so a base backup can be rolled forward to any moment inside the retention window, which is the machinery of topic 65. A physical standby receives the records over a network connection and applies them continuously to stay current, which is Chapter 13's subject and not this one's. Logical decoding reads the stream and reconstructs row-level changes for a subscriber or a change-data-capture pipeline, which is the entire reason wal_level = logical exists.

One line follows from that, and it is the line that costs people their data. A replica is not a backup. It consumes exactly the same records as everything else, so the 90,000-row delete at 14:12 was on pg-replica-a within a second of landing on pg-primary, faithfully and with no way to decline it. An archive is the only one of the three consumers that keeps the state from before a record was applied.

Watching pg_wal

Postgres recycles segment files once they are no longer needed, and free space in pg_wal is stable as long as that keeps happening. Two things stop it, and neither writes a line anywhere. An archive_command that keeps failing leaves every segment marked as still needed, because the server will not discard a file it has not been told was archived. A replication slot whose consumer has gone away does the same thing for the same reason. Neither raises an error on the write path; the disk simply stops going down, and then the filesystem fills and the cluster stops accepting writes.

The catching mechanisms are cheap and specific: pg_stat_archiver reports failed_count and last_failed_time, pg_replication_slots shows what each slot is holding, and free space on the volume is the number that ties both together. What you must not do is the instinct, which is deleting the oldest files in pg_wal to buy room. Those are precisely the segments a restart needs, and the supported way to remove archived ones is pg_archivecleanup pointed at the archive rather than at the live directory. Chapter 5 already argued the durable answer, which is a separate volume for pg_wal with its own free-space alert.