PostgreSQL WAL, in depth: segments and LSNs, synchronous_commit, full-page writes, checkpoint sizing, archiving and a full pg_wal

The path of one record

When a backend updates a row, it changes the page in shared buffers and inserts a WAL record describing the change into the WAL buffers. The record's log sequence number is stamped on the page, and the page may not be written to disk until the WAL up to that LSN is flushed: the write-ahead rule, enforced per page.

At commit, the backend writes a commit record and, by default, waits until the WAL up to it is flushed. Backends committing at the same moment share that flush (group commit). The WAL writer also flushes in the background every wal_writer_delay (200 ms), which matters mostly for asynchronous commits.

The data page stays dirty in memory until the checkpointer writes it, spreading writes over checkpoint_completion_target (0.9) of the interval. Completed segments are copied by the archiver and streamed by WAL senders to standbys and logical consumers.

LSNs, segments and file names

An LSN is a 64-bit byte position in an ever-growing log, printed as two hexadecimal halves such as 2/3A000148. The log is stored as segment files in pg_wal, 16 MB each by default (the size is fixed at initdb time with --wal-segsize). A file name is 24 hex digits: the timeline, the high 32 bits of the LSN, and the low 32 bits divided by the segment size.

So on timeline 1, LSN 2/3A000148 lives in 00000001000000020000003A: timeline 00000001, high half 00000002, and 0x3A000148 divided by 16 MB is 0x3A, at byte offset 328 inside the file. pg_walfile_name() does this for you, but knowing the shape helps you read an archive listing, and explains why names jump after a standby is promoted: promotion starts a new timeline, writes a small .history file, and new segments carry the new timeline number.

While total WAL stays below min_wal_size (80 MB), old segments are recycled by renaming rather than deleted (wal_recycle).

# Which resource managers and record types produce our WAL?  (run on a copy, or on the primary's pg_wal)
pg_waldump --path="$PGDATA/pg_wal" --start=2/3A000000 --end=2/3C000000 --stats=record
# Output: one row per record type with count, record bytes and FPI (full-page image) bytes.

pg_waldump decodes segment files into records. Its --stats=record summary is the fastest answer to "why is our WAL so big": it splits volume by record type and separates record bytes from full-page images, which are usually the answer.

wal_level: how much the log has to say

The log must describe changes well enough for its consumers. wal_level has three values, each including everything below it, and changing it requires a restart.

Pick replica unless you decode changes, and switch to logical the moment you plan to, because it needs a restart. minimal is useful for a disposable bulk-load instance, not for anything you would want to restore.

synchronous_commit: what a commit waits for

Durability is a per-transaction choice: synchronous_commit can be set globally, per role, per session or with SET LOCAL, and the value in force at commit decides what that commit waits for.

The remote levels only mean something when synchronous_standby_names is set; without it, they behave like local. ANY 1 (a, b) waits for whichever of two standbys answers first, so losing one standby does not stall writes. FIRST 1 (a, b) waits for the highest-priority standby that is connected.

Keep on as the default and lower it per transaction where loss is acceptable: a click-tracking insert can use SET LOCAL synchronous_commit = off while payments keep the default. Unlike turning off fsync, which risks corruption, this can only lose recent commits.

Full-page writes and where WAL volume really comes from

A PostgreSQL page is 8 kB, but many disks only promise atomic writes of 4 kB or less, so a power failure can leave a torn page that no incremental record can repair. With full_page_writes on, the first modification of a page after a checkpoint logs the whole page image, so recovery restores the page and replays later records on top.

So WAL volume depends on how many distinct pages you touch per checkpoint interval, not just how many changes you make. Random updates across a large table are the worst case: almost every update pays roughly 8 kB instead of roughly 100 bytes. wal_fpi in pg_stat_wal shows this directly.

Two levers reduce it. wal_compression compresses page images with pglz, lz4 or zstd (off by default; the last two depend on build options), trading CPU for bytes. A longer checkpoint interval means each page pays its image less often. Turning full_page_writes off is only safe on storage that guarantees atomic page writes.

Worked example: sizing checkpoints from a measurement

Take a 50 GB table, 6,553,600 pages of 8 kB, receiving 2,000 single-row updates per second at uniformly random rows, and ignore indexes to keep the arithmetic visible. With checkpoint_timeout = 5min, one interval holds 600,000 updates. The expected number of distinct pages touched is N(1 - e^(-k/N)) = about 573,000, so full-page images contribute up to 4.4 GB per interval, about 15 MB per second, while the records themselves at roughly 120 bytes each add only about 70 MB. Full-page images are over 98 percent of the WAL.

Now max_wal_size, which defaults to 1 GB. Because WAL keeps accumulating while a checkpoint runs, the server requests one after max_wal_size / (1 + checkpoint_completion_target) of WAL, about 540 MB by default: at 15 MB per second, every 36 seconds or so, and each checkpoint restarts the full-page cycle, so volume gets worse. pg_stat_checkpointer would show num_requested far above num_timed. The log's "checkpoints are occurring too frequently" hint only appears below checkpoint_warning (30 s), so its absence proves little.

Recompute for longer intervals. At 15 minutes, 1.57 million distinct pages give 12.0 GB per interval; at 30 minutes, 2.77 million pages give 21.1 GB. Per five minutes of wall time that is 4.4, 4.0 and 3.5 GB: a 30-minute interval saves about 20 percent of WAL. The savings are real but modest here, because with uniformly random updates most pages touched in an interval are still being touched for the first time; when updates concentrate on a hot subset of pages, a longer interval saves far more. The cost is recovery time: after a crash, PostgreSQL replays everything since the last checkpoint, so a 30-minute interval can mean replaying 20 GB or more.

The configuration below picks 15 minutes, max_wal_size = 32GB (a trigger point of 32 / 1.9, about 16.8 GB, 40 percent above the 12 GB per interval, so checkpoints stay timed without counting on compression) andwal_compression = lz4. Then re-measure with the queries that follow, and check that num_timed is now the counter that grows.

# postgresql.conf excerpt for the worked example
wal_level = replica
synchronous_standby_names = 'ANY 1 (standby_a, standby_b)'
full_page_writes = on
wal_compression = lz4               # needs a build with lz4
checkpoint_timeout = 15min
checkpoint_completion_target = 0.9
max_wal_size = 32GB                 # >= 1.9 x measured WAL per interval, plus margin
min_wal_size = 2GB
archive_mode = on
archive_command = '/usr/local/bin/wal-push %p %f'   # exit 0 only once durable
max_slot_wal_keep_size = 100GB      # default -1 is unlimited

-- 1. WAL generation: sample twice, 60 seconds apart, and subtract.
SELECT wal_records, wal_fpi, wal_bytes, wal_buffers_full FROM pg_stat_wal;

-- 2. Timed (good) versus requested (max_wal_size too small) checkpoints. PostgreSQL 17+.
SELECT num_timed, num_requested FROM pg_stat_checkpointer;

-- 3. What each slot retains, and how close it is to being lost.
SELECT slot_name, active, wal_status, safe_wal_size,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained
FROM   pg_replication_slots;

-- 4. Is archiving keeping up?
SELECT archived_count, last_archived_wal, failed_count, last_failed_wal FROM pg_stat_archiver;

Archiving and point-in-time recovery

A base backup plus every WAL segment since lets you restore to any moment: restore the backup, provide a restore_command, set recovery_target_time, create recovery.signal and start the server. This is the only defence against mistakes replicas copy faithfully, such as a DROP TABLE at 14:02.

Archiving needs archive_mode (a restart) and either archive_command or an archive_library module. The command must exit zero only once the segment is durably stored and must never overwrite a different file of the same name. The documentation's cp example does not fsync; use a purpose-built tool that uploads, verifies and retries.

If archiving keeps failing, PostgreSQL keeps every unarchived segment and pg_wal grows until the disk fills, so alert on pg_stat_archiver lag, not only failures. archive_timeout forces a segment switch after a quiet period, bounding how much committed work exists only on the primary.

Replication slots and wal_keep_size

A disconnected standby needs the primary to keep the WAL it has not received. wal_keep_size (0 by default) keeps a fixed amount; a replication slot keeps whatever its consumer still needs. Logical replication and CDC tools use slots too, and the classic outage is a slot whose consumer died weeks ago, retaining WAL until the disk is full. Logical replication in PostgreSQL covers the consumer side.

Three settings bound the damage. max_slot_wal_keep_size (default -1, unlimited) caps how much WAL slots may retain; past it, the slot's wal_status moves from reserved or extended to unreserved and then lost, and the consumer must be rebuilt. idle_replication_slot_timeout (0, disabled) invalidates slots inactive for longer than a set time. safe_wal_size says how many bytes can still be written before a slot is lost; alert on it. The cap is a trade-off: a lost slot means re-syncing a consumer, but a full disk takes down the primary.

Failure modes and what they look like

• pg_wal fills the disk. When PostgreSQL cannot write WAL it raises a PANIC and stops. Never delete files from pg_wal by hand; removing a segment still needed for recovery can make the cluster unrecoverable. Add space, fix the cause (failing archive, a stuck slot you then drop, max_wal_size larger than the volume), start the server, and let the next checkpoint remove old segments.
• Commit latency follows the disk. Every synchronous commit waits for a flush, so slow or shared WAL storage caps commit rate. Put pg_wal on low-latency storage.
• Write spikes after each checkpoint. WAL bytes jump after a checkpoint and decay through the interval: the full-page-write cycle.
• Standbys fall behind on replay. A standby can receive WAL fast but replay it slowly, growing lag with no network problem; compare its received and replayed LSNs. Database replication in depth covers what that lag means at failover.
• WAL grows from maintenance. Index rebuilds, bulk updates and vacuum write WAL too, and a vacuum that freezes an old table can touch every page; vacuum and bloat explains why.

What to do next

1. Run the four queries above and record WAL bytes per minute, full-page-image share, timed versus requested checkpoints, slot retention and archive status.
2. If requested checkpoints dominate, raise max_wal_size above (1 + checkpoint_completion_target) times the measured WAL per checkpoint_timeout, with margin, and decide the interval from the recovery time you can tolerate.
3. Run pg_waldump --stats=record over a busy segment range; if full-page images dominate, enable wal_compression and measure the before and after.
4. Confirm wal_level matches your plans for CDC before you need a restart.
5. Audit synchronous_commit: keep the default for money and identity, lower it per transaction where loss is acceptable, and set synchronous_standby_names with ANY if you rely on a standby.
6. Set max_slot_wal_keep_size, alert on safe_wal_size and on inactive slots, and drop slots without a live consumer.
7. Restore a base backup to a target time on a scratch host this month, and write down how long it took.