Welcome to the Day3 of PG19 Hacktober!!
If you’ve run PostgreSQL in production, you’ve probably watched autovacuum spend hours on one huge table while other tables waited. PostgreSQL 19 (tested here on beta 4) addresses this in two ways. Autovacuum can clean a table’s indexes in parallel, and it can decide which table needs attention first.
This post covers what autovacuum is, how the feature developed, what PostgreSQL 18 does and what 19 improves, and commands you can run yourself.
Most of us have heard of autovacuum, but for those who want a refresher, here’s what it is. When you update or delete a row in PostgreSQL, the old version doesn’t disappear right away. It stays on disk as a dead tuple. Autovacuum is the background process that cleans these up so your tables don’t bloat and your queries stay fast. It also protects the database from transaction ID wraparound, which becomes a serious problem if ignored. Think of it as a cleaning crew that works while the shop is open.
A vacuum on a table has a few phases: scanning the table (the heap), cleaning the indexes, and removing the dead rows. On large tables with many indexes, index cleaning is often the slowest phase, and it’s the one that matters for this post.
What’s new in PostgreSQL 19, and how we got here
A short history
- PostgreSQL 13 added parallel vacuum: VACUUM (PARALLEL N) can clean several indexes at once. This worked only for manual VACUUM. Autovacuum couldn’t use it.
- PostgreSQL 18 added tuning knobs for autovacuum, such as autovacuum_worker_slots and autovacuum_vacuum_max_threshold. These help you tune how autovacuum runs, but they don’t change how it cleans indexes or picks tables.
- PostgreSQL 19 closes both gaps.
Feature 1: Parallel autovacuum
Autovacuum Workers can now bring in helpers for two phases:
- Index vacuuming: removing index entries that point to dead rows
- Index cleanup: updating index statistics afterward
Each helper handles one index at a time. With 4 indexes and 3 helpers, the leader takes one, and the helpers take the other three, so the phase takes about as long as your largest single index. Heap scanning and heap vacuuming are still single-threaded, so don’t expect the whole vacuum to get N times faster.
It’s off by default. Two settings control it:
| Setting | What it does |
| autovacuum_max_parallel_workers | Cluster-wide cap on parallel workers per autovacuum worker. Default 0 (off). |
| autovacuum_parallel_workers | Per-table storage parameter that overrides the global value. |
Real limits still apply: max_parallel_workers the number of indexes on the table, and the rule that an index must be larger than min_parallel_index_scan_size to qualify.
Watch memory. Parallel workers add memory use. Autovacuum uses autovacuum_work_mem if set, and otherwise falls back to maintenance_work_mem. Monitor memory while you test before enabling this on a busy server.
Feature 2: Priority scores for tables
Autovacuum now gives each table a score and handles the most urgent ones first. The score is the highest of five components: transaction ID age, multixact age, vacuum, vacuum insert, and analyze. Databases at risk of wraparound are served first.
You can tune each component with a weight (all default to 1.0):
autovacuum_freeze_score_weightautovacuum_multixact_freeze_score_weightautovacuum_vacuum_score_weightautovacuum_vacuum_insert_score_weightautovacuum_analyze_score_weight
Set them all to 0.0, and you get the old ordering back.
The new view pg_stat_autovacuum_scores shows the scores. Its columns are relid, schemaname, relname, score, xid_score, mxid_score, vacuum_score, vacuum_insert_score, analyze_score, do_vacuum, do_analyze and for_wraparound.
What PostgreSQL 18 does, and what 19 improves
In PostgreSQL 18:
- One worker per table. Autovacuum cleans a table’s indexes one after another. A table with five big indexes pays the full price for each.
- No ranking by urgency. Autovacuum works through tables in the order they appear in pg_class. A tiny table could be handled before a huge one close to wraparound.
- Manual VACUUM (PARALLEL N) works, but you must run it yourself.
What 19 improves:
| PostgreSQL 18 | PostgreSQL 19 beta 4 | |
| Parallel index cleanup in autovacuum | No | Yes (opt-in) |
| autovacuum_max_parallel_workers | Doesn’t exist | Exists, default 0 |
| Manual VACUUM (PARALLEL N) | Works | Works |
| Table ordering | pg_class order | Score-based |
| pg_stat_autovacuum_scores view | Doesn’t exist | Exists |
| Per-component score weights | No | Five settings, default 1.0 |
Hands-on
Step 1: Turn on logging
ALTER SYSTEM SET autovacuum_naptime = '10s';
ALTER SYSTEM SET log_autovacuum_min_duration = 0; ALTER SYSTEM SET maintenance_work_mem = '256MB';
SELECT pg_reload_conf();Step 2: Create a table with five indexes
CREATE TABLE events (
id bigserial PRIMARY KEY,
user_id int,
event_type int,
region int,
payload text,
created_at timestamptz DEFAULT now()
);
INSERT INTO events (user_id, event_type, region, payload)
SELECT (random()*100000)::int, (random()*50)::int,
(random()*20)::int, md5(random()::text)
FROM generate_series(1, 5000000);
CREATE INDEX ON events (user_id);
CREATE INDEX ON events (event_type);
CREATE INDEX ON events (region);
CREATE INDEX ON events (created_at);
ANALYZE events;Check the index sizes:
SELECT indexrelid::regclass AS index,
pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_index WHERE indrelid = 'events'::regclass;
index | size
-----------------------+--------
events_pkey | 107 MB
events_user_id_idx | 34 MB
events_event_type_idx | 33 MB
events_region_idx | 33 MB
events_created_at_idx | 33 MB
Part A: PostgreSQL 18
Step 3: Confirm there is no parallel autovacuum setting
SHOW server_version;
SHOW autovacuum_max_parallel_workers;
server_version
----------------
18.6
ERROR: unrecognized configuration parameter "autovacuum_max_parallel_workers"Step 4: Create dead rows and let autovacuum run
Pause autovacuum on the table, delete a third of the rows, then re-enable it so it starts within about 10 seconds:
ALTER TABLE events SET (autovacuum_enabled = false);
DELETE FROM events WHERE id % 3 = 0;
ALTER TABLE events SET (autovacuum_enabled = true);
DELETE 1666666In a second session, watch the progress:
SELECT pid, relid::regclass AS tbl, phase,
heap_blks_scanned, heap_blks_total,
indexes_total, indexes_processed
FROM pg_stat_progress_vacuum \watch 1
pid | tbl | phase | heap_blks_scanned | heap_blks_total | indexes_total | indexes_processed
------+--------+-------------------+-------------------+-----------------+---------------+-------------------
6245 | events | scanning heap | 13333 | 56819 | 0 | 0
6245 | events | scanning heap | 45576 | 56819 | 0 | 0
6245 | events | vacuuming indexes | 56819 | 56819 | 5 | 0
6245 | events | vacuuming indexes | 56819 | 56819 | 5 | 1
6245 | events | vacuuming indexes | 56819 | 56819 | 5 | 2
6245 | events | vacuuming indexes | 56819 | 56819 | 5 | 3
6245 | events | vacuuming indexes | 56819 | 56819 | 5 | 4
6245 | events | vacuuming heap | 56819 | 56819 | 0 | 0What to notice: one process (pid 6245) handles everything, and indexes_processed rises one index at a time. The vacuum has three phases: scanning the heap, vacuuming the indexes, and vacuuming the heap.
Step 5: Read the log entry
LOG: automatic vacuum of table "demo.public.events": index scans: 1
tuples: 1666666 removed, 3333334 remain, 0 are dead but not yet removable
index "events_pkey": pages: 13712 in total, ...
index "events_user_id_idx": pages: 4342 in total, ...
...
system usage: CPU: user: 2.47 s, system: 0.99 s, elapsed: 38.53 sThere is no line about parallel workers. On PostgreSQL 18, autovacuum took 38.53 s here.
Step 6: Manual parallel vacuum works on 18
DELETE FROM events WHERE id % 3 = 1;
VACUUM (PARALLEL 4, VERBOSE) events;
INFO: launched 2 parallel vacuum workers for index vacuuming (planned: 2)Parallel vacuum has existed since version 13, but only when you run VACUUM yourself. PARALLEL 4 is a maximum, and PostgreSQL planned 2 workers here.
Part B: PostgreSQL 19 beta 4
Step 7: Check the new setting
SELECT name, setting, boot_val, source
FROM pg_settings
WHERE name = 'autovacuum_max_parallel_workers';On a fresh server, this returns 0, 0, default, so parallel autovacuum is off until you enable it.
Step 8: Control run with parallel autovacuum off
Reload the table (Step 2), then repeat the Step 4 commands. The log entry:
LOG: automatic vacuum of table "demo.public.events": index scans: 1
tuples: 1666666 removed, 3333334 remain, 0 are dead but not yet removable
...
system usage: CPU: user: 1.87 s, system: 1.09 s, elapsed: 36.21 sThere is no parallel line, and the run took 36.21 s, close to PostgreSQL 18.
Step 9: Turn on parallel autovacuum
ALTER SYSTEM SET autovacuum_max_parallel_workers = 4;
SELECT pg_reload_conf();The server log confirms the change without a restart:
LOG: received SIGHUP, reloading configuration files
LOG: parameter "autovacuum_max_parallel_workers" changed to "4"Repeat the same delete and enable steps. The log entry now has a new line:
LOG: automatic vacuum of table "demo.public.events": index scans: 1
tuples: 1666667 removed, 1666667 remain, 0 are dead but not yet removable
...
parallel workers: index vacuum: 3 planned, 3 launched in total
...
system usage: CPU: user: 1.14 s, system: 0.60 s, elapsed: 27.69 sparallel workers: index vacuum: 3 planned, 3 launched in total shows autovacuum used helpers for index vacuuming. The run took 27.69 s.
Step 10: Results
| Run | Parallel workers line in the log | Elapsed |
|---|---|---|
| PostgreSQL 18 autovacuum | none | 38.53 s |
| PostgreSQL 19, setting off | none | 36.21 s |
PostgreSQL 19, autovacuum_max_parallel_workers = 4 | 3 planned, 3 launched | 27.69 s |
These are single runs on 2-vCPU instances. With only two CPUs, the helpers compete for the same cores, and heap scanning and heap vacuuming are still single-threaded, so the gain is modest. The runs also differed in the amount of WAL written, so treat the numbers as an illustration, not a benchmark. Servers with more CPUs, faster storage and larger indexes should see a bigger difference.
Part C: Priority scores (PostgreSQL 19)
Step 11: Look at the scores
SELECT relname, score, xid_score, vacuum_score,
vacuum_insert_score, analyze_score,
do_vacuum, do_analyze, for_wraparound
FROM pg_stat_autovacuum_scores
ORDER BY score DESC
LIMIT 5;
relname | score | xid_score | vacuum_score | do_vacuum | do_analyze | for_wraparound
------------------+---------------------+-----------+--------------------+-----------+------------+----------------
pg_database | 0.1394422289564546 | 1.8e-07 | 0.0793650769622719 | f | f | f
pg_shdescription | 0.03992016089647368 | 1.95e-07 | 0 | f | f | f
pg_toast_1255 | 1.95e-07 | 1.95e-07 | 0 | f | f | fEach table gets a score, and autovacuum handles the highest scores first.
Step 12: Tune the weights
The five weights are server-level settings, not per-table ones:
ALTER SYSTEM SET autovacuum_vacuum_score_weight = 3.0;
SELECT pg_reload_conf();Reset it afterwards:
ALTER SYSTEM RESET autovacuum_vacuum_score_weight;
SELECT pg_reload_conf();Trying it per table fails on beta 4:
ALTER TABLE events SET (autovacuum_vacuum_score_weight = 3.0);
ERROR: unrecognized parameter "autovacuum_vacuum_score_weightHere is the youtube video:
When does parallel autovacuum help?
Good fit
- Big tables with several large indexes
- Write-heavy tables like audit logs and event streams
- Tables where autovacuum can’t keep up with dead-row generation
Little or no benefit
- Tables with a single index
- Small indexes (below min_parallel_index_scan_size)
- Vacuums where the heap scan is the bottleneck
As a rough starting point, many guides suggest leaving it at 0 for small databases, and trying 2 to 4 on larger ones with several big indexes. Your index count and CPU headroom matter more than database size.
Conclusion
PostgreSQL 19 (beta 4) closes two long-standing gaps in autovacuum:
1. Parallel autovacuum. Autovacuum workers can now use helper workers for index vacuuming and index cleanup, something manual VACUUM (PARALLEL N) has offered since version 13. It’s opt-in: autovacuum_max_parallel_workers defaults to 0, so nothing changes until you enable it. In the test, setting it to 4 cut the run from about 36 s to 27.7 s (versus 38.5 s on PostgreSQL 18). The gain was modest because the test ran on 2 vCPUs, and heap scanning and heap vacuuming are still single-threaded. These were single runs, so treat them as an illustration, not a benchmark.
2. Priority-based table selection. Instead of working through tables in pg_class order, autovacuum now scores each table, takes the highest of five components, and handles the most urgent first (wraparound risk included). The pg_stat_autovacuum_scores view makes the scores visible, and five weight settings let you tune the ordering. Setting all weights to 0.0 restores the old behavior. On beta 4, the weights are server-level only, not per-table.
Practical takeaways
- Parallel autovacuum helps most on large, write-heavy tables with several big indexes, and on systems where autovacuum can’t keep up with dead-row generation.
- It does little for single-index tables, small indexes (below
min_parallel_index_scan_size), or vacuums bottlenecked on the heap scan. - Watch memory: parallel workers add usage, drawing on
autovacuum_work_memormaintenance_work_mem. - A reasonable starting point is to leave it at 0 for small databases and try 2 to 4 on larger ones, based on index count and available CPU headroom.
- Test on your own workload first. These results come from a beta release, and behavior or defaults could still change before the final release.
Overall, PostgreSQL 19 makes autovacuum both faster on index-heavy tables and smarter about what to clean first, with low risk since the parallel feature is off by default.
Thank you for reading and see you tomorrow with Day4!!