Parallel autovacuum and score-based scheduling

Before PostgreSQL 19: serial autovacuum

Two long-standing limitations shaped autovacuum operations through PostgreSQL 18:

• An autovacuum worker processes one table at a time, and the vacuuming indexes and cleaning up indexes phases walk the table's indexes serially. A wide table with a dozen indexes could hold a worker for hours while other tables waited.
• Within a database, the worker processed candidate tables in roughly pg_class catalog order. A table approaching transaction ID wraparound had no formal priority over one that barely crossed an analyze threshold.

Manual VACUUM has supported PARALLEL for index work since PostgreSQL 13, but autovacuum could not use it. Closing the gap for a big table meant running manual vacuums by hand — see autovacuum and table bloat for the baseline mechanics.

Parallel index processing: autovacuum_max_parallel_workers

autovacuum_max_parallel_workers sets the maximum number of parallel workers a single autovacuum worker may recruit to process indexes during the index vacuuming and index cleanup phases. It defaults to 0 (disabled), so it is opt-in:

ALTER SYSTEM SET autovacuum_max_parallel_workers = 4;
SELECT pg_reload_conf();

It is the autovacuum equivalent of the PARALLEL option of manual VACUUM. The actual worker count is further limited by max_parallel_workers, which is shared with parallel query.

A per-table storage parameter caps individual tables — useful for keeping one hot table from consuming the whole parallel budget:

ALTER TABLE app.events SET (autovacuum_parallel_workers = 2);

Constraints, per Section 24.1.7, Parallel Vacuum:

• An index participates only if it is larger than min_parallel_index_scan_size, and each index gets at most one worker — so a table needs at least two eligible indexes for parallel workers to launch at all.
• The calculated number of workers is not guaranteed; a vacuum may run with fewer workers or none.
• Parallel workers use the same cost delay parameters as the leader autovacuum worker, so the existing throttling model still applies.

Score-based scheduling

Within a database, the autovacuum worker now builds its candidate list and sorts it by score instead of catalog order. The score of a table is the maximum of five component scores, described in Section 24.1.6.1, Autovacuum Prioritization:

• Transaction ID age — age(relfrozenxid) against autovacuum_freeze_max_age; it grows sharply once the age passes vacuum_failsafe_age. Weight: autovacuum_freeze_score_weight.
• Multixact ID age — relminmxid against autovacuum_multixact_freeze_max_age; also grows sharply past vacuum_multixact_failsafe_age or when multixact members exceed roughly 2 billion entries. Weight: autovacuum_multixact_freeze_score_weight.
• Vacuum — updated/deleted tuples against the vacuum threshold. Weight: autovacuum_vacuum_score_weight.
• Vacuum insert — inserted tuples against the insert threshold. Weight: autovacuum_vacuum_insert_score_weight.
• Analyze — changed tuples against the analyze threshold. Weight: autovacuum_analyze_score_weight.

All five weights default to 1.0 (equal treatment) and are reloadable with pg_reload_conf(). Two subtleties from the documentation:

• Raising a freeze weight above 1.0 does more than multiply the score — the age at which the component starts scaling aggressively is divided by the weight, so freeze pressure becomes urgent earlier.
• Setting all five weights to 0.0 reverts to the pre-19 strategy of plain catalog order.

Database selection is separate: the launcher still prioritizes databases at risk of wraparound, then the least recently processed one.

Observing scores with pg_stat_autovacuum_scores

The new pg_stat_autovacuum_scores view shows the current scores for every table in the current database, turning "why is autovacuum ignoring this table" from guesswork into a query:

SELECT relid::regclass AS relation,
       round(score::numeric, 1)             AS score,
       round(xid_score::numeric, 1)         AS xid_score,
       round(vacuum_score::numeric, 1)      AS vacuum_score,
       round(vacuum_insert_score::numeric, 1) AS insert_score,
       round(analyze_score::numeric, 1)     AS analyze_score,
       do_vacuum, do_analyze, for_wraparound
FROM pg_stat_autovacuum_scores
ORDER BY score DESC
LIMIT 20;

score is the maximum of the five *_score components; do_vacuum / do_analyze show what the table currently qualifies for, and for_wraparound marks anti-wraparound pressure. One caveat from the documentation: the view computes scores from the information visible to your session, which can differ from what an autovacuum worker sees when it builds its list — so it is a debugging aid, not a guarantee of processing order. Use it before and after reweighting to confirm the ordering actually changed — for example, making dead-tuple reclamation outrank statistics refresh:

ALTER SYSTEM SET autovacuum_vacuum_score_weight = 2.0;
ALTER SYSTEM SET autovacuum_analyze_score_weight = 0.5;
SELECT pg_reload_conf();

Add the view to the regular inspection routine described in monitoring and logging, alongside pg_stat_progress_vacuum.

Compared with vacuumdb --jobs on PostgreSQL 18

The classic PostgreSQL 18 workaround for slow maintenance is manual parallelism:

vacuumdb --jobs=4 --analyze dbname

vacuumdb --jobs runs several connections, each processing a different table — parallelism across tables, on a schedule you own, during windows you pick. It does not speed up the index phases of a single large table unless you run VACUUM (PARALLEL n) yourself, and it does nothing about processing order inside autovacuum.

PostgreSQL 19 covers the complementary axis: parallelism within one table's index phases, and urgency-aware ordering, both automatic. Scheduled vacuumdb runs remain useful for predictable batch windows and full-cluster FREEZE passes; the two are not mutually exclusive.

When not to enable it

• I/O-bound systems. Parallel index vacuuming multiplies concurrent I/O streams. The cost limit is still shared, but latency-sensitive workloads on saturated storage should enable it gradually and watch pg_stat_io and query latency.
• Small databases. If all indexes are under min_parallel_index_scan_size, no index ever participates — the setting changes nothing except expectations.
• Tight worker budgets. Vacuum parallel workers come out of max_parallel_workers, the same pool parallel query uses. On small instances, raising both can starve query parallelism.
• Mostly single-index tables. One worker per index means a table with one eligible index never goes parallel.

As with any autovacuum tuning, change one knob at a time and verify with pg_stat_autovacuum_scores and the autovacuum log rather than assuming. For more on PostgreSQL 19, see the release overview.

AI prompt: tune autovacuum scoring

Help me tune PostgreSQL 19 autovacuum scoring.

1. Here is the output of: SELECT relid::regclass, score, xid_score, mxid_score, vacuum_score, vacuum_insert_score, analyze_score, do_vacuum, do_analyze, for_wraparound FROM pg_stat_autovacuum_scores ORDER BY score DESC LIMIT 30; (paste it)
2. My workload: (describe — e.g. append-only event tables + a churning queue table, nightly batch deletes)
3. Please:
 - Explain which component dominates the score for each top table and why
 - Recommend concrete values for autovacuum_freeze_score_weight / autovacuum_vacuum_score_weight / autovacuum_analyze_score_weight (remember: freeze weights above 1.0 also lower the aggressive-scaling age)
 - Tell me which tables need per-table storage parameters instead of global weights
 - Warn me if any for_wraparound table is being starved
4. Suggest how to verify the effect after pg_reload_conf().