Tables not being autovacuumed — finding the gaps

Some tables have accumulated significant dead tuples or stale statistics but autovacuum has not run on them — they may have been excluded, throttled out, or have per-table settings that prevent timely cleanup.

Diagnose it

-- Tables with dead tuples but no recent autovacuum:
SELECT schemaname,
       relname,
       n_dead_tup,
       n_mod_since_analyze,
       n_ins_since_vacuum,
       last_autovacuum,
       last_autoanalyze,
       autovacuum_count,
       autoanalyze_count
FROM pg_stat_all_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema', 'pg_toast')
  AND (last_autovacuum IS NULL
       OR last_autovacuum  1000
ORDER BY n_dead_tup DESC
LIMIT 20;

Why it happens

Possible reasons autovacuum skips a table: (1) the table has autovacuum_enabled = false
set via storage parameters; (2) the table is too small to cross the threshold
(autovacuum_vacuum_threshold + scale_factor * n_live_tup); (3) all autovacuum
worker slots are busy with other tables; (4) the table belongs to a schema not visible
to autovacuum (extremely rare); (5) the cost-delay throttle is so aggressive that the worker
never finishes.

How to fix it

-- Check per-table settings:
SELECT relname, reloptions
FROM pg_class
WHERE relname = 'your_table_name';

-- Check if autovacuum is disabled:
SELECT relname, reloptions
FROM pg_class
WHERE reloptions::text LIKE '%autovacuum_enabled=false%';

-- Re-enable if needed:
ALTER TABLE tablename RESET (autovacuum_enabled);

Prevent it next time

Never set autovacuum_enabled = false on a user table in production unless you
have a fully managed VACUUM schedule replacing it. Monitor last_autovacuum across
all tables in a scheduled query; alert on any table that has not been vacuumed in 24 hours and
has more than a threshold of dead tuples.