Monitoring WAL Generation and Retention

Measuring the rate at which a publisher produces WAL — bytes per second, not just bytes on disk — is what turns WAL stream mechanics from a static gauge into a forecast: multiply the generation rate against the headroom left under max_slot_wal_keep_size and you get the number of minutes before your slowest consumer’s slot is invalidated. Skip that arithmetic and the first sign of trouble is pg_wal at 95% and a primary about to refuse writes, with no runway left to add consumer capacity or raise a ceiling.

This page is about capacity and velocity across the whole publisher — the aggregate WAL generation rate, segment churn, and how to size retention ceilings against a measured rate and the slowest drain. It is deliberately not about diagnosing one stuck slot pinning restart_lsn; that failure signature and its remediation live in preventing WAL bloat from inactive slots. Everything here is validated against PostgreSQL 14 through 17, where pg_stat_wal first exposes cumulative generation counters.

Generation-Rate and Retention Semantics #

WAL retention pressure is the product of two independent quantities: how fast the publisher writes WAL, and how fast the slowest consumer confirms it. Monitoring either alone is misleading — a 40 MB/s generation rate is harmless if every slot drains at 40 MB/s, and a 2 MB/s rate is an incident if the slowest slot drains at zero. The table below fixes what each measurement source reports and how it maps to the retention forecast, targeting PostgreSQL 14–17.

The distinction that trips up most capacity plans is max_wal_size versus max_slot_wal_keep_size. The first governs checkpoint cadence and gives no protection against a lagging slot — a slot will happily retain WAL far past max_wal_size. Only max_slot_wal_keep_size bounds slot-driven retention, and it is the number your forecast divides into. Size max_wal_size for checkpoint smoothing under peak write load; size max_slot_wal_keep_size for the worst-case runway you are willing to tolerate before a consumer is sacrificed. The mechanics of how restart_lsn pins the oldest needed segment are covered in the parent WAL stream mechanics reference.

Diagnostic Patterns #

The generation rate is not a column you can read once — it is a slope between two samples. Take a paired sample of pg_stat_wal and the write head with a known interval, then convert to bytes per second:

-- Sample 1: record the baseline. Note the wall-clock time alongside.
SELECT now() AS t, wal_bytes, wal_records, wal_fpi
FROM pg_stat_wal \gset s1_
-- ... wait a fixed interval (e.g. 60 s) under representative load ...
-- Sample 2: compute the aggregate generation rate.
SELECT round(
         (pg_stat_wal.wal_bytes - :s1_wal_bytes) /
         extract(epoch FROM now() - :'s1_t')::numeric
       )                                                AS bytes_per_sec,
       pg_size_pretty(
         (pg_stat_wal.wal_bytes - :s1_wal_bytes)::bigint) AS wal_since_baseline
FROM pg_stat_wal;

A generation rate climbing past your provisioned drain capacity is the leading indicator; a bytes_per_sec above roughly 80% of your slowest consumer’s sustained drain rate means the runway is shrinking and you should alert well before pg_wal fills. Correlate the rate with full-page-image churn — a spike in wal_fpi after a checkpoint inflates WAL volume without any extra logical change and can briefly halve your runway:

-- Full-page images as a share of records reveals checkpoint-driven WAL spikes.
SELECT wal_records, wal_fpi,
       round(100.0 * wal_fpi / NULLIF(wal_records, 0), 1) AS fpi_pct,
       wal_buffers_full,          -- non-zero => raise wal_buffers
       stats_reset
FROM pg_stat_wal;

Inspect segment churn directly to see how fast pg_wal turns over and whether old segments are being recycled or pinned:

-- Live segment inventory: file count * 16 MB is on-disk WAL, and the oldest
-- file's age shows whether recycling is keeping up with generation.
SELECT count(*)                                   AS segment_files,
       pg_size_pretty(sum(size))                  AS wal_dir_size,
       min(modification)                          AS oldest_segment
FROM pg_ls_waldir()
WHERE name ~ '^[0-9A-F]{24}$';

Then fold in the slowest consumer to compute the net fill and the runway in a single query — this is the number to graph and alert on:

-- Net fill rate and runway per slot. keep_bytes from the configured cap.
WITH cap AS (
  SELECT NULLIF(current_setting('max_slot_wal_keep_size'), '-1') IS NOT NULL AS bounded
)
SELECT s.slot_name,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), s.restart_lsn)) AS retained,
       pg_size_pretty(
         1024::bigint*1024*1024*40 -
         pg_wal_lsn_diff(pg_current_wal_lsn(), s.restart_lsn))              AS headroom_vs_40gb
FROM pg_replication_slots s
WHERE s.slot_type = 'logical'
ORDER BY pg_wal_lsn_diff(pg_current_wal_lsn(), s.restart_lsn) DESC;

Alert when headroom_vs_40gb (substituting your real max_slot_wal_keep_size) divided by the measured net fill rate drops below 30 minutes — that is the point at which a human can no longer intervene before invalidation. Wire these gauges into the shared collector described in the monitoring and alerting guide rather than polling by hand.

Safe Deployment Sequence #

Sizing the two retention ceilings against a measured rate is a reload-only change, so the rollout below is zero-downtime and reversible without a restart.

1. Establish the baseline rate. Run the paired pg_stat_wal sample above across at least one full peak window and one checkpoint cycle. Record the 95th-percentile bytes_per_sec, not the mean — the runway must survive the worst minute, not the average one.

2. Set max_wal_size for checkpoint smoothing. If wal_buffers_full is non-zero or checkpoints fire more than every few minutes under peak load, raise it so a full write burst does not force a checkpoint mid-transaction:

   ALTER SYSTEM SET max_wal_size = '8GB';   -- soft checkpoint target, not a retention cap
   SELECT pg_reload_conf();
   

3. Set max_slot_wal_keep_size from the runway you will tolerate. Choose the ceiling as worst_case_rate × acceptable_runway. For an 18 MB/s peak and a 40-minute intervention budget: 18 MB/s × 2400 s ≈ 42 GB, so round to a safe 40GB:

   ALTER SYSTEM SET max_slot_wal_keep_size = '40GB';  -- never leave unbounded in prod
   SELECT pg_reload_conf();
   

4. Confirm the cap is live and the disk can hold it. The volume backing pg_wal must comfortably exceed max_slot_wal_keep_size + max_wal_size + wal_keep_size with margin:

   SELECT name, setting, unit FROM pg_settings
   WHERE name IN ('max_wal_size','max_slot_wal_keep_size','wal_keep_size');
   

5. Revert. Both parameters are reload-scoped, so rollback is symmetric — ALTER SYSTEM RESET max_slot_wal_keep_size; followed by SELECT pg_reload_conf(); restores unbounded retention immediately. Never lower the cap below the WAL a healthy slot legitimately needs during its slowest window, or you will invalidate a consumer that was merely catching up.

Pipeline Integration #

A Python monitoring sidecar should export the generation rate and per-slot runway as gauges, computing the rate itself from two samples so the metric backend never has to differentiate a monotonic counter. The pattern is a fixed-interval poll that emits bytes_per_sec and a projected runway_seconds for the slowest slot:

# Emits WAL generation rate and retention runway as Prometheus gauges.
import time, psycopg2
from prometheus_client import Gauge

wal_rate   = Gauge("pg_wal_generation_bytes_per_sec", "Aggregate WAL generation rate")
runway_sec = Gauge("pg_slot_retention_runway_seconds", "Time to slot invalidation", ["slot"])

KEEP_BYTES = 40 * 1024**3   # must mirror max_slot_wal_keep_size

def sample(cur):
    cur.execute("SELECT wal_bytes FROM pg_stat_wal")
    return cur.fetchone()[0], time.monotonic()

def poll(dsn, interval=60):
    conn = psycopg2.connect(dsn); conn.autocommit = True
    cur = conn.cursor()
    prev_bytes, prev_t = sample(cur)
    while True:
        time.sleep(interval)
        cur_bytes, cur_t = sample(cur)
        rate = (cur_bytes - prev_bytes) / (cur_t - prev_t)   # bytes/sec
        wal_rate.set(rate)
        cur.execute("""
            SELECT slot_name,
                   pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)
            FROM pg_replication_slots WHERE slot_type='logical'
        """)
        for slot, retained in cur.fetchall():
            headroom = max(KEEP_BYTES - retained, 0)
            # net fill ≈ generation rate minus this slot's own drain; guard div-by-zero.
            runway_sec.labels(slot).set(headroom / rate if rate > 0 else float("inf"))
        prev_bytes, prev_t = cur_bytes, cur_t

Two integration rules keep the forecast honest. First, reset the baseline sample after any pg_stat_reset_shared('wal') or server restart — a counter that goes backwards produces a negative rate and a nonsensical runway. Second, when the net rate is at or below zero, publish an explicit “unbounded” sentinel rather than infinity so downstream alert rules can treat no runway risk distinctly from missing data.

Failover handling. Generation rate is a property of whichever node is primary, so after a promotion the baseline sample must be re-taken against the new pg_stat_wal — the old counter does not carry over. A promoted standby that inherits the workload will show the same generation rate, but its slots start fresh, so the runway is effectively reset until consumers reattach and begin draining. Bake a baseline re-sample into the promotion runbook so the first post-failover forecast is not computed against a stale counter.

Authoritative references #

• PostgreSQL: pg_stat_wal (cumulative WAL statistics) — wal_bytes, wal_fpi, and wal_buffers_full column definitions used for the rate calculation.
• PostgreSQL: WAL configuration (max_wal_size, wal_segment_size) — checkpoint targets and segment sizing.
• PostgreSQL: max_slot_wal_keep_size — the hard retention cap that bounds the runway.
• PostgreSQL: WAL functions (pg_ls_waldir, pg_current_wal_lsn) — segment inventory and LSN arithmetic.

Related #

• Preventing WAL bloat from inactive slots — the single-slot failure signature this capacity view deliberately excludes.
• WAL stream mechanics — the parent guide to how restart_lsn pins the segments this rate consumes.
• Monitoring and alerting — exporting the generation rate and runway gauges with production alert thresholds.