PostgreSQL autovacuum lag: connect dead tuples to query slowdown

The symptoms

• •pg_stat_all_tables.n_dead_tup rises continuously for a table while n_live_tup stays flat or grows slowly, indicating cleanup is not matching write rate.
• •Queries that previously used an index switch to sequential scans, or pick a noticeably higher cost than expected, after a heavy UPDATE/DELETE batch.
• •Table or index physical size (pg_relation_size) keeps growing even after DELETE, and the next VACUUM reclaims far less than the dead-tuple count suggests.
• •Last autovacuum timestamp in pg_stat_all_tables is stale relative to the workload (hours or days behind recent DML).
• •Wraparound warnings or anti-wraparound autovacuum entries appear in logs while normal autovacuum is not making progress on hot tables.
• •Application-side complaints about slow queries on tables that have not changed schema, coinciding with a recent bulk UPDATE/DELETE or replication catch-up.

Likely causes

• •autovacuum workers are CPU-, IO-, or slot-constrained relative to the table's dead-tuple generation rate, so the queue cannot drain.
• •Per-table cost delay or autovacuum_vacuum_cost_limit is configured so conservatively that a single pass over a large hot table takes longer than the interval between DML peaks.
• •Long-running transactions, idle-in-transaction sessions, or hot standby feedback is holding an oldest xmin horizon, preventing vacuum from removing recently-dead tuples.
• •Table-level autovacuum settings (autovacuum_vacuum_threshold, scale factor, freeze_max_age) are inherited from defaults that are too generous for the workload shape.
• •Wraparound-driven anti-wraparound autovacuum is preempting regular vacuum workers on system catalogs, leaving user tables starved.
• •Statistics targets, fillfactor, or index bloat cause planner cost estimates to drift; this is downstream of vacuum lag rather than a separate bug.

First ten minutes

1. 01Snapshot n_dead_tup, n_live_tup, n_mod_since_analyze, last_autovacuum, last_autoanalyze, and n_ins_since_vacuum for the suspect table from pg_stat_all_tables and record deltas over a short window to confirm the counter is actually moving.
2. 02Inspect pg_stat_activity for any transaction whose xact_start or state change is older than the workload's typical statement time; long or idle-in-transaction sessions pin xmin and block tuple removal.
3. 03Read the PostgreSQL log (or pg_log_backend_memory_contexts if enabled) for autovacuum worker entries, noting which tables were processed, the elapsed time, and whether anti-wraparound workers are active.
4. 04Cross-check pg_relation_size and pg_total_relation_size for the table and its indexes against the live-tuple count to quantify physical bloat versus logical dead tuples.
5. 05Compare last_autovacuum and last_autoanalyze timestamps to the most recent peak DML window to see whether cleanup is hours or just minutes behind.
6. 06Decide whether the evidence points to capacity (workers/cost limits), horizon (long transactions), or workload shape (per-table threshold) before considering a GUC change.

Evidence to collect

• •Two or three pg_stat_all_tables snapshots for the suspect table, with wall-clock timestamps, so n_dead_tup and n_mod_since_analyze deltas are observable.
• •The autovacuum_max_workers value and a count of currently active autovacuum workers from pg_stat_activity filtered by backend_type.
• •List of active transactions with xact_start and state from pg_stat_activity, especially rows in 'idle in transaction' for longer than a few minutes.
• •PostgreSQL log lines for autovacuum worker starts and completions, including table name, removed tuple counts, and elapsed duration.
• •Effective autovacuum settings for the database and for the specific table (via pg_class.reloptions and pg_db_role_setting), since ALTER TABLE ... SET can override defaults silently.
• •EXPLAIN (ANALYZE, BUFFERS) output for a representative slow query before any tuning, to establish a baseline plan and buffer profile.

Where to look

• •pg_stat_all_tables: the boundary between the planner's view of the table and the physical storage; dead-tuple counters and last_autovacuum timestamps live here.
• •pg_stat_activity: the boundary where session state (xact_start, state, query) can hold back the oldest xmin and silently block cleanup.
• •PostgreSQL log directory (current_logfiles, log_autovacuum_min_duration): the boundary where autovacuum decisions, durations, and anti-wraparound activity are recorded.
• •pg_class and pg_db_role_setting: the boundary where per-table autovacuum storage parameters and database-role defaults are materialized; look here before assuming a global default.
• •pg_stat_progress_vacuum: the boundary where an in-flight autovacuum worker's heap_blks_total, heap_blks_scanned, and phase are visible in near real time.

Diagnostic steps

1. 01Read pg_stat_all_tables for the suspect table and confirm n_dead_tup is growing while n_mod_since_analyze is also rising, separating "lag" from "just-modified".
2. 02Compute a simple rate: (n_dead_tup_now - n_dead_tup_prev) / time_between_snapshots, and compare it to the dead-tuple removal rate implied by the last autovacuum entry's count and duration.
3. 03Query pg_stat_activity for sessions in 'idle in transaction' or 'idle in transaction (aborted)' older than the workload's longest normal transaction; each one is a candidate horizon blocker.
4. 04Check pg_stat_progress_vacuum to see whether a worker is currently scanning the table and how far it has progressed; a stuck phase here suggests a different bug than no-worker-at-all.
5. 05Inspect pg_class.reloptions and pg_db_role_setting for the database to confirm whether autovacuum_vacuum_scale_factor, autovacuum_vacuum_threshold, or vacuum_cost_delay have been overridden on the table.
6. 06Re-run EXPLAIN on a representative slow query with (ANALYZE, BUFFERS) and compare the chosen plan and row estimates against an earlier good plan to confirm the regression is planner-related rather than IO-bound.
7. 07Decide among three buckets: capacity (workers or cost limits too low), horizon (long transactions or replication slots pinning tuples), or threshold (per-table scale factor too coarse for this workload) before changing settings.

Common mistakes

• •Lowering autovacuum_vacuum_cost_delay globally without measuring current cost accumulation, which can starve foreground queries of IO bandwidth and replace one slowdown with another.
• •Assuming a high n_dead_tup value alone means vacuum is broken, when an idle-in-transaction session is actually pinning the tuples that vacuum is correctly refusing to remove.
• •Running a manual VACUUM FULL during peak traffic to "fix" bloat, which takes an ACCESS EXCLUSIVE lock and blocks reads and writes on the table.
• •Increasing autovacuum_max_workers without checking autovacuum_vacuum_cost_limit, since each additional worker shares the same cost budget and can collectively throttle to no net gain.
• •Rewriting the application to avoid UPDATEs as a primary response, when a per-table scale factor adjustment plus targeted manual VACUUM (ANALYZE) would address the planner drift without code changes.

Safe fixes

• •If horizon is the cause, terminate or roll back the long or idle-in-transaction sessions only after confirming their owners and impact; each rollback frees xmin and lets the next autovacuum remove the dead tuples.
• •If threshold is the cause, set per-table autovacuum storage parameters on the hot table only, lowering autovacuum_vacuum_scale_factor (for example, toward 0.02) and/or autovacuum_vacuum_threshold to fire more often, and verify the change with pg_class.reloptions.
• •If capacity is the cause, raise autovacuum_vacuum_cost_limit modestly and keep autovacuum_vacuum_cost_delay unchanged, then re-measure dead-tuple removal rate over a full pass before considering further increases.
• •Run a manual VACUUM (no FULL) on the affected table during a low-traffic window to clear the existing backlog without taking an ACCESS EXCLUSIVE lock, and follow with ANALYZE to refresh planner statistics.
• •Reduce the work each vacuum pass must do by tuning fillfactor for tables with heavy UPDATE patterns so HOT updates can elide dead tuples, then verify HOT update ratios via pg_stat_all_tables.
• •Address planner regression only after statistics are current; if a query still misplans, consider plan-level fixes (indexes, statistics targets) rather than further vacuum tuning.

Prove the fix

1. 01pg_stat_all_tables.last_autovacuum for the affected table advances within the expected per-pass interval, and n_dead_tup trends downward across two consecutive snapshots taken after a workload peak.
2. 02The PostgreSQL log shows autovacuum worker entries for the table with non-zero dead-tuple removal counts and durations consistent with the new cost settings.
3. 03EXPLAIN (ANALYZE, BUFFERS) for the previously slow query returns to its earlier plan shape and buffer profile, with row estimates within the workload's normal estimate-vs-actual range.
4. 04pg_stat_activity shows no sessions in 'idle in transaction' older than the documented application timeout during a representative observation window.
5. 05pg_relation_size of the table and its indexes stabilizes or decreases across a full DML cycle, indicating that cleanup is reclaiming space at roughly the rate it is generated.

Prevention and next steps

• •Capture periodic pg_stat_all_tables snapshots into monitoring, with alerts when n_dead_tup / n_live_tup exceeds a workload-specific ratio or when last_autovacuum age exceeds a documented threshold.
• •Track pg_stat_activity counts of sessions in 'idle in transaction' older than an application-defined timeout, and alert before they accumulate enough to pin xmin across the cluster.
• •Review per-table autovacuum storage parameters whenever a table's DML mix changes (for example, a new bulk UPDATE job), and adjust scale factor and threshold based on measured dead-tuple rate rather than global defaults.
• •Cap application-side transaction duration in code and connection poolers so that no single transaction can hold a backstop horizon long enough to stall cleanup.
• •Keep autovacuum_max_workers and autovacuum_vacuum_cost_limit tuned together, revisiting them after storage changes or major version upgrades rather than leaving them at installer defaults.

Safe commands and checks

SELECT relname, n_live_tup, n_dead_tup, n_mod_since_analyze, last_autovacuum, last_autoanalyze FROM pg_stat_all_tables WHERE relname = '<table_name>';
SELECT pid, datname, usename, state, xact_start, query FROM pg_stat_activity WHERE state LIKE '%idle in transaction%' ORDER BY xact_start;
SELECT pid, datname, relid::regclass AS table, phase, heap_blks_total, heap_blks_scanned FROM pg_stat_progress_vacuum;
SELECT count(*) FROM pg_stat_activity WHERE backend_type = 'autovacuum worker';
SELECT relname, reloptions FROM pg_class WHERE relname = '<table_name>';
SELECT pg_relation_size('<table_name>'), pg_total_relation_size('<table_name>');
VACUUM (VERBOSE, ANALYZE) <table_name>; -- run only in a low-traffic window and only when evidence shows a real backlog.
EXPLAIN (ANALYZE, BUFFERS) <representative_query>; -- baseline and post-fix comparison query, not a destructive command.