WAL Sizing, Checkpoint Tuning, pg_wal Growth, and Production Alerting

Learning outcomes

A disk alert reports that PGDATA/pg_wal has grown far beyond max_wal_size. An operator proposes deleting the oldest files manually. That response confuses a checkpoint sizing target with a retention contract. PostgreSQL may legitimately retain WAL for crash recovery, archiving, replication, replication slots, or other recovery requirements. The correct workflow is to identify the retaining mechanism first.

1. max_wal_size is not a hard directory quota

max_wal_size influences when automatic checkpoints are requested because of WAL growth. PostgreSQL documents it as a soft limit. WAL can exceed it under heavy load, failed archiving, wal_keep_size, or replication-slot retention. min_wal_size is a lower recycling reserve: below it, old segments are retained for reuse at checkpoints rather than removed.

SELECT name, setting, unit, context, sourceFROM pg_settingsWHERE name IN (  'max_wal_size','min_wal_size','checkpoint_timeout','checkpoint_completion_target',  'wal_keep_size','max_slot_wal_keep_size','archive_mode','archive_command','archive_library')ORDER BY name;

Increasing max_wal_size can reduce checkpoint pressure for a write-heavy workload, but it can increase the amount of WAL that crash recovery must potentially replay and increase storage requirements. A value is therefore a capacity/recovery tradeoff, not an isolated tuning number.

A useful first-order capacity model is rate based: measure WAL bytes generated over a representative interval, measure how much WAL is retained beyond the active checkpoint need, and compare the resulting directory growth with filesystem free space. If free space is 40 GiB but the directory is growing at 4 GiB/hour because an abandoned slot is retaining WAL, the meaningful operational quantity is roughly ten hours to exhaustion—not whether the directory has crossed some generic “large” threshold. Use your own measured rate; do not copy this example as an alert value.

Long-running recovery and backup workflows can also require WAL to remain available somewhere. The primary pg_wal directory, an archive, a standby, and a replication slot have different retention roles. Capacity planning must state which recovery path is being protected and where its required WAL is expected to live.

2. Measure pg_wal safely

On PostgreSQL 18, pg_ls_waldir() returns file names, sizes, and modification times. It is restricted to superusers and roles with pg_monitor privileges by default. Prefer this supported interface or ordinary filesystem monitoring; do not grant generic server-file-read privileges merely to make a dashboard work.

SELECT  count(*) AS wal_files,  pg_size_pretty(sum(size)) AS wal_directory_size,  min(modification) AS oldest_file_mtime,  max(modification) AS newest_file_mtimeFROM pg_ls_waldir();

The directory size is an instantaneous capacity measurement. It does not identify why the files are retained.

3. Check archive health before blaming checkpoints

If continuous archiving is enabled, completed WAL segments cannot be recycled normally until archiving succeeds. A failing archive command/library can therefore cause local WAL accumulation.

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

A rising failed_count plus stalled last_archived_time is direct evidence to investigate the archive destination, command/library, permissions, network/object storage, or capacity. Do not “solve” it by deleting local WAL and silently breaking the archive chain.

4. Replication slots can deliberately retain WAL

A replication slot records how far a consumer still needs WAL. PostgreSQL therefore retains required segments so the consumer can resume. An abandoned or stalled slot can hold large amounts of WAL. PostgreSQL 18 exposes the oldest required location as restart_lsn, a high-level wal_status, and safe_wal_size when a finite max_slot_wal_keep_size applies.

SELECT  slot_name, slot_type, database, active, active_pid,  restart_lsn, confirmed_flush_lsn, wal_status, safe_wal_size,  inactive_sinceFROM pg_replication_slotsORDER BY slot_name;

SELECT  slot_name,  active,  restart_lsn,  CASE    WHEN restart_lsn IS NULL THEN NULL    ELSE pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn))  END AS approx_wal_distance,  wal_status,  safe_wal_sizeFROM pg_replication_slotsORDER BY slot_name;

The LSN distance is not identical to current directory bytes because segment recycling, archives, checkpoints, and file boundaries also matter. It is still useful evidence for identifying a retaining consumer.

5. Streaming replication has send/write/flush/replay positions

On a primary with physical or logical streaming consumers, pg_stat_replication separates what PostgreSQL has sent from what each standby has written, flushed, and replayed. These are different stages and support different durability/lag interpretations.

SELECT  application_name, state, sync_state,  sent_lsn, write_lsn, flush_lsn, replay_lsn,  write_lag, flush_lag, replay_lagFROM pg_stat_replicationORDER BY application_name;

An empty result simply means this primary currently has no streaming replication connections. Do not fabricate lag numbers for a single-node lab.

6. A practical WAL-capacity dashboard

Capture a small set of measurements at a regular interval and derive rates from deltas. The sample below deliberately reports facts rather than embedding universal alert thresholds.

SELECT now() AS observed_at,       pg_current_wal_lsn() AS current_write_lsn,       pg_current_wal_flush_lsn() AS current_flush_lsn;SELECT wal_records, wal_fpi, wal_bytes, wal_buffers_full, stats_resetFROM pg_stat_wal;SELECT num_timed, num_requested, num_done,       write_time, sync_time, buffers_written, stats_resetFROM pg_stat_checkpointer;SELECT count(*) AS wal_files,       sum(size) AS wal_dir_bytesFROM pg_ls_waldir();

At the monitoring system, store successive snapshots and calculate WAL bytes per second, directory growth rate, checkpoint rate, archive failure rate, and slot-retention distance. Pair those with filesystem free bytes and the operational time needed to fix the retaining consumer.

SELECT  s.slot_name,  s.active,  s.wal_status,  s.restart_lsn,  CASE WHEN s.restart_lsn IS NULL THEN NULL       ELSE pg_wal_lsn_diff(pg_current_wal_lsn(), s.restart_lsn) END AS retained_distance_bytes,  s.safe_wal_size,  s.inactive_sinceFROM pg_replication_slots AS sORDER BY retained_distance_bytes DESC NULLS LAST;

Attach this evidence, the WAL-directory trend, archiver status, and filesystem time-to-full to the incident. That turns “WAL is large” into a concrete retaining-consumer diagnosis.

7. Wrong approach: “max_wal_size says 1 GB, alert at 1.1 GB”

A static alert just above max_wal_size creates false positives because exceeding that value is not itself an error. Conversely, a cluster can be in real danger below any arbitrary fixed number if the WAL volume is growing faster than remaining disk can absorb.

8. Retention decision tree

9. Bridge to backup engineering

Keeping WAL on the primary is not a backup policy. Chapter 13 will combine logical dumps, physical base backups, continuous archiving, manifests, restore commands, recovery targets, and restore verification. The key transition is from “Do we have files?” to “Can we restore the required business state within the target RPO and RTO?”

Authoritative references

Durability behavior depends on PostgreSQL version, storage semantics, configuration source, replication topology, and the exact failure mode. The references below are the primary source for the mechanisms used in this lesson.

• PostgreSQL 18 — WAL Configuration
• PostgreSQL 18 — Streaming Replication / WAL Retention
• PostgreSQL 18 — Replication Configuration
• PostgreSQL 18 — pg_replication_slots
• PostgreSQL 18 — Monitoring Statistics
• PostgreSQL 18 — System Administration Functions / pg_ls_waldir
• PostgreSQL 18 — Continuous Archiving and PITR