Re: POC: Parallel processing of indexes in autovacuum

On Tue, Apr 7, 2026 at 6:32 AM Daniil Davydov <3danissimo(at)gmail(dot)com> wrote:
>
> 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.

In multi-tenant environments, the roles of table owners and DBAs are
often separated. Tenants can freely set reloptions via ALTER TABLE,
but a DBA cannot easily revert those settings on user-owned tables.
Even if a DBA tries to use ALTER TABLE to fix a misconfigured
reloption, it would cancel any currently running autovacuum on that
table. Furthermore, if the table is undergoing an anti-wraparound
vacuum, the ALTER TABLE command itself will be blocked, making it
impossible to resolve a resource crisis quickly. If a single tenant
could exhaust the entire parallel worker pool by setting a high
reloption value, the DBA would have no effective way to prevent or
mitigate it under an override model.

While I understand the use case for enabling parallel vacuum only on
specific tables, this is already achievable under the cap model (by
setting a global GUC > 0 and using the reloption to disable it on
others), even if the initial configuration is more tedious.

Also, I'm concerned that the override behavior would be inconsistent
with other parallel-query-related features. While some autovacuum
reloptions (like autovacuum_vacuum_scale_factor) do override GUCs,
those parameters only affect the local behavior of that specific
process and do not impact shared system-wide resources. In contrast,
autovacuum_max_parallel_workers takes workers from the
max_parallel_workers pool. Allowing a single table to monopolize this
shared pool by bypassing the GUC cap creates a significant risk that
cannot be easily managed.

While this isn't exclusively about multi-tenancy, I think that we
cannot simply introduce a behavior that creates such a high risk for
system-wide resource exhaustion.

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

Okay.

Regards,

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