Re: POC: Parallel processing of indexes in autovacuum

Hi,

On Tue, Apr 7, 2026 at 10:49 AM Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
>
> > >
> > > 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 :
>
> How can DBAs prevent parallel workers from being exhaustly used if
> users set a high value to the reloption?
>

Only manual control. Since DBA increased reloption manually, it is OK to ask
him to manually decrease it.

> > 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.
>
> Does that mean DBAs would need to run these queries periodically?

Not really. I say that even if DBA has lost control on the parallel a/v
workers, it has an ability to find these bottlenecks.

> I don't think that in a multi-tenant environment, DBAs can (or should)
> execute ALTER TABLE on user-owned tables just to fix resource issues.
>

Well, the people I talked to had a different opinion which is based on clients
feedback : what is acceptable and what is not. I don't think that we can
convince each other, so let it be as it is :)

But if you don't mind continuing to discuss this topic (perhaps with the
involvement of other people), I would love to create a new thread for it.

> > 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.
>
> I think the answer is no, particularly for this parameter. Since it
> controls a system-wide shared resource, it should be capped by a GUC
> to ensure centralized management, consistent with other
> parallel-query-related GUCs and reloptions.

OK. I believe that "global switch" will also be pretty handy for many use cases.

> Thank you! Pushed.

Great news! Thank you very much for your help, Masahiko-san!

--
Best regards,
Daniil Davydov