PostgreSQL 19 autovacuum - parallel workers and the scoring system

Autovacuum has two jobs: keep dead tuples from accumulating, and keep every table’s relfrozenxid young enough that the cluster never approaches transaction ID wraparound. Before 19 it had a blind spot in each. Table order inside a database was effectively pg_class order - no notion of which table was most urgent. And a vacuum’s index cleanup ran serially even though manual VACUUM had supported a PARALLEL option since 13. PostgreSQL 19 fixes both.

The scoring system

The launcher still picks a database first - the one at risk of wraparound, else the least recently processed - but inside the database the worker now builds the list of tables needing vacuum or analyse and sorts them by a score: the maximum of five component scores, each comparing a measurement against its threshold.

Each weight multiplies its component, so autovacuum_analyze_score_weight = 2.0 doubles the analyse component’s score. Two non-obvious behaviours are in the documentation: for the two freeze components, a weight above 1.0 also lowers the age at which that component starts scaling aggressively (the scaling age is divided by the weight); and setting all five weights to 0.0 reverts to the pre-19 behaviour of plain pg_class order.

-- watch what autovacuum thinks is urgent, in the current database:
SELECT * FROM pg_stat_autovacuum_scores;

pg_stat_autovacuum_scores is the diagnostic this feature ships with - it shows the current score of every table in the current database, which is the answer to “why is autovacuum ignoring my hot table” that previously had no answer.

A worked score

Take three tables in one database with default settings (autovacuum_freeze_max_age 200 million, autovacuum_vacuum_threshold 50 + 20% scale factor, all five weights at 1.0):

sessions vacuums first despite audit_log being closer to wraparound in absolute terms: scoring normalises each component against its own threshold and takes the maximum, so a table over a regular threshold outranks one merely approaching freeze age - until freeze age passes the failsafe point, where the freeze component grows sharply. Tuning then reads like this: a hot append-mostly table that keeps losing the queue gets autovacuum_vacuum_insert_score_weight raised server-wide only if many insert-heavy tables share the problem; for one table the honest fix is still lowering its autovacuum_vacuum_insert_scale_factor storage parameter, because the weights move every table at once.

Parallel index vacuuming

Manual VACUUM (PARALLEL n) could already run index vacuum and index cleanup with background workers; in 19 autovacuum can do the same:

Enable it globally with autovacuum_max_parallel_workers, and steer per table with the storage parameter. The caveats are straight from the parallel vacuum rules: an index participates only if larger than min_parallel_index_scan_size, one worker per index, so parallelism needs at least two qualifying indexes; and the calculated worker count is not guaranteed - a vacuum may run with fewer or none. Workers also inherit the leader’s cost delay settings and come out of the same max_parallel_workers budget as everything else.

ALTER TABLE events SET (autovacuum_parallel_workers = 4);
-- postgresql.conf:
-- autovacuum_max_parallel_workers = 4

The trade-off: index phases finish sooner, but each worker draws on the shared worker pool and the same I/O budget, so on a small instance a parallel autovacuum can compete with query parallelism rather than complement it.

Monitoring changes around it

• pg_stat_progress_vacuum gains started_by (who launched the vacuum, including autovacuum_wraparound for anti-wraparound runs) and mode (its aggressiveness); pg_stat_progress_analyze gains the same started_by column.
• log_autovacuum_min_duration now covers only vacuum operations; a separate log_autoanalyze_min_duration logs long-running autovacuum-triggered analyses, so you can watch analyse without vacuum noise or vice versa.
• The wraparound warning fires at 100 million transactions remaining, up from 40 million - earlier warning, same failure.
• VACUUM (VERBOSE) and autovacuum logs now report memory usage and parallelism.
• Separately but related: query table scans can now mark pages all-visible in the visibility map, a job previously reserved to VACUUM and COPY ... FREEZE. Reads now help future index-only scans - verify the bits with pg_visibility, covered on maintenance extensions.

Manual vacuuming: vacuumdb and index I/O

Two changes on the manual side of routine vacuuming, plus a performance one under the hood:

• vacuumdb --dry-run prints the VACUUM/ANALYZE commands it would run without running them - the safe way to check what a scheduled run would touch.
• vacuumdb --analyze-only and --analyze-in-stages now include partitioned tables when no targets are given, matching ANALYZE; previously they silently skipped them.
• Hash index bulk-deletion and GIN index vacuuming switched to streaming reads - less I/O latency on the index-cleanup phase for those index types.

What changed, and in which release

Where it breaks

• Weights set unevenly. Raising autovacuum_freeze_score_weight does not just rank freeze urgency higher, it lowers the age where freeze scoring turns aggressive. Read the component semantics in pg_stat_autovacuum_scores before tuning by feel.
• Expecting workers that never come. autovacuum_max_parallel_workers is a cap, not a promise - under max_parallel_workers contention or with fewer than two large indexes, vacuum stays serial.
• Disabled autovacuum still cannot stop wraparound vacuums. Unchanged from earlier versions: autovacuum = off does not prevent anti-wraparound workers, and started_by = autovacuum_wraparound in pg_stat_progress_vacuum is how you see them.
• Beta caveat. The GUC names and defaults are from the 19 beta documentation and may still change before GA.

What this is not for

• A bloated table that already needs surgery - scoring decides order, not capability; a table that has outgrown vacuum still needs REPACK or VACUUM FULL.
• Per-table thresholds. The existing storage parameters (autovacuum_vacuum_scale_factor and friends) remain the right tool for a single hot table; scoring changes which table goes first, not when each qualifies.