Autovacuum: What It Does and When It Doesn't

Autovacuum is a launcher process that wakes on a timer, looks at one database's tables, compares each table's estimated dead-tuple count against a threshold, and starts a worker on the ones that qualify. Every clause in that sentence is a limit. The timer is autovacuum_naptime, one minute by default. The threshold is a percentage that was chosen so a small database on modest hardware never falls over. And three workers, the default, is not a number that scales to a schema where two tables are permanently busy.

None of the defaults are wrong. They are defaults for an unknown database on unknown hardware, and Cartwheel is a known database on 16 vCPU and NVMe. The gap between those two situations is the whole of this topic, and closing it costs two settings on each of four tables.

The Trigger Formula

A table qualifies for vacuuming when its estimated dead tuples exceed autovacuum_vacuum_threshold, which is 50, plus autovacuum_vacuum_scale_factor, which is 0.2, times the row count the catalogue believes the table has. Since 18 the result is also capped by autovacuum_vacuum_max_threshold, 100 million by default, which matters only for the very largest tables. The percentage is the part to internalize, because it makes the threshold grow with the table it is supposed to protect.

inventory         50 + 0.20 ×     12,000  =        2,450 dead tuples
orders            50 + 0.20 × 40,000,000  =    8,000,050 dead tuples
delivery_events   50 + 0.20 ×  1.2e9      =  240,000,050 → capped at
                                             100,000,000 (since 18)

The single most consequential default in Postgres is the eight million dead row versions orders accumulates before its first vacuum starts. It is not a bug and it is not a surprise to the people who chose it, since on a table that size vacuuming at 1% would mean vacuuming constantly, but it is a decision, and leaving it at 0.2 means agreeing to carry eight million dead versions as normal operating state. On delivery_events the number would have been 240 million before 18 introduced the cap; the cap brings it down to 100 million, which is better and still enormous.

inventory is the opposite case and the more instructive one. The trigger was never what failed there; everything downstream of it was, which is where most autovacuum investigations should start and almost none do. Its threshold is 2,450 dead tuples, and at Saturday peak checkout produces around 9,000 updates a minute against it, so the table qualifies for vacuuming permanently.

The Insert-Only Path

A table that is only ever appended to produces no dead tuples at all, so under the dead-tuple rule alone it would never be vacuumed — and would therefore never have its visibility map maintained and never have a single row frozen until an emergency arrived. Since 13 there is a second trigger: autovacuum_vacuum_insert_threshold, 1000 rows, plus autovacuum_vacuum_insert_scale_factor, 0.2, times the row count — and since 18 that second term is scaled down by the fraction of the table that is already frozen. That last part is what keeps the number sane on a huge cold table: as more of delivery_events becomes all-frozen, the share of the table the formula counts shrinks with it.

This is the setting that keeps delivery_events healthy without anyone thinking about it. Turning the insert path off on an append-only table is a way of scheduling the twelve-hour run for later. Four million rows a day arrive, the insert threshold fires regularly, the visibility map gets maintained so index-only scans stay possible, and freezing happens continuously in small pieces rather than arriving all at once three years from now.

Cost-Based Throttling

A vacuum worker keeps a running cost as it goes: 1 for a page already in shared buffers, 2 for a page it has to read, and 20 for a page it dirties. When the accumulated cost passes autovacuum_vacuum_cost_limit the worker sleeps for autovacuum_vacuum_cost_delay, which is 2 milliseconds, and then continues. The limit ships as -1, meaning "use vacuum_cost_limit", which is 200. Two hundred cost units is ten dirtied pages. Ten dirtied pages, then sleep.

That arithmetic was calibrated for spinning disks, and on pg-primary's NVMe it throttles vacuum to a small fraction of what the storage would do without noticing. It gets worse when several workers are busy, because the limit is a budget for the whole daemon rather than for each worker: the value is divided proportionally among the running workers, so three concurrent vacuums get roughly 67 each. Raising autovacuum_vacuum_cost_limit is usually the first change that produces a visible improvement, and it is the one people skip because the setting has "cost" in the name and sounds like money. Cartwheel runs 2000 globally, and higher still on inventory.

Three Workers and Many Tables

autovacuum_max_workers is 3. A worker is occupied for the entire duration of one table's vacuum, so while orders and delivery_events are being processed there is one worker left for the other seven tables, and the launcher only starts a round on a given database every autovacuum_naptime. There is no queue you can inspect and no priority you can set: tables that qualify while all workers are busy simply wait for the next round.

Raising the worker count without raising the cost limit changes nothing, because the same total I/O budget is now divided more ways and every individual vacuum gets slower. Raise both or neither. Version 18 made the first half of that easier: autovacuum_worker_slots reserves the process slots at server start, 16 by default, and autovacuum_max_workers can then be adjusted at run time up to that number. Before 18 the same change needed a restart.

Per-Table Settings Are the Real Interface

One global scale factor has to serve a 12,000-row table updated 9,000 times a minute and a 1.2-billion-row table that is only ever appended to. It cannot, and a compromise value between them is worse than either extreme, because it is now wrong for both. Every autovacuum setting that matters can be attached to the table instead, and that is where they belong.

-- small, hot: vacuum early and let it move at disk speed
ALTER TABLE inventory SET (
    autovacuum_vacuum_scale_factor  = 0.01,   -- 170 dead tuples
    autovacuum_vacuum_threshold     = 50,
    autovacuum_vacuum_cost_limit    = 4000,
    autovacuum_analyze_scale_factor = 0.02);

-- large and updated: a percentage is the wrong unit at this size
ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.0,
    autovacuum_vacuum_threshold    = 500000);  -- a flat number

-- huge and append-only: keep the insert path lively, nothing else
ALTER TABLE delivery_events SET (
    autovacuum_vacuum_insert_scale_factor = 0.01);

That is three different tables and three different shapes of answer. inventory gets a scale factor of 1%, which puts its trigger at roughly 170 dead tuples, and a cost limit high enough that the worker finishes the whole 900 MB table before the next wave of updates arrives. orders gets its scale factor set to zero and a flat threshold instead, because at 40 million rows a percentage is simply the wrong unit — half a million dead tuples is a number chosen from what the table can absorb, not from its size. delivery_events gets nothing but a livelier insert trigger, because it has no dead tuples to clean and what it actually needs is steady freezing.

Two of these settings do not behave the way the syntax suggests: a per-table autovacuum_freeze_max_age larger than the cluster-wide setting is ignored, and so is a per-table freeze minimum age larger than half the cluster-wide maximum. Freezing can be made more urgent for one table, never less. Everything else is stored as a relation option, so it survives restarts, appears in pg_class.reloptions, and belongs in the migration that created the table rather than in someone's shell history.

Watching It Work

Nearly every autovacuum question is answered by three views. pg_stat_user_tables carries n_dead_tup, n_live_tup, last_autovacuum and autovacuum_count per table, and since 18 also total_autovacuum_time, which turns "is autovacuum expensive on this table" from a guess into a number. pg_stat_progress_vacuum shows what is running right now. And the log, with log_autovacuum_min_duration lowered from its 10-minute default, records what each completed run actually removed.

A table whose last_autovacuum is a week old while n_dead_tup reads in the millions has already told you the story before you run anything else. The most common autovacuum mistake is a configuration change made against an imagined problem. Read that pair for a week first, then change one setting, so the following week's reading means something.