The autovacuum process has been dutifully tending Postgres tables since version 8.1, and almost every release since has acquired some new refinement: smarter thresholds, insert-aware triggering, resource cost limits, and so on. It's the reliable maintenance workhorse that heroically forestalls transaction ID wraparound and keeps statistics fresh. So how did the devs improve it this time around?

For most of its life, an autovacuum worker built its list of tables needing attention and then worked through them in roughly the order they appear in the pg_class catalog. Very egalitarian. But a table one transaction away from triggering a wraparound shutdown got the same treatment as a table that crossed a statistical update threshold. Any implied urgency is lost in that context. 

So how do you teach a maintenance daemon the difference between sweeping the floor and putting out a five-alarm fire? Can an administrator dictate that the cluster cares more about freezing rows than reclaiming space without rewriting the whole scheduler? There's only one way to find out!

Keeping Score

The mechanism behind the change is a prioritization system that scores every table before the worker decides where to start. The launcher picks a database first, favoring any database skirting transaction ID or multixact wraparound, then deferring to whichever went longest without attention. Within that database, the worker builds its candidate list and sorts it by a numeric score instead of catalog order.

That score is a weighted heuristic. Postgres computes five separate component scores for each table and keeps the highest as the table's overall priority. The components are the transaction ID age, the multixact ID age, the count of dead tuples waiting to be reclaimed, the count of freshly inserted tuples, and the volume of changes since the last ANALYZE. Whichever of those is highest decides the table's place in line.

We don't have to take any of this on faith, because Postgres 19 also exposes a new pg_stat_autovacuum_scores view that reflects the current scores for every table in the database:

\d pg_stat_autovacuum_scores

               View "pg_catalog.pg_stat_autovacuum_scores"
       Column        |       Type       
---------------------+------------------
 relid               | oid
 schemaname          | name
 relname             | name
 score               | double precision
 xid_score           | double precision
 mxid_score          | double precision
 vacuum_score        | double precision
 vacuum_insert_score | double precision
 analyze_score       | double precision
 do_vacuum           | boolean
 do_analyze          | boolean
 for_wraparound      | boolean

The score column is that overall maximum, and the five *_score columns are the raw components feeding it. Numbers in a catalog view are abstract, though. Let's make some real ones.

Two's Company

Consider two tables with completely different patterns. The first is an append-only event log that only ever grows. The second is a work queue that churns constantly as jobs flip state and get cleared out. We can disable autovacuum on both so the daemon doesn't automatically clean up before we get a look at the scores:

CREATE TABLE append_log (id bigserial PRIMARY KEY, payload text)
  WITH (autovacuum_enabled = false);

CREATE TABLE churn_queue (id bigserial PRIMARY KEY, state int, payload text)
  WITH (autovacuum_enabled = false);

INSERT INTO append_log (payload)
  SELECT 'event' FROM generate_series(1, 500000);

INSERT INTO churn_queue (state, payload)
  SELECT 0, 'job' FROM generate_series(1, 200000);

UPDATE churn_queue SET state = 1 WHERE id % 2 = 0;
DELETE FROM churn_queue WHERE id % 5 = 0;

The log absorbed half a million clean inserts. The queue saw two hundred thousand inserts, then experienced a hundred thousand updates and forty thousand deletes, leaving plenty of dead tuples. After nudging the statistics collector with pg_stat_force_next_flush(), here's how Postgres scores them:

SELECT relname, ceil(score) AS score,
       ceil(vacuum_score) AS vacuum_score,
       ceil(vacuum_insert_score) AS insert_score,
       ceil(analyze_score) AS analyze_score
  FROM pg_stat_autovacuum_scores
 WHERE relname IN ('append_log', 'churn_queue')
 ORDER BY score DESC;

   relname   | score | vacuum_score | insert_score | analyze_score 
-------------+-------+--------------+--------------+---------------
 append_log  | 10000 |            0 |          500 |         10000
 churn_queue |  6800 |         2800 |          200 |          6800

Each component is essentially a ratio: how far past its trigger threshold the table has drifted. The append-only log has zero dead tuples, so its vacuum_score is a flat zero. It has half a million inserts against an insert threshold of 1000, giving an insert_score of 500. And every one of those inserts counts toward analyze, which against the default threshold of fifty rows works out to an analyze_score of 10000. The queue tells a different story: a hundred and forty thousand dead tuples push its vacuum_score up to 2800, while its mix of inserts, updates, and deletes lands its analyze_score at 6800.

The overall score for each table is simply the loudest of its constituents. For both tables here, that happens to be the analyze score, so the log outranks the queue, 10000 to 6800. Maybe that's exactly what you want. But maybe you run a system where reclaiming dead space matters more than refreshing a few statistics, and you'd rather the churning queue jumped the line. That’s now an option.

A Thumb on the Scale

This is where the six new tuning knobs earn their keep. Five of them are scaling factors meant to multiply against the matching component score before determining the maximum.

autovacuum_freeze_score_weight           = 1.0
autovacuum_multixact_freeze_score_weight = 1.0
autovacuum_vacuum_score_weight           = 1.0
autovacuum_vacuum_insert_score_weight    = 1.0
autovacuum_analyze_score_weight          = 1.0

Each weight defaults to 1.0, meaning all concerns are treated equally. Raising autovacuum_analyze_score_weight to 2.0 doubles the priority of the analyze component. Lowering it to 0.5 cuts analyze scores in half. There's even a documented escape hatch: set all five weights to 0.0 and the cluster reverts to the pre-19 strategy of plain catalog order. Just in case the old behavior works better for some reason.

So let's put our thumb on the scale. Suppose we decide reclamation is twice as important as it used to be, and statistics refreshes are worth only half. These are sighup parameters, so a configuration reload is enough to apply them with no restart:

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

Now the same two tables sort differently:

SELECT relname, ceil(score) AS score,
       ceil(vacuum_score) AS vacuum_score,
       ceil(analyze_score) AS analyze_score
  FROM pg_stat_autovacuum_scores
 WHERE relname IN ('append_log', 'churn_queue')
 ORDER BY score DESC;

   relname   | score | vacuum_score | analyze_score 
-------------+-------+--------------+---------------
 churn_queue |  5600 |         5600 |          3400
 append_log  |  5000 |            0 |          5000

How about that? The queue's vacuum component doubled from 2800 to 5600, its analyze component halved from 6800 to 3400. The log's analyze component fell from 10000 to 5000. Now the maintenance priority has flipped. The churning queue that used to sit behind the log now leads it, purely because we told the cluster that dead space bothers us more than stale statistics. That single policy decision rearranged the entire maintenance schedule.

Notice that the reweighting touched the vacuum and insert numbers independently. Let's get into why inserts are tracked separately. Veterans are already aware of this distinction, but it's an interesting distinction worth exploration.

Inserts Are Special

It's tempting to lump all table activity together as "stuff that makes autovacuum work harder," but inserts and updates create fundamentally different messes. An UPDATE or a DELETE leaves dead tuples behind, and dead tuples are the bloat that the vacuum in autovacuum exists to reclaim. A plain INSERT doesn’t have this effect at all, and in fact may even replace a previously reclaimed slot. The append-only log above proved it: half a million rows added, and a vacuum_score of exactly zero, because there was nothing to clean up.

So why does an insert-only table ever need an autovacuum at all? First, those rows still shift the table's statistics. A query planner working from a stale row count on a table that grew by half a million entries will probably make some genuinely terrible decisions, so insert activity should eventually trigger an analyze. Second, every row Postgres has ever written carries a transaction ID that must eventually be frozen, whether anyone updates the row or not.

That's the reasoning behind giving insert activity its own insert threshold back in Postgres 13. We could manage a data warehouse packed with append-only fact tables where the vacuum component is almost always zero and the real maintenance pressure comes entirely from inserts. A system like that would mainly focus on autovacuum_vacuum_insert_score_weight.

That said, most systems should probably focus on the freeze weights.

Frozen Landscape

Postgres tracks row visibility with transaction IDs, and that counter is finite. Left unfrozen, the oldest rows in a table inch ever closer to the wraparound horizon. Once a database actually reaches that point, it stops accepting writes until Postgres completes an emergency vacuum. Freezing is the maintenance that rewrites those old transaction IDs as permanently visible, and unlike reclaiming dead space, it is not optional. Every row gets there eventually.

That inevitability is exactly why the freeze scores deserve to outrank the others when things get tight. The transaction ID score measures a table's relfrozenxid age against autovacuum_freeze_max_age, and the multixact score does the same for relminmxid. As a table's oldest unfrozen rows age, its freeze score climbs, and a table approaching wraparound will eventually out-score all other weighted metrics.

The two freeze weights, autovacuum_freeze_score_weight and autovacuum_multixact_freeze_score_weight, carry a subtlety the others don't. Increasing them above 1.0 doesn't just multiply the score, it divides the thresholds by the weight. Pushing the freeze weight to 2.0 tells Postgres to start treating freeze pressure as urgent at half the usual autovacuum_freeze_max_age. The max freeze age still applies, but score totals could mean a table gets frozen much earlier than we’d normally expect.

There's also a hard backstop introduced in Postgres 18. The vacuum_failsafe_age and vacuum_multixact_failsafe_age parameters, both defaulting to 1.6 billion, mark the point where Postgres stops being polite. Once a table crosses that age, autovacuum throws out its cost-based delays, skips non-essential work like index vacuuming, and bulldozes straight into freezing the table at basically any cost. So these two parameters aren't just an emergency brake anymore, but a heavy influence on the observable score.

Sorting tables sensibly is only half the battle, though. Picking the right table to vacuum first doesn't help much if that table then takes an hour to finish.

Many Hands Make Light Work

The sixth new parameter is a different animal from the five weights. The autovacuum_max_parallel_workers parameter lets a single autovacuum worker recruit helpers to process a table's indexes in parallel during the index vacuuming and cleanup phases.

Manual VACUUM has been able to parallelize index work since Postgres 13, but autovacuum workers have long been relegated to handling one table and its indexes serially. As a result, a wide table with a dozen indexes could monopolize an autovacuum worker for excessive lengths of time.

For now, it's necessary to set the new parameter to a non-zero value to opt in:

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

Now a single autovacuum worker can launch up to four parallel workers when it reaches a table with enough indexes. Paired with the prioritization system, the two halves start to complement each other. The scoring determines the table priority order, and the parallel workers enable processing each as quickly as possible. Triage, then throughput.

The Shape of Things to Come

It's very tempting to look at six new Grand Unified Configuration (GUC) variables with names like autovacuum_multixact_freeze_score_weight and file them under "obscure dials I'll never touch." Most clusters will probably be just fine leaving the weights at 1.0. This is relatively unexplored territory, so inert defaults are a perfectly good starting position. The point of these knobs is merely that the autovacuum machinery finally has a vocabulary for priority at all.

The fact that scores are constantly reflected in a view is great disclosure as well. Up until now, diagnosing autovacuum behavior meant reading source code and making educated guesses. Was it from a table approaching the max freeze age? Autovacuum threshold? Analyze scale factor? Something else? Now pg_stat_autovacuum_scores lays the reasoning out in plain sight. Heck, we can even monitor it in a dashboard.

Then there's this note from the commit that introduced this prioritization system:

This is intended to be a baby step towards smarter autovacuum
workers.  Possible future improvements include, but are not limited
to, periodic reprioritization, automatic cost limit adjustments,
and better observability (e.g., a system view that shows current
scores).  While we do not expect this commit to produce any
earth-shattering improvements, it is arguably a prerequisite for
the aforementioned follow-up changes.

That's pretty exciting, no? Autovacuum will continue to improve, just as it always has. Speaking purely as a former DBA in charge of a financial database that handled over a billion transactions per day, I can authoritatively state that we all sleep a little easier with every new autovacuum parameter. Even un-modified, it's comforting knowing they're available to address mysterious future emergencies. I can't wait to see what they come up with next.