Re: POC: Parallel processing of indexes in autovacuum

Hi,

On Tue, Mar 17, 2026 at 11:51 PM Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
>
> 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).
>

Actually we have several places in the code where "-1" means disabled and "0"
means choosing a parallel degree based on the number of indexes. Since this
is an inner logic, I agree that we should make our parameter more intuitive
to the user. But this will make the code a bit confusing.

> 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?

I'm afraid that I can't agree with you here. As I wrote above [1], the
parallel a/v feature will be useful when a user has a few huge tables with
a big amount of indexes. Only these tables require parallel processing and a
user knows about it.

If we implement the feature as you suggested, then after setting the
av_max_parallel_workers to N > 0, the user will have to manually disable
processing for all tables except the largest ones. This will need to be done
to ensure that parallel workers are launched specifically to process the
largest tables and not wasting on the processing of little ones.

I.e. I'm proposing a design that will require manual actions to *enable*
parallel a/v for several large tables rather than *disable* it for all of
the rest tables in the cluster. I'm sure that's what users want.

Allowing the system to decide which tables to process in parallel is a good
way from a design perspective. But I'm thinking of the following example :
Imagine that we have a threshold, when exceeded, parallel a/v is used.
Several a/v workers encounter tables which exceed this threshold by 1_000 and
each of these workers decides to launch a few parallel workers. Another a/v
worker encounters a table which is beyond this threshold by 1_000_000 and
tries to launch N parallel workers, but facing the max_parallel_workers
shortage. Thus, processing of this table will take a very long time to
complete due to lack of resources. The only way for users to avoid it is to
disable parallel a/v for all tables, which exceeds the threshold and are not
of particular interest.

I cannot imagine how our heuristics can handle such situations. IMHO the
situation will come down to the fact that users will manually disable
parallel a/v for a big amount of tables. I guess it can be pretty frustrating.

What do you think?

>
> +{ 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."?

I'm not sure if this phrase will be understandable to the user.
I don't see any places where we would define the "autovacuum operation"
concept, so I suppose it could be ambiguous. What about "Maximum number of
parallel processes per autovacuuming of one table"?

>
> The maximum value should be MAX_PARALLEL_WORKER_LIMIT.
>

Sure!

>
> 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.
>

I agree with all proposed fixes. Thank you!

>
> + 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.
>

OK, I agree.

> 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.
>

I agree with proposed changes. BTW, don't we need to reduce the strings
length to 80 characters in the tests? In some tests, this rule is followed,
and in some it is not.

--
Thank you very much for the review and proposed patches!
Please, see an updated set of patches. Note that the "logging for autovacuum"
is considered as the first patch now.

[1] https://www.postgresql.org/message-id/CAJDiXghaazbrQMZZS08d9Ffh2y4w05TgH9dpBhqChv1qNTp%2BxA%40mail.gmail.com

--
Best regards,
Daniil Davydov