Every byte you write passes through WAL. Understanding WAL is understanding your write amplification.
Module 3 — Write-Ahead Logging: Durability, Replication, and the Price of Every Write
What this module covers: The WAL mechanism from first principles — why it exists, what is physically written, how checkpoints and background writes interact, how streaming replication flows from WAL, and the production consequences of misconfiguring any of it. By the end, you will be able to calculate write amplification for any workload and diagnose WAL-related performance problems from
pg_stat_*views alone.
Why WAL Exists: The Durability Problem
Consider what happens when you commit a transaction in a naive database implementation.
The data must eventually reach disk. Disk writes are expensive — an 8KB page write on a spinning disk takes 5–10ms. A modern OLTP system might commit thousands of transactions per second. If every commit required flushing all modified heap pages to disk synchronously, throughput would collapse.
But you cannot skip the flush. If the system crashes before modified pages reach disk, those committed transactions are lost. That violates Durability — the D in ACID.
The naive options are both unacceptable:
- Flush heap pages on every commit → terrible write throughput
- Don't flush → committed data lost on crash
WAL is the solution to this dilemma.
Instead of flushing heap pages on commit, Postgres flushes a compact sequential record of what changed. This record — the Write-Ahead Log — is small, sequential, and fast to write. Heap pages can be flushed lazily in the background.
On crash recovery, Postgres replays the WAL to reconstruct any committed changes that hadn't yet made it to heap pages. The WAL is the authoritative record of what happened. The heap is a derived, materialized form of that record.
This is the core invariant of WAL: a transaction's WAL record must be on disk before the commit returns to the client. The heap page can wait. The WAL cannot.
Physical Structure of the WAL
Segments, Pages, and Records
WAL is stored in $PGDATA/pg_wal/ as a series of segment files, each 16MB by default (configurable at initdb time via --wal-segsize).
The filename encodes three components in hexadecimal:
- Timeline ID (
00000001) — identifies the database history branch (changes after PITR recovery) - Segment high bits (
00000000) — upper 32 bits of the segment number - Segment low bits (
00000001) — lower 32 bits of the segment number
Each 16MB segment is divided into 8KB pages (matching the heap page size). Each page has a header. Within pages, WAL is written as a stream of variable-length WAL records.
LSN: The Coordinate System
Every position in the WAL stream is identified by a Log Sequence Number (LSN) — a 64-bit integer representing the byte offset from the beginning of the WAL.
LSNs appear everywhere in Postgres: in tuple headers (t_lsn records the LSN of the last WAL record that modified the page), in replication slots, in recovery targets for PITR, and in monitoring views.
Anatomy of a WAL Record
Every WAL record has:
The resource manager (xl_rmid) identifies what kind of operation this record describes. Key resource managers:
| rmid | Name | What it logs |
|---|---|---|
| 0 | XLOG | Checkpoint records, backup labels |
| 10 | Heap | INSERT, UPDATE, DELETE, HOT update |
| 9 | Heap2 | VACUUM, FREEZE, visibility map updates |
| 1 | Transaction | COMMIT, ROLLBACK, PREPARE |
| 2 | Storage | File creation/deletion |
| 3 | CLOG | Transaction status page updates |
| 6 | MultiXact | Multixact ID and offset updates |
| 11 | Btree | B-tree splits, page deletions |
What an INSERT Actually Writes to WAL
When you insert a row, the WAL record for the heap contains:
- The full tuple data (for non-HOT inserts) — enough to reconstruct the row on recovery
- The target page and offset — where the tuple was placed in the heap
- The transaction ID — for visibility tracking
For an UPDATE, WAL contains:
- A record marking the old tuple as dead (with its page/offset)
- A record with the new tuple data — Postgres compresses this by logging only the changed prefix/suffix bytes relative to the old tuple, regardless of whether the update qualifies as HOT. This WAL-delta compression is a separate optimization from HOT: HOT's actual benefit is skipping index updates entirely and reusing space on the same page when no indexed column changed — it doesn't change what gets written to WAL for the row itself.
For a DELETE, WAL contains:
- A record marking the old tuple's
xmaxas set
This is why updates are expensive in Postgres — they generate more WAL than inserts. Every update is a delete + insert at the WAL level.
The Write Path: From Memory to Disk
Understanding exactly when data moves from memory to disk is critical for reasoning about both performance and durability.
Shared Buffers and the WAL Buffer
Postgres has two separate in-memory write buffers:
shared_buffers — the main page cache. When you modify a page (insert, update, delete), the modified page lives here until a background writer or checkpoint flushes it to the heap file on disk. A dirty page in shared_buffers is a performance optimization — it defers expensive random writes.
WAL buffers (wal_buffers, default 16MB or 1/32 of shared_buffers) — a circular buffer in shared memory where WAL records are accumulated before being written to the WAL segment files. WAL records flow from the WAL buffer to disk much more frequently than heap pages.
When WAL Is Flushed
WAL is flushed to disk (fsync'd) at these moments:
-
Transaction commit — by default (
synchronous_commit = on), WAL is flushed before the commit acknowledgement is sent to the client. This is what makes committed transactions durable. -
WAL buffer fills — if the circular WAL buffer fills up before a commit, the WAL writer flushes it to avoid stalling.
-
The WAL writer background process — wakes up every
wal_writer_delaymilliseconds (default 200ms) and flushes any unflushed WAL. This bounds the exposure window for asynchronous commit. -
Checkpoint — all WAL through the checkpoint LSN is guaranteed to be on disk.
The WAL Writer Process
buffers_backend_fsync being non-zero means backends are having to do their own fsyncs because the WAL writer is falling behind. This is a performance warning sign.
Checkpoints: Bounding Recovery Time
WAL solves durability, but it creates a new problem: if the database crashes and needs to replay WAL for recovery, how far back does it need to go? In theory, the entire WAL history since the database was created.
Checkpoints solve this by periodically guaranteeing that all dirty heap pages have been flushed to disk. After a checkpoint completes, crash recovery only needs to replay WAL from the checkpoint LSN forward.
What Happens During a Checkpoint
- The checkpointer identifies all dirty pages in
shared_buffers. - Dirty pages are flushed to disk — this is the expensive part. The checkpointer spreads this work over time using
checkpoint_completion_targetto avoid a burst of I/O. - The checkpoint record is written to WAL — recording the checkpoint LSN.
- The WAL is flushed — ensuring the checkpoint record is durable.
After a checkpoint:
- All heap pages that were dirty before the checkpoint start are now on disk
- Recovery can safely start from the checkpoint LSN instead of the beginning of WAL
Checkpoint Configuration
The checkpoint_completion_target trade-off:
Setting this to 0.9 means the checkpointer spreads its I/O across 90% of the checkpoint_timeout interval. At checkpoint_timeout = 5min, that's 270 seconds of smooth I/O. This reduces I/O spikes but means at any point, up to 5 minutes of WAL must be replayed on crash.
Setting it to 0.1 means the checkpointer writes aggressively at the start — high I/O spike, but recovery is faster.
For most production systems: checkpoint_completion_target = 0.9 and checkpoint_timeout = 15min with max_wal_size = 4GB is a reasonable starting point.
Sign in to keep reading
The rest of this module is free — sign in with Google to unlock it and track your progress.
Sign in & RegisterDiscussion
0Join the discussion