Autovacuum launcher and workers

For DBAs and operators

The launcher

One process (postgres: autovacuum launcher, wait_event AutovacuumMain). It builds a schedule so that each database gets a worker roughly every autovacuum_naptime (1min): with 20 databases, it starts one every 3 seconds. Databases at risk of transaction ID or multixact wraparound are served first. It cannot start workers itself; it signals the postmaster, which forks them. If all worker slots are busy, a due database simply waits, which is how large clusters with many busy databases fall behind.

Workers and slots

Each worker is postgres: autovacuum worker <dbname> with backend_type = 'autovacuum worker' and a query like autovacuum: VACUUM ANALYZE public.big. New in PostgreSQL 18: autovacuum_worker_slots (restart, default 16) reserves the process slots, while autovacuum_max_workers can now be changed with a reload, up to that number.

A worker reads the per-table counters from the cumulative statistics (n_dead_tup, n_ins_since_vacuum, n_mod_since_analyze), compares them with the thresholds explained in Autovacuum, and processes the qualifying tables. It rechecks each table just before starting it, so two workers in the same database do not vacuum the same table.

Shared cost budget

autovacuum_vacuum_cost_limit (-1 means use vacuum_cost_limit, 200) is divided among all running workers that use the default cost settings; each sleeps autovacuum_vacuum_cost_delay (2ms) when it has spent its share (wait_event VacuumDelay). Tables with their own cost settings are not balanced. To make autovacuum faster, raise the cost limit, not only the number of workers. PostgreSQL 18 adds track_cost_delay_timing to measure the sleeping in pg_stat_progress_vacuum.delay_time.

Being cancelled

When a normal autovacuum blocks someone for deadlock_timeout (for example ALTER TABLE, LOCK TABLE, TRUNCATE), the waiting backend cancels it. The worker logs this and continues with its next table:

A worker started because age(relfrozenxid) passed autovacuum_freeze_max_age shows (to prevent wraparound) in its query and is not auto-cancelled. DDL then queues behind it, and everything else on the table queues behind the DDL. Do not kill it repeatedly: it will just start again. Let it finish, or run a manual VACUUM (FREEZE) during a quiet period.

Watching it

SELECT pid, datname, wait_event, now() - xact_start AS running, query
  FROM pg_stat_activity
 WHERE backend_type = 'autovacuum worker';

SELECT p.pid, p.relid::regclass, p.phase,
       p.heap_blks_scanned, p.heap_blks_total, p.delay_time
  FROM pg_stat_progress_vacuum p;

With log_autovacuum_min_duration (default 10min since PostgreSQL 15; set it to 0 to log every run) each run logs LOG: automatic vacuum of table "postgres.public.t": index scans: 0 followed by pages, tuples, buffer usage and WAL usage details.

If they die

A worker whose current table is cancelled (pg_cancel_backend() or a lock conflict) moves on to its next table; one terminated with pg_terminate_backend() just exits (FATAL: terminating autovacuum process due to administrator command). Killing the launcher or a worker with signal 9 causes a crash restart, which also resets the statistics autovacuum relies on.

PostgreSQL 18 source trail: autovacuum.c documents launcher/worker roles and balancing of cost-delay settings.