Postgres Vacuum Performance

Bloat affects perf.

Overview

Vacuum is on the critical path for Postgres query performance. The default autovacuum settings work for moderate workloads but fall behind on high-write tables, where bloat compounds and queries slow without warning.

• Bloat affects performance. Dead tuples consume buffer space and force seq-scans where index-scans should hit. The cost shows up as slow queries.
• Per-table tuning. High-write tables need aggressive autovacuum settings; low-write tables can stay on defaults.
• Naptime and cost-limit knobs. autovacuum_naptime controls how often the daemon wakes; autovacuum_vacuum_cost_limit controls how aggressively it runs once awake.
• Vacuum metrics. Dead-tuple percentage per table is the leading indicator. pg_stat_user_tables exposes the data.

The approach

Three habits keep Postgres vacuum healthy at scale: tune per table, monitor bloat continuously, and run manual vacuum before bulk operations.

• Per-table tuning. High-write tables override the global autovacuum settings. ALTER TABLE ... SET (autovacuum_vacuum_scale_factor = 0.05).
• Monitor bloat. Per-table dead-tuple percentage on the database dashboard. Alert at 20 percent for high-traffic tables.
• Tune the daemon. Lower autovacuum_naptime and raise autovacuum_vacuum_cost_limit on busy databases.
• Manual vacuum before bulk. VACUUM (ANALYZE) before bulk inserts or schema changes that would cause a long autovacuum to fire mid-operation.

Why this compounds

Each tuned table produces ongoing query-performance benefit. The patterns transfer across Postgres deployments and teach the team how the storage layer actually behaves.

• Faster queries. Less bloat means more index-scans where index-scans should happen. p99 query time improves measurably.
• Stability. Aggressive autovacuum prevents transaction-wraparound emergencies that show up as full-database freezes.
• Workload-matched settings. Per-table tuning lets each table run with the right cost. Defaults stop being a one-size-fits-all compromise.
• Year-one investment, year-two habit. The first tuning takes effort. By the fourth table, the team knows the knobs and the right values reflexively.