PostgreSQL replica falls behind and reports WAL streaming delay when frequent autovacuum runs on primary

Environment

PostgreSQL 13 cluster: primary on cloud VM (8 vCPU, 32GB RAM), standby streaming replication over 1 Gbps link; autovacuum aggressive tuning with autovacuum_max_workers=20 and autovacuum_naptime=10s, several large partitioned tables with heavy UPDATE churn.

Symptoms

Standby replication_lag (pg_stat_replication) occasionally spikes to several minutes during nightly maintenance window; clients hitting the standby see stale data. Primary shows increased wal_sender delay and replica's replay_lag climbs. WAL shipping backpressure messages appear in primary logs during autovacuum bursts.

Observations

Primary disk I/O saturates while multiple autovacuum workers run concurrently: iostat shows >200% utilization (multiple vdevs), and checkpointer/background writer throughput increases. WAL generation rate spikes during autovacuum and update-heavy vacuum passes. Archive_command is synchronous but succeeding. Standby's walreceiver log shows 'server closed the connection' sometimes during peak I/O. autovacuum logs reveal long vacuum cycles on large partitions.

Already attempted

Reduced autovacuum_vacuum_cost_limit and increased cost_delay on primary to throttle autovacuum — replication lag improved but not fully eliminated. Increased wal_sender_timeout on primary. Temporarily paused non-critical autovacuum on biggest tables via ALTER TABLE SET (autovacuum_enabled = false) and manually scheduled throttled vacuums; lag decreased. No schema or logical replication changes made.

Question

How can I prevent autovacuum activity on the primary from causing WAL generation spikes that overwhelm standby replay capacity, while keeping table bloat under control? What configuration and operational changes are recommended?

Post timeline

COMMUNITY RESPONSES

Verified community troubleshooting

0 replies

No answers yet..

Showing 0 of 0 community responses

AI DIAGNOSTICS

Incident analysis

Updated 2d ago• Trouble ID

Most likely 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%

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.

Suggested Fixes

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.

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.

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.