Autovacuum Tuning for the SMB: A Field Guide

Autovacuum Tuning for the SMB: A Field Guide

Most small and mid-sized businesses running PostgreSQL never need to touch autovacuum settings. The defaults exist for a reason, and they work fine for the vast majority of tables in a typical schema. But every database of meaningful age tends to grow a small number of tables that defaults genuinely can't handle well — and those tables are worth identifying early, before bloat and transaction ID wraparound risk become production incidents.

This guide focuses on a simple idea: tune the few tables that need it, and leave everything else alone.

Why Defaults Work for Almost Everything

PostgreSQL's autovacuum defaults (`autovacuum_vacuum_scale_factor` at 0.2, `autovacuum_analyze_scale_factor` at 0.1, and a base threshold of 50 rows) are designed to scale proportionally with table size. For a table with a few thousand or even a few hundred thousand rows, that proportional model keeps vacuum frequency reasonable without any intervention.

The mistake many teams make is tuning autovacuum globally the moment they see bloat on one table. Lowering scale factors system-wide increases vacuum frequency across the entire database, including tables that don't need it, which burns I/O and CPU for no benefit. Global tuning is a blunt instrument; most SMB workloads need a scalpel.

The 3 Tables That Actually Need Manual Tuning

In practice, three categories of tables consistently justify manual autovacuum settings:

1. High-churn queue or status tables. Tables that function as job queues, session trackers, or event buffers — where rows are inserted and then updated or deleted within minutes — accumulate dead tuples far faster than the default scale factor accounts for. A 20% dead-tuple threshold on a million-row queue table means half a million rows of bloat before vacuum even triggers. These tables benefit from a much lower `autovacuum_vacuum_scale_factor` (sometimes near 0) combined with a fixed row-count threshold.

2. Large, slow-growing tables. Ironically, big tables that grow slowly also need attention, but for the opposite reason: the default percentage-based threshold means autovacuum rarely fires because the absolute number of changes needed to hit 20% is enormous. Here, lowering the scale factor and setting a modest fixed threshold ensures vacuum runs often enough to avoid a single massive, disruptive vacuum later. This matters directly for query performance, since bloated indexes on large tables degrade planner estimates and scan efficiency.

3. Tables with frequent bulk updates or batch deletes. Nightly ETL jobs, bulk status flips, or batch archival processes create dead tuples in large bursts rather than steadily. Autovacuum's scale-factor model assumes gradual change, so these tables often need a tighter threshold or even a scheduled manual `VACUUM` tied to the batch job itself.

For all three categories, per-table settings via `ALTER TABLE ... SET (autovacuum_vacuum_scale_factor = ...)` are the right tool — they isolate the behavior change to the table that needs it.

Everything Else: Leave It Alone

Lookup tables, configuration tables, low-write dimension tables, and anything with infrequent updates should stay on defaults indefinitely. Tuning them adds operational complexity — more settings to document, more drift to audit — without measurable benefit. The goal isn't to tune autovacuum broadly; it's to find the handful of tables where default behavior breaks down, fix those spec