Autovacuum Disabled or Stalled

Overview

Autovacuum is Postgres's background maintenance daemon. It reclaims dead tuples, updates statistics, and prevents transaction ID wraparound. It should be running continuously for any active table. When autovacuum is disabled (via reloptions) or hasn't run in days despite accumulating dead tuples, the table is headed toward bloat and eventually XID wraparound—a condition where Postgres freezes all writes until an emergency VACUUM FREEZE completes.

The most common reason autovacuum stalls is a long-running transaction blocking its xmin horizon. The second most common cause is autovacuum_vacuum_cost_delay being too aggressive, throttling autovacuum below the write rate. insightral surfaces both cases.

Detection SQL

Run this query with a read-only role. No writes, no locks.

-- Tables with autovacuum disabled OR not vacuumed in more than 3 days with active writes
SELECT
  c.relname                   AS table_name,
  c.reloptions,
  s.last_autovacuum,
  s.last_manual_vacuum,
  s.n_dead_tup,
  s.n_mod_since_analyze,
  now() - s.last_autovacuum   AS since_last_autovacuum
FROM pg_stat_user_tables s
JOIN pg_class c ON c.oid = s.relid
WHERE
  (c.reloptions::text LIKE '%autovacuum_enabled=false%')
  OR (
    s.n_dead_tup > 5000
    AND (s.last_autovacuum IS NULL OR now() - s.last_autovacuum > interval '3 days')
    AND (s.last_manual_vacuum IS NULL OR now() - s.last_manual_vacuum > interval '3 days')
  )
ORDER BY s.n_dead_tup DESC;

Fix SQL

Replace placeholders in <angle brackets> with values from the detection output before running.

-- Re-enable autovacuum if it was explicitly disabled
ALTER TABLE <table_name> RESET (autovacuum_enabled);

-- If autovacuum is stalled, run manually to unblock
VACUUM (ANALYZE) <table_name>;

-- Check for long-running transactions blocking autovacuum's xmin
SELECT pid, xact_start, state, query
FROM pg_stat_activity
WHERE xact_start < now() - interval '10 minutes';

Worked example

Common false positives

• Archive tables intentionally set to autovacuum_enabled=false because they are never updated — this is a valid pattern for immutable historical data.
• A table with zero writes since the last vacuum (n_mod_since_analyze = 0) genuinely doesn't need vacuuming even if time has passed.