PostgreSQL In-Depth
Module A-3·28 min read

Every byte you write passes through WAL. Understanding WAL is understanding your write amplification.

Written by Jatin Jain Saraf · Architect of SupraScan

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).

$PGDATA/pg_wal/
├── 000000010000000000000001
├── 000000010000000000000002
├── 000000010000000000000003
└── ...

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.

-- Current write position (where the next WAL record will be written)
SELECT pg_current_wal_lsn();
--  pg_current_wal_lsn
-- --------------------
--  2/4F3A1820
 
-- Current flush position (what has been flushed to disk)
SELECT pg_current_wal_flush_lsn();
 
-- How much WAL has been generated since startup
SELECT pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), '0/0'));

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:

WALRecordHeader:
  xl_tot_len   uint32   — total length of the record
  xl_xid       TransactionId — transaction that generated this record
  xl_prev      XLogRecPtr   — LSN of the previous record (for backward traversal)
  xl_info      uint8        — flags and resource manager subtype
  xl_rmid      RmgrId       — resource manager ID (identifies record type)
  xl_crc       pg_crc32c    — CRC of the record
 
WALRecordData:
  [variable-length payload — format depends on resource manager]

The resource manager (xl_rmid) identifies what kind of operation this record describes. Key resource managers:

rmidNameWhat it logs
0XLOGCheckpoint records, backup labels
10HeapINSERT, UPDATE, DELETE, HOT update
9Heap2VACUUM, FREEZE, visibility map updates
1TransactionCOMMIT, ROLLBACK, PREPARE
2StorageFile creation/deletion
3CLOGTransaction status page updates
6MultiXactMultixact ID and offset updates
11BtreeB-tree splits, page deletions

What an INSERT Actually Writes to WAL

When you insert a row, the WAL record for the heap contains:

  1. The full tuple data (for non-HOT inserts) — enough to reconstruct the row on recovery
  2. The target page and offset — where the tuple was placed in the heap
  3. The transaction ID — for visibility tracking

For an UPDATE, WAL contains:

  1. A record marking the old tuple as dead (with its page/offset)
  2. 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:

  1. A record marking the old tuple's xmax as 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.

-- Measure WAL generated by a single UPDATE
SELECT pg_current_wal_lsn() AS before \gset
UPDATE transactions SET status = 'confirmed' WHERE id = 1;
SELECT pg_size_pretty(
  pg_wal_lsn_diff(pg_current_wal_lsn(), :'before')
) AS wal_generated;
 
-- Typical result for a row update: 200–400 bytes of WAL

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:

  1. 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.

  2. WAL buffer fills — if the circular WAL buffer fills up before a commit, the WAL writer flushes it to avoid stalling.

  3. The WAL writer background process — wakes up every wal_writer_delay milliseconds (default 200ms) and flushes any unflushed WAL. This bounds the exposure window for asynchronous commit.

  4. Checkpoint — all WAL through the checkpoint LSN is guaranteed to be on disk.

The WAL Writer Process

-- Monitor WAL writer activity
-- PG17+: this view was split — checkpointer-related columns (including
-- buffers_checkpoint) moved to the new pg_stat_checkpointer view. On PG17+,
-- query pg_stat_checkpointer for the checkpoint-related columns and
-- pg_stat_bgwriter for the rest, or check both if a column errors as missing.
SELECT
  buffers_checkpoint,
  buffers_clean,
  buffers_backend,
  buffers_backend_fsync,
  buffers_alloc
FROM pg_stat_bgwriter;

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

  1. The checkpointer identifies all dirty pages in shared_buffers.
  2. Dirty pages are flushed to disk — this is the expensive part. The checkpointer spreads this work over time using checkpoint_completion_target to avoid a burst of I/O.
  3. The checkpoint record is written to WAL — recording the checkpoint LSN.
  4. 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

# How often to checkpoint (time-based)
checkpoint_timeout = 5min          # default; increase for write-heavy workloads
 
# How often to checkpoint (WAL-size-based)
max_wal_size = 1GB                 # trigger checkpoint if WAL exceeds this
 
# How to spread checkpoint I/O
checkpoint_completion_target = 0.9 # use 90% of checkpoint_timeout interval for I/O
 
# Minimum WAL to keep (even after checkpoint)
min_wal_size = 80MB

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 & Register
PreviousModule A-2: MVCC: The Architecture That Makes Concurrency Possible (and Expensive)Next Module A-4: Autovacuum: The Process Everyone Misconfigures

Discussion

0

Join the discussion

Loading comments...
PortfolioBlog

© 2026 Jatin Jain Saraf (JJS). All rights reserved.