PostgreSQL Vacuum and Autovacuum Tuning

Understanding VACUUM and Autovacuum

PostgreSQL uses MVCC (Multi-Version Concurrency Control) — updates and deletes don't remove old row versions immediately. VACUUM reclaims this dead space. Without regular vacuuming, tables bloat, queries slow down, and eventually you risk transaction ID wraparound — a catastrophic event that forces the database into read-only mode.

What VACUUM Does

• Marks dead tuples (deleted/updated rows) as reusable space
• Updates the visibility map for index-only scans
• Freezes old transaction IDs to prevent wraparound
• VACUUM FULL — rewrites the entire table to reclaim disk space (requires exclusive lock)

Autovacuum Configuration

# postgresql.conf — recommended settings
autovacuum = on
autovacuum_max_workers = 5                    # Default 3, increase for busy DBs
autovacuum_naptime = 30s                      # Check every 30 seconds
autovacuum_vacuum_threshold = 50              # Minimum dead tuples before vacuum
autovacuum_vacuum_scale_factor = 0.05         # Default 0.2 (20%), reduce to 5%
autovacuum_analyze_threshold = 50
autovacuum_analyze_scale_factor = 0.02        # Default 0.1, reduce to 2%
autovacuum_vacuum_cost_delay = 2ms            # Default 2ms (reduce for faster vacuum)
autovacuum_vacuum_cost_limit = 1000           # Default 200 (increase for faster vacuum)

Per-Table Tuning for Hot Tables

-- For tables with heavy UPDATE/DELETE traffic
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.01,      -- Vacuum after 1% dead tuples
  autovacuum_vacuum_threshold = 100,
  autovacuum_analyze_scale_factor = 0.005
);

Monitoring Vacuum Activity

-- Check dead tuples and last vacuum time
SELECT schemaname, relname, n_dead_tup, n_live_tup,
       round(n_dead_tup::numeric / GREATEST(n_live_tup, 1) * 100, 2) as dead_pct,
       last_vacuum, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

Preventing Transaction ID Wraparound

-- Monitor oldest unfrozen transaction
SELECT datname, age(datfrozenxid) as xid_age,
       current_setting('autovacuum_freeze_max_age')::int as freeze_max_age
FROM pg_database
ORDER BY xid_age DESC;

If xid_age approaches autovacuum_freeze_max_age (default 200 million), PostgreSQL forces aggressive anti-wraparound vacuums that can impact performance.

Eazy SaaS Tip: Reduce autovacuum_vacuum_scale_factor to 0.05 and increase autovacuum_vacuum_cost_limit to 1000. These two changes dramatically improve autovacuum's ability to keep up with busy tables.