autovacuum_vacuum_scale_factor

For DBAs and operators

Formula (PostgreSQL 18): n_dead_tup > min(autovacuum_vacuum_threshold + scale_factor × reltuples, autovacuum_vacuum_max_threshold). The new cap (100 million dead tuples by default) stops the very worst cases, but 100M dead tuples is still a lot of bloat; per-table tuning remains the main tool.

Sizing guidance

• Small/medium tables: keep 0.2 (or lower globally to 0.05–0.1 if you see bloat everywhere).
• Tables over ~10M rows: 0.01–0.02, or scale factor 0 with an absolute threshold.
• Lowering the trigger means more frequent, shorter vacuums; give autovacuum enough cost budget so it can keep up.

ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.01);
ALTER TABLE orders RESET (autovacuum_vacuum_scale_factor);
SELECT relname, reloptions FROM pg_class WHERE reloptions IS NOT NULL;

-- who is close to the line?
SELECT s.relname, s.n_dead_tup, c.reltuples::bigint,
       50 + 0.2 * greatest(c.reltuples, 0) AS vac_line, s.last_autovacuum
  FROM pg_stat_user_tables s JOIN pg_class c ON c.oid = s.relid
 ORDER BY s.n_dead_tup DESC LIMIT 20;

Common mistake: setting 0.01 globally on a cluster with thousands of small tables; it creates a storm of tiny vacuums. Tune the big tables instead.

How to change it

Change in postgresql.conf or ALTER SYSTEM, then run SELECT pg_reload_conf().