Introduction

Proper vacuum configuration is essential for PostgreSQL health. This guide explains vacuum mechanics and tuning for high-transaction workloads.

Symptoms

  • Tables growing despite deletions (bloat)
  • Warning: 'database must be vacuumed within X transactions'
  • Slow query performance over time
  • High I/O from autovacuum processes

Step-by-Step Fix

  1. 1.Check table bloat:
  2. 2.```sql
  3. 3.SELECT schemaname, tablename, n_dead_tup, n_live_tup,
  4. 4.round(n_dead_tup * 100.0 / nullif(n_live_tup, 0), 2) as dead_pct
  5. 5.FROM pg_stat_user_tables
  6. 6.WHERE n_dead_tup > 1000
  7. 7.ORDER BY n_dead_tup DESC;
  8. 8.`
  9. 9.Manual vacuum verbose:
  10. 10.```sql
  11. 11.VACUUM (VERBOSE, ANALYZE) large_table;
  12. 12.`
  13. 13.Tune autovacuum settings:
  14. 14.```ini
  15. 15.# postgresql.conf
  16. 16.autovacuum_max_workers = 6
  17. 17.autovacuum_naptime = 10s
  18. 18.autovacuum_vacuum_threshold = 50
  19. 19.autovacuum_vacuum_scale_factor = 0.1
  20. 20.autovacuum_analyze_scale_factor = 0.05
  21. 21.autovacuum_vacuum_cost_delay = 2ms
  22. 22.autovacuum_vacuum_cost_limit = 2000
  23. 23.`