WAL segments, LSNs and generation rate · PostgreSQL

WAL is a single logical stream written into fixed-size files. Knowing how fast yours grows is a prerequisite for sizing archives, replication links, and the filesystem pg_wal lives on.

Segments

The stream is divided into segments of wal_segment_size, 16 MB by default and fixed at initdb time. A segment is named from three 8-character hexadecimal fields:

0000000100000007000000CF
^^^^^^^^                  timeline
        ^^^^^^^^          high 32 bits of the LSN
                ^^^^^^^^  segment number within that

The mapping is available directly rather than by hand:

Read-only / SafeLSN to segment file— Captured on 18.6 with the default 16 MB segment size.

$ psql -U postgres -c "SELECT pg_walfile_name('7/CF446178')"

 pg_walfile_name
--------------------------
0000000100000007000000CF

Segment size is an initdb decision

initdb --wal-segsize=64 -D /var/lib/postgresql/18/main

Larger segments mean fewer files, which matters on clusters generating hundreds of gigabytes a day where 16 MB files produce tens of thousands of archive operations per day and an archive_command invocation for each.

It cannot be changed afterwards without a dump and reload, so it sits alongside checksums, block size and locale in lesson VI-06’s list of decisions to get right while the cluster is empty.

The counter-argument for a busy cluster: archive_timeout forces a segment switch after a period even if the segment is not full, and a partially-filled 64 MB segment is archived as 64 MB. On a cluster with low write volume and a short archive_timeout, large segments waste archive space.

Measuring the rate

Read-only / SafeWAL generated by a measured workload— Captured on 18.6. pgbench with 8 clients for 20 seconds, LSN read before and after.

$ psql -U postgres -At -c "SELECT pg_current_wal_lsn()"   # before and after the workload

tps = 2218.150222

start_lsn   |  end_lsn   | wal_in_20s | wal_per_second
-------------+------------+------------+----------------
7/C0F3DB58  | 7/C645F2A0 | 85 MB      | 4359 kB

4,359 kB per second. Projected:

Period	WAL
Per minute	~255 MB
Per hour	~15 GB
Per day	~367 GB

That is the number every downstream capacity question depends on:

pg_wal filesystem size must hold max_wal_size plus whatever slots and archiving retain.
Archive storage grows at this rate, before compression.
Replication bandwidth must sustain it, or replicas fall behind permanently.
Recovery time is this rate multiplied by the checkpoint distance.

The measurement takes twenty seconds. Do it on the real workload rather than on pgbench, and do it again after any significant schema change.

-- a reusable form: run twice, a minute apart
SELECT now(), pg_current_wal_lsn();

`pg_stat_wal`

Read-only / Safepg_stat_wal after about two and a half hours of mixed load— Captured on 18.6. Note wal_buffers_full.

$ psql -U postgres -x -c "SELECT * FROM pg_stat_wal"

wal_records      | 82823247
wal_fpi          | 3150464
wal_bytes        | 33429991279
wal_buffers_full | 2554998
stats_reset      | 2026-08-27 18:31:22.55921+00

Column	Reading
`wal_records`	Total records written
`wal_fpi`	Full page images. Against `wal_records`, how much of your WAL is torn-page protection
`wal_bytes`	Total bytes. With `stats_reset`, gives the rate
`wal_buffers_full`	Times a backend had to wait because the WAL buffer was full

wal_buffers_full is the one that has an action attached

2,554,998 occurrences over two and a half hours means backends repeatedly found the WAL buffer full and had to wait for it to be flushed before they could continue. That wait is on the commit path.

ALTER SYSTEM SET wal_buffers = '64MB';   -- requires a restart

The default is 1/32 of shared_buffers, capped at 16 MB, which on a cluster with a high write rate is small. Raising it is cheap — it is shared memory, allocated once — and the diagnostic is unambiguous: wal_buffers_full growing means it is too small.

Compare wal_fpi against wal_records at the same time. On this cluster, 3.15 million full page images against 82.8 million records is about 3.8% of records but a much larger share of the bytes, since a full page image is 8 kB and an ordinary record is tens of bytes. If that share is high, wal_compression is the lever, and lengthening checkpoint_timeout is the other.

Note that PostgreSQL 18 also exposes wal_buffers_full per statement in pg_stat_statements, which attributes the waiting to specific queries rather than only to the cluster. The column was confirmed present on pg_stat_statements 1.12 on an 18.6 cluster.

What moves the rate

In rough order of how much difference each makes:

Non-HOT updates. Lesson VII-02 measured 2.8× more WAL from fillfactor alone, and 2.6× from adding one index to an updated column. This is usually the largest available reduction.

Index count. Lesson X-05 measured 3.6× more WAL for seven indexes against one, on identical inserts.

Checkpoint frequency. Every checkpoint restarts the full-page-write cycle. Lesson XII-01 measured 50% more WAL in the round following one.

Freezing. Lesson VIII-05 measured a first freeze generating roughly the table’s own size in WAL.

wal_compression. Compresses full page images specifically. On a workload where wal_fpi is a large share of bytes, this is straightforwardly worthwhile.

Batch size. Ten thousand single-row transactions generate substantially more WAL than one transaction inserting ten thousand rows, because each commit writes its own record and each may force a flush.

Why pg_wal can exceed max_wal_size, and by how much

max_wal_size is not a limit. The documentation calls it a soft target, and the distinction matters during an incident when a filesystem is filling.

It is the amount of WAL that may accumulate between checkpoints before one is triggered. Once triggered, the checkpoint takes time to complete — up to checkpoint_completion_target × checkpoint_timeout, which is 270 seconds on defaults — and WAL continues to be generated throughout. So the steady-state size is roughly:

max_wal_size + (generation rate × checkpoint duration)

At 4,359 kB/s and a 270-second checkpoint, that is 1 GB plus about 1.1 GB — more than double the configured value, in normal operation with nothing wrong.

Three things can take it far higher, and none of them is bounded by max_wal_size:

A replication slot whose consumer is not advancing. WAL is retained from the slot’s restart_lsn regardless of anything else. This is the most common cause of a full pg_wal, and max_slot_wal_keep_size is the setting that bounds it.

A failing archive_command. WAL that has not been archived cannot be recycled. A silent archive failure fills the disk at exactly the rate measured above.

wal_keep_size, which deliberately retains a fixed amount for standbys that may reconnect.

The query that tells you which:

SELECT slot_name, active,
       pg_size_pretty(pg_current_wal_lsn() - restart_lsn) AS retained
  FROM pg_replication_slots
 ORDER BY pg_current_wal_lsn() - restart_lsn DESC;

SELECT archived_count, last_archived_wal, last_archived_time,
       failed_count, last_failed_wal, last_failed_time
  FROM pg_stat_archiver;

Size the pg_wal filesystem for the steady state plus a genuine margin, and alert on its usage rather than trusting max_wal_size to bound it. Lesson XII-03 is entirely about what retains WAL.

What to take from this

Segments are 16 MB by default and fixed at initdb. The name encodes timeline and LSN.
Measured: 2,218 tps produced 4,359 kB/s, about 367 GB a day. Measure yours; it takes twenty seconds.
wal_buffers_full growing means wal_buffers is too small. It is cheap shared memory and needs a restart.
wal_fpi against wal_records says how much of your WAL is torn-page protection.
The largest reductions available are usually HOT updates and index count, not WAL settings.
max_wal_size is a soft target. Steady-state pg_wal is larger, and slots and archiving are not bounded by it at all.

Cross-course references

Observability for Production Sysadmins — Part XIII (Rates and counters) covers turning an LSN into a generation rate, which is the input to archive sizing and to replication bandwidth.
Linux for Production Sysadmins — Part XLI (Storage Performance) covers whether the device can absorb that rate alongside everything else on it.