1
Diagnostic step 1
Reduce autovacuum concurrency and throttle parameters on primary: lower autovacuum_max_workers, reduce autovacuum_vacuum_cost_limit, and increase autovacuum_vacuum_cost_delay to smooth WAL generation.
FULL AI ANALYSIS
PostgreSQL replica falls behind and reports WAL streaming delay when frequent autovacuum runs on primary
Generated Oct 12, 2026, 10:57:58 PM
Analysis summary
Autovacuum on large, update-heavy tables can produce I/O and WAL spikes if many workers run concurrently or cost parameters allow aggressive work. Standby replay can fall behind when WAL production rate exceeds network or I/O replay capacity. Throttling autovacuum, spacing vacuum operations, and ensuring standby has sufficient I/O and network headroom are key. Additionally, VACUUM behavior on partitioned tables can be tuned per-partition.
Possible causes
Too many autovacuum workers running concurrently causing I/O contention and bursty WAL generation.
90%Autovacuum cost settings allow aggressive WAL-producing work (high cost_limit or low cost_delay) generating WAL faster than standby can apply.
90%Standby I/O subsystem (disk or CPU) cannot keep up with replaying large WAL volumes.
80%Network hiccups or wal_sender/wal_receiver timeouts during heavy WAL bursts leading to disconnections and lag accumulation.
40%Suggested fixes
2
Diagnostic step 2
Configure per-table autovacuum settings on hot partitions (ALTER TABLE ... SET (autovacuum_vacuum_cost_delay = X, autovacuum_vacuum_cost_limit = Y)) to spread out work; schedule off-peak manual VACUUM for very large partitions.
3
Diagnostic step 3
Monitor WAL generation (pg_stat_archiver/pg_stat_wal) and standby replay (pg_stat_replication replay_lag) while making incremental changes; use pgbadger/pg_stat_activity logs for autovacuum durations.
4
Diagnostic step 4
Ensure standby disk I/O is adequate: check IOPS/latency and consider faster storage or scaling standby resources to improve apply throughput.
5
Diagnostic step 5
If needed, add an intermediate WAL receiver/standby or logical replica to offload read traffic; escalate to DBA team for changes that require topology updates or downtime.