What happens when autovacuum is disabled on a table?

Autovacuum disabled: bloat and wraparound left to accumulate

Note: this works and blocks nothing, but a performance regression is likely.

What happens

With autovacuum_enabled = false, dead tuples accumulate unchecked (bloat, degrading scans) and the table still gets aggressive wraparound vacuums eventually, at the worst possible moment. Tuning autovacuum beats disabling it in almost every case.

Why it is dangerous on a populated table

Nothing blocks, but the cost scales with the table and shows up as a slower query plan or a longer maintenance window rather than an outage.

Fires on

ALTER TABLE orders SET (autovacuum_enabled = false);

The safe pattern

Tune autovacuum per table instead of disabling it: a lower scale factor and cost delay make it run more often and gentler. Wraparound vacuums will come regardless, the only choice is whether routine vacuums kept the table small first.

SET lock_timeout = '5s';
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.05,
  autovacuum_vacuum_cost_delay = 2
);

Fixtures

The rule ships with these files and the test suite runs them on every change: the first set must fire, the second must stay silent.

Fires (1)

autovacuum off

ALTER TABLE orders SET (autovacuum_enabled = false);

Stays silent (1)

autovacuum tuned

ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.01);

How to check locally

Catch this before it ships

This rule runs in the hosted service on Startup and above: add --remote with a team token, or use the GitHub Action. No install, nothing leaves your machine:

npx bolvrk check migration.sql --remote

Browse all 135 rules

← BV037 BV039 →