DEV Community

Vahid Yousefzadeh
Vahid Yousefzadeh

Posted on

23% Faster Autovacuum with Parallel Workers in PostgreSQL 19

#postgres #autovacuum #parallel #performance

Before PostgreSQL 19, autovacuum processed indexes without using parallel vacuum workers, even when a table had multiple large indexes. Although PostgreSQL already supported parallel vacuuming for manually executed VACUUM commands, this capability was not available to autovacuum.

For example, manual vacuuming can use parallel workers:

vahiddb=# VACUUM (PARALLEL 10) person;
VACUUM
Enter fullscreen mode Exit fullscreen mode

PostgreSQL 19 introduces Parallel Autovacuum, allowing autovacuum to use parallel workers during index vacuuming and index cleanup.

This feature is particularly useful for tables with large indexes and substantial numbers of dead tuples. However, it does not parallelize the table’s heap-scanning phase. Consequently, tables without indexes cannot benefit from this feature, and tables with large indexes are promising candidates.

In this article, I will demonstrate the feature using a table containing five million rows and four indexes. I will compare the execution times of autovacuum with parallel workers disabled and enabled.

1. Inspecting the test table

My test environment contains a table named person in the vahiddb database.

First, I checked the number of rows:

vahiddb=# select count(*) from person;
  count
---------
 5000000
(1 row)
Enter fullscreen mode Exit fullscreen mode

The table contains five million rows and has four indexes.

I used the following command to check their sizes:

vahiddb=# \dt+
                                     List of tables
 Schema |  Name  | Type  |  Owner   | Persistence | Access method |  Size  | Description
--------+--------+-------+----------+-------------+---------------+--------+-------------
 public | person | table | postgres | permanent   | heap          | 403 MB |
(1 row)
Enter fullscreen mode Exit fullscreen mode 
vahiddb=# \di+
                                               List of indexes
 Schema |        Name        | Type  |  Owner   | Table  | Persistence | Access method |  Size  | Description
--------+--------------------+-------+----------+--------+-------------+---------------+--------+-------------
 public | person_flname_idx  | index | postgres | person | permanent   | btree         | 41 MB  |
 public | person_full_idx    | index | postgres | person | permanent   | btree         | 366 MB |
 public | person_lfaname_idx | index | postgres | person | permanent   | btree         | 324 MB |
 public | person_pk          | index | postgres | person | permanent   | btree         | 107 MB |
(4 rows)
Enter fullscreen mode Exit fullscreen mode

The four indexes occupy approximately 838 MB in total, which is more than twice the size of the table.

This workload is a good candidate for testing parallel autovacuum because index processing represents a substantial part of the maintenance work.

2. Configuring autovacuum logging

To measure the execution time, I enabled detailed autovacuum logging.

I set log_autovacuum_min_duration to 0 so that every autovacuum operation would be logged:

vahiddb=# ALTER SYSTEM SET log_autovacuum_min_duration = 0;
ALTER SYSTEM
vahiddb=# SELECT pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)
Enter fullscreen mode Exit fullscreen mode

3. Measuring autovacuum without parallel workers

In PostgreSQL 19, the autovacuum_max_parallel_workers parameter controls the maximum number of parallel workers that a single autovacuum worker can use.

Setting it to zero disables parallel vacuuming during autovacuum.

vahiddb=# ALTER SYSTEM SET autovacuum_max_parallel_workers = 0;
ALTER SYSTEM

vahiddb=# SELECT pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)
Enter fullscreen mode Exit fullscreen mode

Next, I updated three million rows to generate dead tuples and trigger autovacuum:

vahiddb=# UPDATE person
SET
    fname = 'Vahid' || fname,
    lname = 'Yousefzadeh' || lname
        where id<=3000000;

UPDATE 3000000
Enter fullscreen mode Exit fullscreen mode

After autovacuum completed, I examined the server log. The following excerpt contains the most relevant results:

2026-10-18 19:45:55.201 +03 [2323] LOG:  automatic vacuum of table "vahiddb.public.person": index scans: 1
        pages: 0 removed, 88581 remain, 67964 scanned (76.73% of total), 0 eagerly scanned
        tuples: 3000000 removed, 5000070 remain, 0 are dead but not yet removable
        removable cutoff: 771, which was 0 XIDs old when operation ended
        frozen: 37038 pages from table (41.81% of total) had 3000058 tuples frozen
        visibility map: 67964 pages set all-visible, 67964 pages set all-frozen (0 were all-visible)
        index scan needed: 30927 pages from table (34.91% of total) had 3000000 dead item identifiers removed
        index "person_pk": pages: 21938 in total, 0 newly deleted, 0 currently deleted, 0 reusable
        index "person_flname_idx": pages: 11218 in total, 0 newly deleted, 0 currently deleted, 0 reusable
        index "person_lfaname_idx": pages: 72221 in total, 0 newly deleted, 0 currently deleted, 0 reusable
        index "person_full_idx": pages: 103264 in total, 0 newly deleted, 0 currently deleted, 0 reusable
        avg read rate: 16.980 MB/s, avg write rate: 14.303 MB/s
        buffer usage: 84022 hits, 291632 reads, 245655 dirtied
        WAL usage: 313620 records, 245655 full page images, 1048438885 bytes, 1018664200 full page image bytes, 98 buffers full
        memory usage: dead item storage 2.33 MB accumulated across 1 reset (limit 64.00 MB each)
        system usage: CPU: user: 6.97 s, system: 1.77 s, elapsed: 134.18 s
Enter fullscreen mode Exit fullscreen mode

The most important measurement is the elapsed time reported by PostgreSQL:

elapsed: 134.18 s
Enter fullscreen mode Exit fullscreen mode

This is our baseline for comparison.

4. Enabling Parallel Autovacuum

Now I enabled parallel autovacuum by setting the maximum number of parallel workers to four:

vahiddb=# ALTER SYSTEM SET autovacuum_max_parallel_workers = 4;
ALTER SYSTEM

vahiddb=# SELECT pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)
Enter fullscreen mode Exit fullscreen mode

A configuration reload is sufficient for this setting; a server restart is not required.

PostgreSQL 19 also supports a table-level setting named . This parameter allows us to configure the maximum number of parallel workers for an individual table.

For the person table, I used:

vahiddb=# ALTER TABLE person SET (
    autovacuum_enabled = true,
    autovacuum_parallel_workers = 4
);

ALTER TABLE
Enter fullscreen mode Exit fullscreen mode

The effective number of workers is subject to the configured limits and available worker capacity. Setting a value of four does not guarantee that four workers will always be launched.

Resource considerations
Parallel autovacuum should be configured carefully on production systems.

The relevant settings include:

· autovacuum_max_parallel_workers: maximum parallel workers per autovacuum operation.

· autovacuum_parallel_workers: table-level parallel-worker limit.

· max_parallel_workers: overall limit for parallel workers.

· autovacuum_work_mem: memory available to each autovacuum worker, subject to its configuration and applicable limits.

Increasing parallelism can increase CPU and I/O demand. Although parallel vacuum operations apply maintenance memory limits differently from parallel queries, administrators should still account for concurrent maintenance operations and the memory requirements of the cluster.

For this test, I used four as the configured maximum and allowed PostgreSQL to determine the actual worker count.

5. Repeating the test with Parallel Autovacuum

I repeated the same update against the person table:

vahiddb=# UPDATE person
SET
    fname = 'Vahid' || fname,
    lname = 'Yousefzadeh' || lname
        where id<=3000000;

  UPDATE 3000000
Enter fullscreen mode Exit fullscreen mode

The resulting autovacuum log included the following information:

2026-10-18 19:33:49.737 +03 [2273] LOG:  automatic vacuum of table "vahiddb.public.person": index scans: 1
        pages: 0 removed, 88581 remain, 67964 scanned (76.73% of total), 0 eagerly scanned
        tuples: 3000000 removed, 5000046 remain, 0 are dead but not yet removable
        removable cutoff: 762, which was 0 XIDs old when operation ended
        new relfrozenxid: 762, which is 6 XIDs ahead of previous value
        frozen: 37037 pages from table (41.81% of total) had 3000000 tuples frozen
        visibility map: 67964 pages set all-visible, 67964 pages set all-frozen (0 were all-visible)
        index scan needed: 30927 pages from table (34.91% of total) had 3000000 dead item identifiers removed
        parallel workers: index vacuum: 3 planned, 3 launched in total
        index "person_pk": pages: 21938 in total, 0 newly deleted, 0 currently deleted, 0 reusable
        index "person_flname_idx": pages: 11218 in total, 0 newly deleted, 0 currently deleted, 0 reusable
        index "person_lfaname_idx": pages: 72221 in total, 0 newly deleted, 0 currently deleted, 0 reusable
        index "person_full_idx": pages: 103264 in total, 0 newly deleted, 0 currently deleted, 0 reusable
        avg read rate: 22.357 MB/s, avg write rate: 18.781 MB/s
        buffer usage: 83274 hits, 292440 reads, 245655 dirtied
        WAL usage: 313620 records, 245655 full page images, 1048438901 bytes, 1018664348 full page image bytes, 139 buffers full
        memory usage: dead item storage 3.00 MB accumulated across 1 reset (limit 64.00 MB each)
        system usage: CPU: user: 4.50 s, system: 0.93 s, elapsed: 102.18 s
Enter fullscreen mode Exit fullscreen mode

This is the line that explicitly confirms parallel vacuum execution:

parallel workers: index vacuum: 3 planned, 3 launched in total
PostgreSQL planned three parallel index-vacuum workers and successfully launched all three. The elapsed time was:

elapsed: 102.18 s
Enter fullscreen mode Exit fullscreen mode

That represents an approximately 23.9% reduction in elapsed time in this particular experiment.

The key takeaway is that Parallel Autovacuum can improve maintenance performance for tables with large indexes, but the actual benefit depends on the workload, index sizes, available resources.

Top comments (0)