Re: POC: Parallel processing of indexes in autovacuum - Mailing list pgsql-hackers

Hi,

On Wed, Apr 1, 2026 at 4:20 AM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
>
> On Tue, Mar 31, 2026 at 7:18 AM Daniil Davydov <3danissimo@gmail.com> wrote:
> >
> > > +    <command>VACUUM</command> can perform index vacuuming and index cleanup
> > Don't we need to mention autovacuum here too? I thought that VACUUM in the
> > context means "manual VACUUM command".
>
> I think that the documentation explains that the autovacuum daemon is
> a worker automatically executing VACUUM and ANALYZE commands.
>

Yeah, that's true. Then I agree with this change.

>
> > He confirmed that as a rule there are *hundreds of thousands* of tables in the
> > system, the vast majority of which do not need to be vacuumed in parallel mode.
>
> I'm still struggling to see the technical justification; why would a
> user want to avoid parallel vacuuming on eligible tables if they have
> already explicitly allowed the system to use more resources by setting
> autovacuum_max_parallel_workers to >0?

Here I am talking about "introductory data". I.e. the situation that the user
has before considering our parameter usage. Based on this situation, it seems
to me that not everyone will want to turn on parallel a/v (because of resource
shortage hazard).

> If resource contention occurs,
> it is typically a sign that the global parameters need re-tuning. As I
> mentioned, the same contention can occur even with an opt-in style if
> multiple tables are manually configured.
>

Yep, we already discussed it and I agree with you. I think that in the case of
opt-in style the resource contention will be much more controlled. But actually
the opt-in style in the form in which I originally proposed it, no longer seems
like a good idea to me. Classic opt-in style will deprive us of support for
half of the parallel a/v use cases. Anton's proposal seems to me like a good
balance between the two styles.

> > He also suggested the following : let the reloption overlap the value of the
> > GUC parameter. I.e. even if av_max_parallel_workers parameters is 0 the user
> > still can set the av_parallel_workers to 10 for some table, and autovacuum
> > will process this table in parallel.
> >
> > I remember that you want to use the GUC parameter as a global switch, and this
> > approach will break this logic. But according to Anton's words, it is okay if
> > the GUC parameter cannot disable parallel a/v for all tables instantly. It will
> > become an administrator's responsibility to manually turn off parallel a/v for
> > several tables (again, it is completely OK). Thus, this feature can be handy
> > for all use cases.
>
> While some autovacuum parameters do override GUCs, those are typically
> local to the process (like cost delay). Parallel workers, however, are
> a shared system-wide resource. In a multi-tenant environment, allowing
> a single table's reloption to bypass the
> autovacuum_max_parallel_workers = 0 limit could lead to unexpected
> exhaustion of the worker pool.

Will this exhaustion really be unexpected? If we describe such an ability in
the documentation, and the user uses it, then everything is fair. Even if
administrator forgets that he enabled av_parallel_workers reloption somewhere,
then he can :
1)
Check the logfile (if log level is not too high) searching for logs like
"parallel workers: index vacuum: N planned, N launched in total".
2)
Run a query that selects all tables which have av_parallel_workers > 0.

>I think that this GUC should act as a
> reliable global switch for resource management.

I agree that the "global switch" is an attractive idea and we should strive
for it. But our parameter *can* play the role of the switch if users don't
manually touch the av_parallel_workers reloption. But if they do - well, it is
their responsibility to turn the reloption off.

>
> > I hope it doesn't look like as an adapting to the needs of a specific user.
> > A lot of super-large productions are migrating to postgres now, and I believe
> > that we should ensure their comfort too.
>
> I'm not prioritizing one specific use case over another. I believe
> that there are also users who want to use parallel vacuum on hundreds
> of thousands of tables. We should consider a better solution while
> checking it from multiple perspectives such as the usability, the
> robustness and consistency with the existing features and behaviors
> etc.

For those users who want to use parallel a/v for hundreds of thousands of
tables we have the default value "-1" which allows parallel a/v everywhere via
GUC parameter manipulation.

For those users who want to parallel a/v on several specific tables we can
allow setting reloption that will override the GUC.

I guess that the question is : "Is it normal if the GUC parameter will lose
ability to turn off parallel a/v everywhere after the user has manually raised
the value for the av_parallel_workers reloption on a few tables?". If the
answer is "Yes", I don't see any obstacles for us to allow overriding the GUC
parameter via reloption.


Thank you very much for your comments!
Please, see an updated patch.

--
Best regards,
Daniil Davydov