Lab 15: Measure exactly how much WAL each statement writes

Objective

WAL volume determines three things you care about: how much bandwidth replication needs, how much space archiving consumes, and how long crash recovery takes. All three are usually estimated by guesswork.

They do not have to be. The WAL position is a monotonically increasing byte offset that any session can read, so the WAL a statement generates is the difference between the position before and the position after. That is a two-line measurement, and this lab builds it into a habit.

The measurements produce several results worth knowing in advance of needing them. Deleting 10,000 rows writes 549 KB; truncating the whole 210,000-row table writes 2 KB. The same UPDATE costs 7.7 MB or 5.4 MB depending on nothing but when the last checkpoint was. And lz4 compression removed a third of the WAL while running faster than no compression at all.

Architecture

One table, one measurement technique applied to everything.

flowchart LR
    B["pg_current_wal_lsn()\nbefore"] --> S["the statement"]
    S --> A["pg_current_wal_lsn()\nafter"]
    A --> D["pg_wal_lsn_diff(after, before)\n= bytes of WAL"]
    D --> U1["per statement kind"]
    D --> U2["with and without a recent checkpoint"]
    D --> U3["across wal_compression settings"]
    W["pg_stat_wal"] --> U4["wal_fpi, wal_buffers_full,\ncumulative totals"]

Requirements

• A PostgreSQL 18 cluster with superuser access. The lab creates and drops a database called lab15, and issues several explicit CHECKPOINT commands.
• A build with lz4 and zstd for Task 6. The Debian PGDG packages include both. If yours does not, ALTER SYSTEM SET wal_compression will reject the value and you can skip those two rows.
• Roughly 200 MB of free disk, for the data and the WAL it generates.

Scenario

A replica in another region is falling behind during the nightly batch, and the WAL archive has doubled in size since last quarter. Nobody knows which part of the workload produces the WAL, so nobody can say whether the fix is in the batch job, the configuration or the network.

Tasks

Task 1 — Read the settings and the current position

LAB="$HOME/rbpg-lab-15"
mkdir -p "$LAB"

docker exec -i -u postgres rbpg-lab01 psql -X -c "CREATE DATABASE lab15;"
docker exec -i -u postgres rbpg-lab01 psql -X -d lab15 <<'SQL'
CREATE TABLE ledger(id int PRIMARY KEY, account int, amount numeric, note text);
INSERT INTO ledger SELECT g, g%1000, (g%97)*1.5, repeat('x',50)
  FROM generate_series(1,200000) g;
SQL
docker exec -u postgres rbpg-lab01 psql -X -d lab15 -c "VACUUM ANALYZE ledger;"

docker exec -u postgres rbpg-lab01 psql -X -c "
  SELECT name, setting, unit, context FROM pg_settings
  WHERE name IN ('wal_level','full_page_writes','wal_compression','wal_segment_size',
                 'checkpoint_timeout','max_wal_size','min_wal_size','wal_buffers',
                 'synchronous_commit') ORDER BY name;"

docker exec -u postgres rbpg-lab01 psql -X -c "
  SELECT pg_current_wal_lsn() AS lsn, pg_walfile_name(pg_current_wal_lsn()) AS segment;"

$ a pg_settings query, then pg_current_wal_lsn and pg_walfile_name

        name        | setting  | unit |  context   
--------------------+----------+------+------------
checkpoint_timeout | 300      | s    | sighup
full_page_writes   | on       |      | sighup
max_wal_size       | 1024     | MB   | sighup
min_wal_size       | 80       | MB   | sighup
synchronous_commit | on       |      | user
wal_buffers        | 512      | 8kB  | postmaster
wal_compression    | off      |      | superuser
wal_level          | replica  |      | postmaster
wal_segment_size   | 16777216 | B    | internal
(9 rows)

  lsn     |         segment          
------------+--------------------------
0/BF7A4EE8 | 0000000100000000000000BF
(1 row)

An LSN is a byte offset into the notional infinite WAL stream, written as two hex halves. pg_wal_lsn_diff(a, b) returns the number of bytes between two of them, which is the whole measurement technique.

wal_segment_size is 16 MB, so 0000000100000000000000BF is the 0xBF-th segment: timeline 1, and the BF in the LSN’s high half.

Task 2 — Build the measurement

measure() {
  DESC="$1"; SQL="$2"
  B=$(docker exec -u postgres rbpg-lab01 psql -X -tAd lab15 -c "SELECT pg_current_wal_lsn();")
  docker exec -u postgres rbpg-lab01 psql -X -d lab15 -c "$SQL" > /dev/null
  A=$(docker exec -u postgres rbpg-lab01 psql -X -tAd lab15 -c "SELECT pg_current_wal_lsn();")
  BYTES=$(docker exec -u postgres rbpg-lab01 psql -X -tAd lab15 \
           -c "SELECT pg_wal_lsn_diff('$A','$B');")
  PRETTY=$(docker exec -u postgres rbpg-lab01 psql -X -tAd lab15 \
           -c "SELECT pg_size_pretty(pg_wal_lsn_diff('$A','$B'));")
  printf "%-46s %12s bytes  (%s)\n" "$DESC" "$BYTES" "$PRETTY"
}

Three points on doing this correctly. Take both readings from a session that is otherwise idle, or you will measure other work as well. Take them outside the transaction, since the WAL is only guaranteed flushed at commit. And on a busy server this measures everything happening concurrently, so use an idle one.

Task 3 — WAL per statement kind

{
measure "SELECT 1 (read only)"                        "SELECT 1;"
measure "UPDATE 1 row"                                "UPDATE ledger SET amount = amount + 1 WHERE id = 1;"
measure "UPDATE 1 row again (same page, now dirty)"   "UPDATE ledger SET amount = amount + 1 WHERE id = 1;"
measure "UPDATE 10000 rows"                           "UPDATE ledger SET amount = amount + 1 WHERE id <= 10000;"
measure "DELETE 10000 rows"                           "DELETE FROM ledger WHERE id BETWEEN 100001 AND 110000;"
measure "INSERT 10000 rows"                           "INSERT INTO ledger SELECT g+1000000, g%1000, 1.0, repeat('y',50) FROM generate_series(1,10000) g;"
measure "TRUNCATE (whole table)"                      "TRUNCATE ledger;"
} | tee "$LAB/per-statement.txt"

$ the measure function applied to seven statements

SELECT 1 (read only)                                      0 bytes  (0 bytes)
UPDATE 1 row                                            312 bytes  (312 bytes)
UPDATE 1 row again (same page, now dirty)               168 bytes  (168 bytes)
UPDATE 10000 rows                                   2776576 bytes  (2712 kB)
DELETE 10000 rows                                    562648 bytes  (549 kB)
INSERT 10000 rows                                   1866008 bytes  (1822 kB)
TRUNCATE (whole table)                                 2336 bytes  (2336 bytes)

Read the last three lines together.

DELETE of 10,000 rows: 549 KB. A delete writes a small record per row — it only has to stamp xmax, as Lab 9 showed — so 56 bytes per row.

TRUNCATE of 210,000 rows: 2,336 bytes. Not 2 KB per row; 2 KB in total. TRUNCATE does not touch rows at all. It creates a new, empty file for the table and records that fact, so its WAL cost is independent of how much data it removed.

SELECT: 0 bytes, as expected — though note that a read-only query can generate WAL, when it sets visibility hint bits on pages it reads and those pages need full-page images. The zero here is because this table was just vacuumed.

Task 4 — Checkpoint frequency changes the volume of identical work

docker exec -i -u postgres rbpg-lab01 psql -X -d lab15 -c \
  "INSERT INTO ledger SELECT g, g%1000, (g%97)*1.5, repeat('x',50) FROM generate_series(1,200000) g;"
docker exec -u postgres rbpg-lab01 psql -X -d lab15 -c "VACUUM ANALYZE ledger;"

{
docker exec -u postgres rbpg-lab01 psql -X -c "CHECKPOINT;" > /dev/null
measure "UPDATE 20000 rows, FIRST touch after a checkpoint" "UPDATE ledger SET amount = amount + 1 WHERE id <= 20000;"
measure "the identical UPDATE again, pages already dirty"   "UPDATE ledger SET amount = amount + 1 WHERE id <= 20000;"
measure "and a third time"                                  "UPDATE ledger SET amount = amount + 1 WHERE id <= 20000;"
docker exec -u postgres rbpg-lab01 psql -X -c "CHECKPOINT;" > /dev/null
measure "after another CHECKPOINT, the same UPDATE"         "UPDATE ledger SET amount = amount + 1 WHERE id <= 20000;"
} | tee "$LAB/full-page-writes.txt"

$ the same UPDATE four times, with checkpoints before the first and the last

UPDATE 20000 rows, FIRST touch after a checkpoint    7769 kB
the identical UPDATE again, pages already dirty      5377 kB
and a third time                                     5352 kB
after another CHECKPOINT, the same UPDATE           10028 kB

Identical statements. The one immediately after a checkpoint wrote 45% more WAL than the ones that followed it, and the one after the second checkpoint wrote nearly twice as much as the steady-state runs.

Task 5 — The server’s own accounting

docker exec -u postgres rbpg-lab01 psql -X -c "SELECT * FROM pg_stat_wal;" | tee "$LAB/capacity.txt"

$ psql -X -c "SELECT * FROM pg_stat_wal;"

 wal_records | wal_fpi | wal_bytes  | wal_buffers_full |          stats_reset          
-------------+---------+------------+------------------+-------------------------------
  35593915 |   51731 | 3227081535 |           333217 | 2026-08-28 00:09:02.920701+00
(1 row)

• wal_fpi — full page images written. Rising faster than wal_records means checkpoints are too frequent for the write pattern.
• wal_bytes — total bytes. Divide by elapsed time for the rate your archive and replication link must sustain.
• wal_buffers_full — times a backend had to flush the WAL buffer because it was full before it could continue.

That last one deserves a look:

docker exec -u postgres rbpg-lab01 psql -X -c "
  SELECT wal_buffers_full, wal_bytes,
         round(wal_buffers_full * 8192.0 / nullif(wal_bytes,0) * 100, 2) AS pct
  FROM pg_stat_wal;"
docker exec -u postgres rbpg-lab01 psql -X -c \
  "SELECT name, setting, unit FROM pg_settings WHERE name='wal_buffers';"

$ compute the proportion of WAL that was written by a buffer-full flush, then read wal_buffers

 wal_buffers_full | wal_bytes  |  pct  
------------------+------------+-------
         334861 | 3256633564 | 84.23
(1 row)

  name     | setting | unit 
-------------+---------+------
wal_buffers | 512     | 8kB
(1 row)

wal_buffers is 512 × 8 kB = 4 MB, and this bulk-loading workload filled it 334,861 times. Every one of those is a backend stopping mid-statement to flush.

Task 6 — Compare the compression algorithms

compare() {
  docker exec -u postgres rbpg-lab01 psql -X -c "CHECKPOINT;" > /dev/null
  B=$(docker exec -u postgres rbpg-lab01 psql -X -tAd lab15 -c "SELECT pg_current_wal_lsn();")
  S=$(date +%s%N)
  docker exec -u postgres rbpg-lab01 psql -X -d lab15 -c \
    "UPDATE ledger SET amount = amount + 1 WHERE id <= 20000;" > /dev/null
  E=$(date +%s%N)
  A=$(docker exec -u postgres rbpg-lab01 psql -X -tAd lab15 -c "SELECT pg_current_wal_lsn();")
  P=$(docker exec -u postgres rbpg-lab01 psql -X -tAd lab15 \
       -c "SELECT pg_size_pretty(pg_wal_lsn_diff('$A','$B'));")
  printf "wal_compression=%-8s  WAL %-10s  time %s ms\n" "$1" "$P" "$(( (E-S)/1000000 ))"
}

for C in off pglz lz4 zstd; do
  docker exec -u postgres rbpg-lab01 psql -X -c "ALTER SYSTEM SET wal_compression = $C;"
  docker exec -u postgres rbpg-lab01 psql -X -c "SELECT pg_reload_conf();"
  sleep 1
  compare "$(docker exec -u postgres rbpg-lab01 psql -X -tAc 'SHOW wal_compression;')"
done | tee "$LAB/compression.txt"

$ the same UPDATE at four wal_compression settings, each after a checkpoint

wal_compression=off       WAL 10051 kB    time 116 ms
wal_compression=pglz      WAL 6638 kB     time 136 ms
wal_compression=lz4       WAL 6725 kB     time 102 ms
wal_compression=zstd      WAL 6087 kB     time 120 ms

lz4 removed a third of the WAL and finished 12% faster than writing it uncompressed. That is not a trade-off; on this workload it is strictly better, because compressing 8 kB with lz4 costs less time than writing the extra 3 MB.

zstd compressed hardest, at a modest time cost. pglz — the original algorithm and the only option on older versions — was both slower and slightly worse than lz4.

Task 7 — WAL per transaction, for capacity planning

docker exec -u postgres rbpg-lab01 psql -X -c "
  SELECT (SELECT xact_commit + xact_rollback FROM pg_stat_database WHERE datname='lab15')
           AS transactions,
         wal_bytes, wal_records, wal_fpi
  FROM pg_stat_wal;" | tee -a "$LAB/capacity.txt"

$ join the database transaction counters to the cluster WAL totals

 transactions | wal_bytes  | wal_records | wal_fpi 
--------------+------------+-------------+---------
        157 | 3256633564 |    35837148 |   54232
(1 row)

On a production server, sample this pair twice an hour apart and divide. That gives you:

• Bytes per second, which the replication link must sustain at peak, not on average — a batch window that produces ten times the daytime rate is what makes a replica fall behind.
• Bytes per day, which the archive must store for the whole retention period.
• wal_fpi / wal_records, which tells you whether wal_compression is worth enabling and whether checkpoints are too frequent.

Validation

test -s "$LAB/per-statement.txt"     && echo "OK per-statement"
test -s "$LAB/full-page-writes.txt"  && echo "OK full-page-writes"
test -s "$LAB/compression.txt"       && echo "OK compression"
test -s "$LAB/capacity.txt"          && echo "OK capacity"

grep -q "TRUNCATE"        "$LAB/per-statement.txt"    && echo "OK truncate measured"
grep -q "after a checkpoint" "$LAB/full-page-writes.txt" && echo "OK FPI effect captured"
grep -q "lz4"             "$LAB/compression.txt"      && echo "OK compression compared"
grep -q "wal_fpi"         "$LAB/capacity.txt"         && echo "OK pg_stat_wal captured"

Questions to answer without looking anything up:

1. Deleting 10,000 rows wrote 549 KB; truncating 210,000 wrote 2 KB. Why?
2. The same UPDATE wrote 7.7 MB once and 5.4 MB the next two times. What changed?
3. You halve checkpoint_timeout. What happens to WAL volume, and what do you gain in return?
4. wal_compression = lz4 was faster than off. How is that possible?
5. Your workload is small updates to a hot working set that never checkpoints between them. Will wal_compression help?

Expected Outcome

You have a measurement technique that works on any PostgreSQL server:

SELECT pg_current_wal_lsn();   -- before
-- run the thing
SELECT pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), '<before>'));

And four results that inform real decisions:

• TRUNCATE and partition drops are effectively free in WAL terms; DELETE is not.
• Checkpoint frequency is a WAL volume control, traded against recovery time.
• wal_compression = lz4 is close to free and often better than free, on any workload with a meaningful share of full page images.
• wal_buffers_full is a specific, actionable number, and 84% of WAL arriving through a full-buffer flush means the setting is too small.

Troubleshooting

pg_wal_lsn_diff returns 0 for work you know wrote data. The statement ran inside a transaction that has not committed, or you captured the second LSN before the commit completed. Capture after COMMIT.

TRUNCATE generated far less WAL than the equivalent DELETE, by orders of magnitude. That is correct and it is one of the lab’s results: TRUNCATE records a file-level operation, while DELETE records every row version it marks.

The same workload generates different WAL volumes on consecutive runs. Full page images. The first write to a page after a checkpoint writes the whole page; subsequent writes to it do not. This is Task 4, and it means WAL volume depends on checkpoint frequency and not only on the work done.

pg_stat_wal does not have the timing columns you expected. PostgreSQL 18 removed the four timing columns from pg_stat_wal. WAL I/O timing now comes from pg_stat_io with track_wal_io_timing on.

wal_compression appears to make no difference. Its effect is on full page images, so it shows on a workload that writes to many distinct pages shortly after a checkpoint. Force a checkpoint, then measure.

Counters do not move. pg_stat_wal and pg_stat_checkpointer are cumulative since the last reset. Record the values before and subtract, or reset with pg_stat_reset_shared() and be aware that this discards history the rest of your monitoring may be using.

Cleanup

docker exec -u postgres rbpg-lab01 psql -X -c "ALTER SYSTEM RESET wal_compression;"
docker exec -u postgres rbpg-lab01 psql -X -c "SELECT pg_reload_conf();"
docker exec -u postgres rbpg-lab01 psql -X -c "DROP DATABASE IF EXISTS lab15;"
docker exec -u postgres rbpg-lab01 psql -X -c "SHOW wal_compression;"

Production notes

• The LSN-difference technique works on any server and needs no extension. It is the right instrument for “how much WAL does this release generate”, which is the input to archive sizing, replication bandwidth and retention.
• Size the archive from a measured bytes-per-day, then add margin for the maintenance operations that dwarf the workload. A first freeze of a large table generates WAL proportional to the table.
• wal_compression = lz4 measured close to free and frequently better than free on workloads with a meaningful share of full page images. Measure on your workload before adopting it, but it is usually a win.
• wal_buffers_full is actionable. A high proportion of WAL arriving through a full-buffer flush means wal_buffers is too small, and it is a cheap fix.
• Checkpoint frequency is a WAL-volume control as well as a recovery-time control. Lengthening the interval reduces full page images and lengthens crash recovery — a trade to make deliberately.

What You Learned

• pg_current_wal_lsn() and pg_wal_lsn_diff() measure WAL for any unit of work, on any server, with no extension.
• TRUNCATE and partition drops are effectively free in WAL terms; DELETE is proportional to the rows it touches.
• Checkpoint frequency changes the WAL volume of identical work, through full page images.
• wal_compression = lz4 is close to free and often better than free where full page images dominate.
• pg_stat_wal lost its timing columns in PostgreSQL 18; WAL I/O timing lives in pg_stat_io under track_wal_io_timing.
• wal_buffers_full names a specific problem with a cheap fix, and 84% of WAL arriving that way means the buffer is too small.