Autovacuum Tuning for the SMB: A Field Guide

Autovacuum Tuning for the SMB: A Field Guide

Most small and mid-sized businesses run PostgreSQL for years without touching autovacuum settings, and for the vast majority of tables, that's the correct call. Autovacuum's default configuration is conservative but reasonable, tuned to avoid surprising anyone with runaway resource usage. The mistake teams make isn't under-tuning — it's over-tuning. They spend hours adjusting `autovacuum_vacuum_scale_factor` on tables that see a few hundred writes a day, while the tables actually causing bloat and planner regressions sit untouched.

The practical approach is to identify the small number of tables where default behavior breaks down, tune those specifically, and leave everything else alone.

Why Defaults Fail on Specific Tables, Not the Whole Database

Autovacuum's default thresholds — a base of 50 dead rows plus 20% of table size — work fine when tables grow slowly and updates are evenly distributed. They fail in three predictable scenarios: very large tables where 20% is millions of rows before a vacuum triggers, high-churn tables with constant updates or deletes, and queue-like tables where rows are inserted and deleted rapidly, leaving bloat that never gets reclaimed in time. If a table doesn't fit one of these patterns, tuning it manually adds operational complexity with no measurable benefit.

The Three Tables Worth Tuning

1. The largest transactional table. Once a table crosses tens of millions of rows, the default 20% scale factor means autovacuum waits far too long between runs. Lowering `autovacuum_vacuum_scale_factor` to something like 0.02–0.05 and setting a fixed `autovacuum_vacuum_cost_limit` per table keeps vacuum running more frequently in smaller increments, avoiding both bloat and long, disruptive vacuum runs.

2. The busiest queue or event-log table. Tables used as job queues, session stores, or event buffers typically see a constant insert-delete cycle. Left on defaults, these accumulate dead tuples faster than autovacuum reclaims them, leading to index bloat and degraded query plans. These tables benefit from aggressive settings: low scale factor, low cost delay, and sometimes a dedicated `autovacuum_vacuum_insert_scale_factor` for insert-heavy patterns.

3. Any table with frequent bulk updates or batch deletes. Nightly ETL jobs, batch status updates, or bulk archival deletes create dead-tuple spikes that don't match autovacuum's steady-state assumptions. Manually triggering vacuum after batch jobs, or tuning scale factor down so autovacuum reacts faster, avoids the days-long lag where query performance degrades before autovacuum catches up.

Everything Else Stays on Defaults

Lookup tables, configuration tables, and low-write dimension tables gain nothing from manual tuning. Adjusting per-table settings on tables that see infrequent writes just adds configuration debt — more things to remember, more places for a future migration to silently break. If a table isn't in the "large, high-churn, or bursty" category, leave it alone and spend the time elsewhere: connection pooling, index review, or query plan analysis tend to yield more return per hour invested.

Tools like Querk that flag bloat and dead-tuple ratios per table make it easier to confirm which three (or four, or five) tables actually need attention, rather than guessing. Tuning based on observed bloat, not intuition, keeps the exercise focused and the rest