How high can autovacuum_vacuum_scale_factor go before a table bloats?
Autovacuum scale factor raised so high the table bloats before it runs
Note: this works and blocks nothing, but a performance regression is likely.
What happens
autovacuum_vacuum_scale_factor is the fraction of the table that must change before autovacuum touches it; the server default is 0.2. At 0.5 or above, a table has to accumulate dead rows equal to half its size before cleanup starts, bloat, slower scans, and index bloat pile up in the meantime, and the eventual vacuum is a long one. The same holds for the analyze scale factor and stale planner statistics. Escalates when the live schema shows the table is large or hot.
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_vacuum_scale_factor = 0.8);The safe pattern
Large, busy tables want a LOWER scale factor than the default, not a higher one: small, frequent vacuums are cheap. If the intent was to stop autovacuum interfering with a batch job, use a cost delay or schedule the job, or RESET the option afterwards.
SET lock_timeout = '5s';
ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.05, autovacuum_analyze_scale_factor = 0.02);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)
SET lock_timeout = '5s';
ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.8);Stays silent (3)
SET lock_timeout = '5s';
ALTER TABLE orders SET (fillfactor = 70);SET lock_timeout = '5s';
ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.05, autovacuum_analyze_scale_factor = 0.02);SET lock_timeout = '5s';
ALTER TABLE orders RESET (autovacuum_vacuum_scale_factor);How to check locally
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