Autovacuum: launcher, workers and thresholds · PostgreSQL

Autovacuum is two things: a launcher that wakes up, decides which tables need attention and starts workers; and the workers, each of which vacuums one table at a time under a shared throughput budget.

Both halves have arithmetic you can compute, and doing so converts most autovacuum questions from opinion into measurement.

The launcher

The launcher aims to start a worker in each database once per autovacuum_naptime — 60 seconds by default. It spreads that work across the interval rather than doing it all at once, so with N databases it starts a worker roughly every autovacuum_naptime / N seconds. The worker then builds its own list of tables in that database that need work and processes them.

Two consequences of the design that trip people up:

The cadence per database is naptime, but the launcher is busier the more databases you have. With ten databases and a 60 second naptime, each database is still visited about once a minute — via a new worker started roughly every six seconds. What that costs is worker slots: ten databases needing attention at once will exhaust autovacuum_max_workers long before naptime is the limiting factor.

A worker processes its list serially. One worker on a database with three enormous tables that all need vacuuming does them one after another. This is why autovacuum_max_workers and table sizes interact.

The eligibility arithmetic

A table becomes eligible for vacuum when:

n_dead_tup  >  autovacuum_vacuum_threshold
             + autovacuum_vacuum_scale_factor × n_live_tup

capped, in PostgreSQL 18, at autovacuum_vacuum_max_threshold.

On defaults that is 50 + 0.2 × n_live_tup, capped at 100,000,000.

Read-only / Safethe threshold for real tables, computed on the server— Captured on 18.6 with default settings. effective_threshold applies the PostgreSQL 18 cap.

$ psql -U postgres -c "SELECT relname, n_live_tup, n_dead_tup, (current_setting('autovacuum_vacuum_threshold')::numeric + current_setting('autovacuum_vacuum_scale_factor')::numeric * n_live_tup) AS scale_based, least(current_setting('autovacuum_vacuum_threshold')::numeric + current_setting('autovacuum_vacuum_scale_factor')::numeric * n_live_tup, current_setting('autovacuum_vacuum_max_threshold')::numeric) AS effective_threshold FROM pg_stat_user_tables WHERE n_live_tup > 0 ORDER BY n_live_tup DESC LIMIT 6"

     relname      | n_live_tup | n_dead_tup | scale_based | effective_threshold
------------------+------------+------------+-------------+---------------------
pgbench_accounts |     500000 |      20428 |    100050.0 |            100050.0
pgbench_history  |     481174 |          0 |     96284.8 |             96284.8
vac_scatter      |     100000 |          0 |     20050.0 |             20050.0
vm_demo          |      50000 |          0 |     10050.0 |             10050.0
churn            |      50000 |       8000 |     10050.0 |             10050.0
fsm_control      |      50000 |      50000 |     10050.0 |             10050.0
(6 rows)

fsm_control has 50,000 dead against a threshold of 10,050 and is overdue. It has autovacuum_enabled = off set on it from an earlier experiment, which is why nothing has collected it — and it is a good illustration of how invisible that setting is once made.

The scale factor is why large tables are vacuumed least often

A scale factor of 0.2 means 20% of the table must be dead before autovacuum acts. That is proportionate on a small table and absurd on a large one:

Live rows	Dead rows required, scale factor only
1,000	250
1,000,000	200,050
100,000,000	20,000,050
1,000,000,000	200,000,050

The table that most needs frequent vacuuming — the big, busy one — is the one the default policy vacuums least often, and when it finally does, the work is enormous.

PostgreSQL 18 addresses this directly. autovacuum_vacuum_max_threshold, new in 18 and defaulting to 100,000,000, caps the computed value. On a billion-row table the requirement halves. Verified against the release notes:

Allow specification of the fixed number of dead tuples that will trigger an autovacuum … The server variable is autovacuum_vacuum_max_threshold. Percentages are still used for triggering.

For tables where even that is too lax, the answer remains per-table settings, which lesson VIII-03 covers.

There are two other triggers with the same shape:

Analyze: autovacuum_analyze_threshold (50) plus autovacuum_analyze_scale_factor (0.1) times live tuples, counting inserts, updates and deletes.

Insert-only vacuum: autovacuum_vacuum_insert_threshold (1000) plus autovacuum_vacuum_insert_scale_factor (0.2) times live tuples. This exists because an append-only table produces no dead tuples and would otherwise never be vacuumed, never build a visibility map, and never freeze — accumulating a single catastrophic aggressive vacuum for later.

The throughput ceiling

This is the arithmetic that decides whether autovacuum can keep up, and almost nobody computes it.

Vacuum accumulates cost points as it works:

Event	Points
Page found in shared buffers	1
Page read from disk	2
Page dirtied	20

When the accumulated cost reaches vacuum_cost_limit (200), the worker sleeps for autovacuum_vacuum_cost_delay (2 ms in PostgreSQL 18) and resets.

Read-only / Safewhat the defaults imply in MB per second— Computed on the server from its own pg_settings values, for the three extreme cases.

$ psql -U postgres -f cost-model.sql

 cost_limit | delay_ms | mb_per_s_all_dirtied | mb_per_s_all_missed | mb_per_s_all_cached
------------+----------+----------------------+---------------------+---------------------
      200 |        2 |                   39 |                 391 |                 781
(1 row)

Roughly 39 MB/s in the worst case where every page is dirtied.

That budget is shared across all workers, not granted to each

From the documentation for autovacuum_vacuum_cost_limit:

Note that the value is distributed proportionally among the running autovacuum workers, if there is more than one, so that the sum of the limits for each worker does not exceed the value of this variable.

So with three workers running, each gets roughly one third: about 13 MB/s of dirtied pages each.

This is the single most misunderstood thing about autovacuum. Raising autovacuum_max_workers on a cluster that is falling behind does not increase total throughput. It divides the same budget into more, slower pieces, and can make things worse by spreading the work thinner across more tables.

If autovacuum cannot keep up, the settings that actually increase throughput are autovacuum_vacuum_cost_limit (raise it) and autovacuum_vacuum_cost_delay (lower it). More workers helps only when the problem is many tables waiting, not one table too slow.

Workers and slots in PostgreSQL 18

Before 18, autovacuum_max_workers required a restart to change, because shared memory for worker slots was sized from it at startup. That has changed. Verified against the release notes:

Add server variable autovacuum_worker_slots to specify the maximum number of background workers … With this variable set, autovacuum_max_workers can be adjusted at runtime up to this maximum without a server restart.

Read-only / Safethe two settings and their contexts on 18.6— Captured on 18.6. Note the context column: max_workers is now sighup.

$ psql -U postgres -c "SELECT name, setting, boot_val, context FROM pg_settings WHERE name IN ('autovacuum_worker_slots','autovacuum_max_workers')"

          name           | setting | boot_val |  context
-------------------------+---------+----------+------------
autovacuum_max_workers  | 3       | 3        | sighup
autovacuum_worker_slots | 16      | 16       | postmaster
(2 rows)

autovacuum_worker_slots reserves the shared memory at startup; autovacuum_max_workers decides how many of those slots may be in use, and can now be raised during an incident without a restart. Setting autovacuum_max_workers above autovacuum_worker_slots has no effect.

Turn on autovacuum logging before you need it

ALTER SYSTEM SET log_autovacuum_min_duration = '0';   -- log every one
SELECT pg_reload_conf();

Zero logs every autovacuum with the full VERBOSE detail. On a busy cluster that is a lot of log volume; '1s' or '10s' gives you the ones worth knowing about while staying manageable.

Without it, autovacuum is invisible. pg_stat_user_tables tells you when a table was last vacuumed and how many times, but not how long it took, how much it removed, how many index passes it needed, or whether it was cancelled.

This is the highest-value observability setting in this part of the course, and it costs nothing until something goes wrong.

pg_stat_progress_vacuum shows what is running right now, including the phase and how far through the heap scan it is. It is the answer to “is it stuck or is it just slow”.

Why autovacuum yields, and the one case where it does not

An autovacuum worker holds SHARE UPDATE EXCLUSIVE. When another session requests a conflicting lock — an ALTER TABLE, a CREATE INDEX, a TRUNCATE — the worker is signalled and cancels itself, logging:

ERROR:  canceling autovacuum task
CONTEXT:  while vacuuming "public.orders"

That is the system working correctly: user work takes priority.

It is also a failure mode, because the vacuum made no progress and will start from the beginning next time. A table subjected to frequent DDL — a partitioned table where partitions are attached and detached hourly, or an ORM that issues DDL on deploy — can have every autovacuum attempt cancelled, indefinitely, while last_autovacuum stays null and nobody notices. That is failure mode four in lesson VIII-04.

An anti-wraparound autovacuum does not yield. Once a table crosses autovacuum_freeze_max_age, the worker that comes for it holds its lock against everything, because the alternative is eventually refusing transactions cluster-wide. This is the correct priority, and it is why a neglected table produces an incident where DDL suddenly cannot proceed and the blocking process is one nobody deployed.

Recognising an anti-wraparound worker matters, because the instinct to kill the blocker is wrong here:

SELECT pid, query, backend_type, now() - xact_start AS running_for
  FROM pg_stat_activity
 WHERE query LIKE 'autovacuum:%';
-- an anti-wraparound worker's query text contains "(to prevent wraparound)"

Kill that and it comes straight back, having lost its progress, while the age keeps climbing.

What to take from this

Eligibility is threshold + scale_factor × live_tuples, capped by autovacuum_vacuum_max_threshold in PostgreSQL 18.
The scale factor means big tables are vacuumed least often, which is backwards; per-table settings are the fix.
The cost budget is roughly 39 MB/s of dirtied pages on defaults, and it is shared across all workers.
More workers does not mean more throughput. Raise the cost limit or lower the delay for that.
autovacuum_max_workers is adjustable without a restart in PostgreSQL 18, bounded by autovacuum_worker_slots.
Set log_autovacuum_min_duration before you need it.

Cross-course references

Linux for Production Sysadmins — Part VI (Processes) covers seeing autovacuum workers as operating-system processes, which is how you confirm the launcher is actually starting them.
Observability for Production Sysadmins — Part LIX (Database observability) covers exporting last_autovacuum and dead-tuple counts per table, which is what makes a worker shortage visible before it becomes bloat.