What is this issue?
Autovacuum is PostgreSQL's essential background maintenance process that:
- Removes dead tuples from tables
- Updates planner statistics (ANALYZE)
- Freezes old transaction IDs to prevent wraparound
With autovacuum disabled:
- Tables bloat continuously
- Query plans become suboptimal
- XID age increases towards wraparound
- Database will eventually shut down to prevent corruption
There is almost never a valid reason to disable autovacuum globally.
Why it matters
Database Shutdown
XID wraparound will force PostgreSQL to stop accepting writes
Severe Bloat
Tables and indexes grow without bound
Poor Performance
Stale statistics cause bad query plans
Manual Burden
Requires 24/7 manual maintenance
How PG Pilot detects it
```sql SELECT name, setting FROM pg_settings WHERE name = 'autovacuum'; ```
How to fix it
Enable autovacuum immediately
ALTER SYSTEM SET autovacuum = on;
SELECT pg_reload_conf();Verify it's running
Check for autovacuum worker processes:
SELECT * FROM pg_stat_activity
WHERE backend_type = 'autovacuum worker';Run manual vacuum if needed
If autovacuum was off for a long time, tables may need
immediate attention:
-- Find tables needing vacuum
SELECT schemaname, relname, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;
-- Vacuum critical tables
VACUUM ANALYZE your_table;
Check XID age
Verify XID wraparound isn't imminent:
SELECT datname, age(datfrozenxid)
FROM pg_database
ORDER BY age(datfrozenxid) DESC;If age > 1 billion, run VACUUM FREEZE on old tables.
Prevention
- Never disable autovacuum globally
- Use per-table settings for special cases
- Monitor autovacuum activity with PG Pilot
- Alert on autovacuum disabled setting