What is this issue?
Tables accumulate dead tuples from UPDATE and DELETE operations. These dead
tuples consume space and slow down queries until VACUUM cleans them up.
A table needs vacuum when:
- Dead tuple ratio exceeds 10%
- Last vacuum was more than 7 days ago
- Dead tuple count is significantly high
While autovacuum should handle this automatically, sometimes it falls behind
or is misconfigured.
Why it matters
Disk Space
Dead tuples consume storage that could be reclaimed
Query Performance
Sequential scans must read through dead tuples
Index Efficiency
Indexes may point to dead tuples
Statistics
Table statistics may be stale without recent ANALYZE
How PG Pilot detects it
```sql SELECT schemaname, relname, n_live_tup, n_dead_tup, round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct, last_vacuum, last_autovacuum, last_analyze FROM pg_stat_user_tables WHERE n_dead_tup > 1000 OR last_vacuum < now() - interval '7 days' ORDER BY n_dead_tup DESC; ```
How to fix it
Run VACUUM ANALYZE
Clean up dead tuples and update statistics:
VACUUM ANALYZE your_table;Check autovacuum status
Verify autovacuum is enabled and running:
SHOW autovacuum;
-- Check recent autovacuum activity
SELECT relname, last_autovacuum, autovacuum_count
FROM pg_stat_user_tables
ORDER BY last_autovacuum DESC NULLS LAST;
Tune autovacuum thresholds
For high-write tables, lower the thresholds:
ALTER TABLE your_table SET (
autovacuum_vacuum_threshold = 50,
autovacuum_vacuum_scale_factor = 0.02
);Prevention
- Ensure autovacuum is enabled globally
- Tune per-table autovacuum settings for high-write tables
- Monitor dead tuple ratios with PG Pilot
- Don't disable autovacuum without a maintenance plan