What is this issue?
Autovacuum is PostgreSQL's background process that automatically:
- Cleans up dead tuples to reclaim space
- Updates table statistics for the query planner
- Freezes old transaction IDs to prevent wraparound
When disabled on a table via autovacuum_enabled = false, this table
requires manual VACUUM and ANALYZE operations.
Valid reasons to disable autovacuum:
- Append-only tables with no updates/deletes
- Temporary staging tables that are truncated regularly
- Tables with highly controlled maintenance windows
Why it matters
Table Bloat
Dead tuples accumulate, wasting disk space
Stale Statistics
Query plans become suboptimal without ANALYZE
XID Wraparound Risk
Transaction IDs aren't frozen, risking database shutdown
Manual Maintenance
Requires scheduled manual VACUUM operations
How PG Pilot detects it
```sql SELECT schemaname, relname, reloptions FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE relkind = 'r' AND array_to_string(reloptions, '') LIKE '%autovacuum_enabled=false%'; ```
How to fix it
Verify autovacuum is intentionally disabled
Check who disabled it and why:
SELECT relname, reloptions
FROM pg_class
WHERE relname = 'your_table';Look for documentation explaining the decision.
Consider re-enabling autovacuum
If there's no good reason, re-enable it:
ALTER TABLE your_table SET (autovacuum_enabled = true);Schedule manual maintenance
If autovacuum must stay disabled, schedule regular maintenance:
-- Run in a scheduled job
VACUUM ANALYZE your_table;Also schedule periodic freeze to prevent XID wraparound:
VACUUM FREEZE your_table;Monitor the table
Set up monitoring for:
- Dead tuple count
- Table size growth
- XID age
PG Pilot tracks these automatically.
Prevention
- Document why autovacuum is disabled for each table
- Set up scheduled maintenance jobs
- Monitor disabled tables more closely
- Consider per-table autovacuum tuning instead of disabling