Re: POC: Parallel processing of indexes in autovacuum

On Mon, Mar 16, 2026 at 1:54 PM Daniil Davydov <3danissimo(at)gmail(dot)com> wrote:
>
> Hi,
>
> On Mon, Mar 16, 2026 at 11:46 PM Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
> >
> > While both ideas can achieve our goal of this feature in general, the
> > new idea doesn't require an additional layer of reserve/release logic
> > on top of the existing bgworker pool, which is good. I've not tried
> > coding this idea but I believe the patch can be simplified very much.
> > So I agree to move to this idea.
> >
>
> OK, let's do it!
>
> Please, see an updated set of patches. Main changes are :
> 0001 patch - removed all logic related to the parallel workers reserving.
> 0002 patch - no changes regarding v26.
> 0003 patch - no changes regarding v26.
> 0004 patch - removed all stuff related to the "test_autovacuum" extension.
> Also removed 3th, 4th and 5th tests, because they were related
> only to the workers reserving logic.
> 0005 patch - minor changes reflecting the new GUC parameter's purpose.
>
> I have maintained the independence of the tests from the user-facing logging.
> Instead of "nworkers released" logs I have added a single log at the end of
> one round of parallel processing :
> "av worker: finished parallel index processing with N parallel workers".
> This is the only code that I added rather than deleted within the 0001 patch.
>
> I hope I didn't miss anything.

Thank you for updating the patch!

I find the current behavior of the autovacuum_parallel_workers storage
parameter somewhat unintuitive for users. The documentation currently
states:

+ <para>
+ Sets the maximum number of parallel autovacuum workers that can process
+ indexes of this table.
+ The default value is -1, which means no parallel index vacuuming for
+ this table. If value is 0 then parallel degree will computed based on
+ number of indexes.
+ Note that the computed number of workers may not actually be available at
+ run time. If this occurs, the autovacuum will run with fewer workers
+ than expected.
+ </para>

It is quite confusing that setting the value to 0 does not actually
disable the parallel vacuum. In many other PostgreSQL parameters, 0
typically means "off" or "no workers." I think that this parameter
should behave as follows:

-1: Use the value of autovacuum_max_parallel_workers (GUC) as the
limit (fallback).
>=0: Use the specified value as the limit, capped by autovacuum_max_parallel_workers. (Specifically, setting this to 0 would disable parallel vacuum for the table).

Currently, the patch implements parallel autovacuum as an "opt-in"
style. That is, even after setting the GUC to >0, users must manually
set the storage parameter for each table. This assumes that users
already know exactly which tables need parallel vacuum.

However, I believe it would be more intuitive to let the system decide
which tables are eligible for parallel vacuum based on index size and
count (via min_parallel_index_scan_size, etc.), rather than forcing
manual per-table configuration. Therefore, I'm thinking we might want
to make it "opt-out" style by default instead:

- Set the default value of the storage parameter to -1 (i.e., fallback to GUC).
- the default value of the GUC autovacuum_max_parallel_workers at 0.

With this configuration:

- Parallel autovacuum is disabled by default.
- Users can enable it globally by simply setting the GUC to >0.
- Users can still disable it for specific tables by setting the
storage parameter to 0.

What do you think?

* 0001 patch

+{ name => 'autovacuum_max_parallel_workers', type => 'int', context
=> 'PGC_SIGHUP', group => 'VACUUM_AUTOVACUUM',
+ short_desc => 'Maximum number of parallel workers that a single
autovacuum worker can take from bgworkers pool.',
+ variable => 'autovacuum_max_parallel_workers',
+ boot_val => '2',
+ min => '0',
+ max => 'MAX_BACKENDS',
+},

How about rephrasing the short description to "Maximum number of
parallel processes per autovacuum operation."?

The maximum value should be MAX_PARALLEL_WORKER_LIMIT.

---
* 0002 patch:

I think that it's better to rename PVWorkersStats and PVWorkersUsage
to PVWorkerStats and PVWorkerUsage (making Worker singular).

I've attached the patch for minor fixes including the above comments.

---
* 0004 patch:

+ if (AmAutoVacuumWorkerProcess())
+ elog(DEBUG2,
+ ngettext("autovacuum worker: finished
parallel index processing with %d parallel worker",
+ "autovacuum worker:
finished parallel index processing with %d parallel workers",
+ nworkers),
+ nworkers);

Now that having planned and launched logs in autovacuum logs is
straightforward, let's use these logs in the tests instead and make it
the first patch. We can apply it independently.

---
We check only the server logs throughout the new tap tests. I think we
should also confirm that the autovacuum successfully completes. I've
attached the proposed change to the tap tests.

The attached 0003 and 0006 patches are fixup changes on top v27. Other
patches don't have any change from the previous v27 patch set.

Regards,

--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com