moflab notes

Postgres autovacuum, translated from the manual

Nov 2026 · 6 min read

Autovacuum is not one setting but a small bundle of thresholds that decide when a table gets cleaned. The two that matter in practice are autovacuum_vacuum_scale_factor (a fraction of the table) and autovacuum_vacuum_threshold (a flat number of dead rows). They add up.

On a table of twenty million rows the default scale factor of 0.2 means four million dead tuples before anything happens. On an insert-heavy table that is weeks of bloat and a plan that stops using the right index.

The fix is not to lower the global defaults. Set it per table, where you know the write pattern:

ALTER TABLE events SET (autovacuum_vacuum_scale_factor = 0.02, autovacuum_vacuum_threshold = 5000);

Each autovacuum worker also has an aggressive mode that kicks in when the table has not been vacuumed for a long time; if you see long vacuums at odd hours, that is usually it, not the thresholds.

Worth measuring before and after: pg_stat_user_tables has n_dead_tup, last_autovacuum and autovacuum_count. A table whose count stays at zero for a week is a table that is not being cleaned at all.