Generazione di WAL e write amplification

Every change in PostgreSQL is first written to the Write-Ahead Log (WAL) before it touches the data files. WAL guarantees durability and crash recovery, but the bytes it produces are not free.

• Disk I/O: WAL is fsynced on commit, so high WAL volume means more write throughput pressure.
• Replication: every WAL byte must be shipped to replicas and read by logical decoders.
• Backups: archived WAL (via archive_command or pg_receivewal) grows storage and restore time.

This lesson is about measuring the WAL each transaction generates and reducing it without sacrificing durability.

WAL records are emitted for far more than just your row changes. Knowing the sources is the first step to cutting volume.

• Heap/index changes: inserts, updates, deletes, and the index entries they touch.
• Full Page Images (FPIs): the first write to a page after a checkpoint logs the entire 8 KB page, not just the change.
• HOT pruning and visibility map updates.
• Hint bit / freeze writes during VACUUM.

FPIs are usually the single biggest and most surprising contributor to write amplification.

Since PostgreSQL 13, EXPLAIN (ANALYZE, WAL) reports exactly how much WAL a statement produced: the number of records, the number of full page images, and the total bytes.

This is the most precise tool for attributing WAL to a specific query. Watch the fpi count closely — a high FPI count signals checkpoint-driven amplification.

The WAL line in the plan looks like this:

• WAL: records=12043 fpi=512 bytes=4823104

Interpretation:

• records: total WAL records emitted (one or more per tuple touched).
• fpi: full page images — each is roughly 8 KB, so 512 FPIs ≈ 4 MB of the total alone.
• bytes: the grand total written to WAL.

If fpi × 8192 is a large fraction of bytes, your amplification is dominated by full page images, not by the logical change itself.

For a cluster-level view, pg_stat_wal (PostgreSQL 14+) aggregates WAL generation. Sample it, run a workload, sample again, and diff.

Key columns: wal_records, wal_fpi, and wal_bytes. A rising wal_fpi rate between checkpoints confirms FPI-driven amplification at the system level.

You can also measure WAL produced over any interval by differencing the current Log Sequence Number (LSN). The LSN is a monotonic byte offset into the WAL stream.

Capture pg_current_wal_lsn() before and after a workload, then subtract. This counts ALL WAL, including background work like autovacuum.

An FPI is written on the first modification of a page after a checkpoint. So checkpoints that fire too often force the same hot pages to be re-imaged repeatedly.

The fix is to spread checkpoints out:

• Raise max_wal_size so checkpoints are triggered by volume less aggressively.
• Raise checkpoint_timeout (e.g. 15min) so fewer time-based checkpoints occur.
• Keep checkpoint_completion_target near 0.9 to smooth the flush, not to reduce FPIs.

Fewer checkpoints means each hot page is imaged once across a longer window — a direct cut in WAL bytes.

When FPIs are unavoidable, you can compress them. wal_compression compresses full page images before they are written to WAL.

• off: no compression (default on older versions).
• pglz: cheap, modest ratio.
• lz4 / zstd (PostgreSQL 15+): better ratios, zstd for maximum reduction.

This trades a little CPU for potentially large WAL savings on FPI-heavy workloads. It does NOT compress regular WAL records, only the page images.

A Heap-Only Tuple (HOT) update avoids writing new index entries when no indexed column changes and the new tuple fits on the same page. Fewer index writes means less WAL.

Two levers maximize HOT:

• Don't update indexed columns when you don't have to — narrow your SET list.
• Leave free space on pages with a lower fillfactor so the new tuple version stays on the same page.

Check the n_tup_hot_upd ratio to confirm HOT is actually firing.

Two more structural reductions:

• Batch writes: one large INSERT ... SELECT or COPY produces far less WAL overhead than thousands of single-row transactions, because per-record and per-commit overhead is amortized.
• UNLOGGED tables: skip WAL entirely for scratch, staging, or derived data. The tradeoff is that the table is truncated after a crash and is NOT replicated.

Use unlogged tables only where losing the data on crash is acceptable — ETL staging, materialized scratch, session caches.

Beyond knobs, schema and access patterns drive WAL volume:

• Avoid wide updates: updating a row rewrites the whole tuple plus FPIs for its page. Split rarely-updated wide columns into a side table.
• Lower fillfactor on hot tables (e.g. 80) to keep HOT updates alive.
• Prune redundant indexes: every index multiplies write WAL.
• Use COPY for bulk loads and consider the COPY ... FREEZE path for fresh tables.

Each design choice compounds: fewer FPIs, fewer index entries, fewer commits.

You run EXPLAIN (ANALYZE, WAL) on a batch UPDATE and see WAL: records=20000 fpi=9800 bytes=82000000. The FPIs account for roughly 80 MB of the 82 MB total. Which single change most directly reduces this WAL volume?

You now have a full WAL-reduction toolkit:

• Measure: EXPLAIN (ANALYZE, WAL) per statement, pg_stat_wal cluster-wide, and pg_wal_lsn_diff() for raw volume over an interval.
• Read the signal: high fpi means checkpoint-driven amplification; high records with low fpi means logical change volume.
• Reduce FPIs: raise max_wal_size and checkpoint_timeout; enable wal_compression (zstd/lz4).
• Reduce records: favor HOT updates (narrow SET lists, lower fillfactor), batch writes, drop redundant indexes.
• Skip WAL: UNLOGGED tables for disposable data.

Always measure first — attribute the bytes before you turn any knob.