Maintenance

Autovacuum: when tables get vacuumed

Autovacuum vacuums a table once its dead rows pass a threshold that grows with the table size, and analyzes it once enough rows have changed. The defaults suit small tables; big tables usually need their own settings.

In plain words

Autovacuum does not clean a table after every change. It waits until enough dead rows have piled up: by default 50 rows plus 20% of the table. For a 1,000-row table that is 250 dead rows; for a 100-million-row table it is 20 million.

That percentage is why huge tables often look neglected: they collect millions of dead rows before autovacuum even starts, and then the cleanup is long. The fix is to give such tables a smaller percentage, or a fixed number, with per-table settings. Since PostgreSQL 18 there is also a global cap: no table waits for more than 100 million dead rows.

Similar rules decide when to analyze (refresh the planner statistics: 50 rows plus 10% changed) and when to vacuum an insert-only table (1000 rows plus 20% of the not-yet-frozen part inserted).

Autovacuum also works slowly on purpose. It pauses briefly after every bit of work so it does not steal all the disk bandwidth from your queries. Try the sliders: make the table bigger, and see how the waiting and the run time grow.

For DBAs and operators

The three triggers

vacuum threshold  = min(autovacuum_vacuum_max_threshold,
                        autovacuum_vacuum_threshold
                        + autovacuum_vacuum_scale_factor * reltuples)
                    -- compared with n_dead_tup

insert threshold  = autovacuum_vacuum_insert_threshold
                    + autovacuum_vacuum_insert_scale_factor * reltuples
                      * (1 - relallfrozen / relpages)
                    -- compared with n_ins_since_vacuum

analyze threshold = autovacuum_analyze_threshold
                    + autovacuum_analyze_scale_factor * reltuples
                    -- compared with n_mod_since_analyze

Two PostgreSQL 18 changes: autovacuum_vacuum_max_threshold (default 100,000,000, -1 disables the cap) and the insert threshold now only counts the unfrozen share of the table (new column pg_class.relallfrozen), so large insert-mostly tables get vacuumed (and frozen) more often. Insert-triggered vacuums exist since PostgreSQL 13. Partitioned parents are never processed by autovacuum: run ANALYZE on them yourself.

Which tables are due?

SELECT s.relname, s.n_dead_tup, c.reltuples::bigint,
       least(nullif(current_setting('autovacuum_vacuum_max_threshold')::float8, -1),
             current_setting('autovacuum_vacuum_threshold')::int
           + current_setting('autovacuum_vacuum_scale_factor')::float8
             * greatest(c.reltuples, 0))::bigint AS vac_threshold,
       s.n_mod_since_analyze, s.last_autovacuum, s.last_autoanalyze,
       s.autovacuum_count
  FROM pg_stat_user_tables s JOIN pg_class c ON c.oid = s.relid
 ORDER BY s.n_dead_tup DESC LIMIT 20;

(This ignores per-table overrides, see pg_class.reloptions.)

Per-table storage parameters

ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_threshold    = 10000,
  autovacuum_analyze_scale_factor = 0.02,
  autovacuum_vacuum_cost_limit   = 1000,
  log_autovacuum_min_duration    = 0);
ALTER TABLE orders RESET (autovacuum_vacuum_cost_limit);

Almost every autovacuum_* setting exists as a table option (plus toast. variants and autovacuum_enabled). Setting autovacuum_enabled = off stops routine vacuums, but anti-wraparound vacuums still run.

Cost-based throttling

Each page costs points: vacuum_cost_page_hit 1, vacuum_cost_page_miss 2, vacuum_cost_page_dirty 20. After spending autovacuum_vacuum_cost_limit (-1 → vacuum_cost_limit = 200) points, the worker sleeps autovacuum_vacuum_cost_delay (2ms). That caps the default at roughly 100,000 points per second, shared by all workers: about 800 MB/s of pages found in shared buffers, 400 MB/s of pages it has to read in, but only ~40 MB/s of pages it has to dirty. On modern storage raising the limit to 1000–2000 is common. Manual VACUUM is unthrottled by default (vacuum_cost_delay = 0).

Typical support findings

A 500 GB table with the default 0.2 scale factor: autovacuum starts only after ~20% of the rows are dead (or 100 million rows, the PostgreSQL 18 cap, if that is lower), then runs for hours. Lower the scale factor per table.
n_dead_tup stays high right after a vacuum: something holds back the xmin horizon (see VACUUM).
Workers always busy and tables still due: raise the cost limit before raising autovacuum_max_workers (see workers).

For developers: inside the source

Where it lives

autovacuum.c: launcher and workers. The decision formula is relation_needs_vacanalyze().
rel.h: AutoVacOpts, the per-table storage parameters (vacuum_threshold, vacuum_max_threshold, vacuum_ins_threshold, vacuum_scale_factor…), parsed in reloptions.c.
pgstat_relation.c: the counters the decision reads.

Inputs

PgStat_StatTabEntry supplies dead_tuples, ins_since_vacuum and mod_since_analyze (the n_dead_tup, n_ins_since_vacuum, n_mod_since_analyze columns). Backends count changes with pgstat_count_heap_insert/update/delete() and flush them to shared stats in pgstat_relation_flush_cb(), so autovacuum sees committed work with a short delay. pgstat_report_vacuum() sets dead_tuples to what VACUUM left and zeroes ins_since_vacuum; pgstat_report_analyze() zeroes mod_since_analyze. From pg_class it reads reltuples, relpages, relallfrozen (new in 18), relfrozenxid, relminmxid.

Call flow

Worker: AutoVacWorkerMain() → do_autovacuum() scans pg_class for RELKIND_RELATION and RELKIND_MATVIEW, then TOAST tables (partitioned tables never qualify), calling relation_needs_vacanalyze() for each.
relation_needs_vacanalyze(): per-table option or GUC for each constant (a negative option means "use the GUC"), then the wraparound check (force_vacuum when relfrozenxid or relminmxid is older than the freeze max age; MultiXactMemberFreezeThreshold() lowers the multixact age when member space is short), which also overrides autovacuum_enabled = off. Then vacthresh is capped by vac_max_thresh and vacinsthresh is scaled by pcnt_unfrozen.
Before working on a table the worker takes AutovacuumScheduleLock, skips tables another worker is busy with, and rechecks with fresh stats in table_recheck_autovac(). Then autovacuum_do_vac_analyze() calls the ordinary vacuum().
Throttling: VacuumUpdateCosts() → AutoVacuumUpdateCostLimit() divides the limit by AutoVacuumShmem->av_nworkersForBalance (an atomic), unless the table has its own cost options; the sleep is in vacuum_delay_point() in vacuum.c.

Observe

At log_min_messages = debug3 each worker logs one line per table from relation_needs_vacanalyze(), with the format "%s: vac: %.0f (threshold %.0f), ins: %.0f (threshold %.0f), anl: %.0f (threshold %.0f)" (or ins: (disabled)): the quickest way to see why a table was or was not picked. Regression stats.sql covers the counters; TAP tests in src/test/modules/xid_wraparound cover anti-wraparound autovacuum. There is no hook for the decision itself: an extension wanting other rules disables autovacuum per table and schedules VACUUM from a background worker.

Parameters that shape this

autovacuum autovacuum_vacuum_threshold autovacuum_vacuum_scale_factor autovacuum_vacuum_max_threshold autovacuum_vacuum_insert_threshold autovacuum_vacuum_insert_scale_factor autovacuum_analyze_threshold autovacuum_analyze_scale_factor autovacuum_naptime autovacuum_vacuum_cost_limit autovacuum_vacuum_cost_delay log_autovacuum_min_duration