Vacuum Tuning in Postgres: A Deep Dive

Postgres vacuum is the source of countless ‘our database got slow’ incidents. The tuning is mechanical when you understand the four settings.

Why vacuum matters

Postgres MVCC: dead tuples accumulate; vacuum reclaims them; without it, tables bloat and queries slow.

Default autovacuum is conservative; large/active tables need tuning.

Four key settings

• autovacuum_vacuum_scale_factor, trigger threshold.
• autovacuum_max_workers, concurrency.
• maintenance_work_mem, per-vacuum memory.
• autovacuum_vacuum_cost_delay, throttling.

Per-table overrides

Per-table: ALTER TABLE x SET (autovacuum_vacuum_scale_factor = 0.05) for hot tables.

Default 0.2 = vacuum when 20% dead; for hot tables, 0.05 keeps bloat down.

Symptoms of bad tuning

Bloat increasing; query planner stats stale; long autovacuum runs that never complete.

Each is a tuning signal; address before they become incidents.

Antipatterns

• Default autovacuum at scale. Bloat.
• Disabling autovacuum. Catastrophe within weeks.
• Manual VACUUM as substitute. Fragile; error-prone.

What to do this week

Three moves. (1) Apply this pattern to your most-loaded table. (2) Measure query latency / write throughput before/after. (3) Document the win and the constraint so the next refactor inherits the knowledge.