Re: POC: Parallel processing of indexes in autovacuum

On Wed, Apr 1, 2026 at 12:44 AM Daniil Davydov <3danissimo(at)gmail(dot)com> wrote:
> >
> > > 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 :

How can DBAs prevent parallel workers from being exhaustly used if
users set a high value to the reloption?

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

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

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.

Regards,

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