Re: POC: Parallel processing of indexes in autovacuum

Hi,

On Thu, Mar 12, 2026 at 2:05 AM Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
>
> BTW thes discussion made me think to change av_max_parallel_workers to
> control the number of workers per-autovacuum worker instead (with
> renaming it to say max_parallel_workers_per_autovacuum_worker). Users
> can compute the maximum number of parallel workers the system requires
> by (autovacuum_worker_slots *
> max_parallel_workers_per_autovacuum_worker). We would no longer need
> the reservation and release logic. I'd like to hear your opinion.
>

IIUC, one of the main autovacuum's goals is to be "inconspicuous" for the
rest of the system. I mean that it should not try to vacuum all the tables
as fast as possible. Instead it should try to interfere with other backends
as little as possible and try to avoid high resource consumption (assuming
there is no hazard of wraparound).

I propose to reason based on the case for which the parallel a/v will
actually be used :
We have a 3 tables which has 80+ indexes each and require a
parallel a/v. Ideally, each of these tables should be processed with 20
parallel workers. This is a real example which can be encountered in
different productions, where such tables take up about half of all the data
in the database.

How parallel a/v will handle such a situation?
1. Our current implementation
We can set av_max_parallel_workers to 60 and autovacuum_parallel_workers
reloption to 20 for each table.
2. Proposed idea
We can set max_parallel_workers_per_av_worker to 20 and
autovacuum_parallel_workers reloption to 20 for each table.

In both cases we have guarantee that all tables will be processed with the
desired number of parallel workers. And both cases allows us to limit the
CPU consumption via reducing the "av_max_parallel_workers" parameter (for
current implementation) or via reducing the "autovacuum_parallel_workers"
reloption for each table (for proposed idea). So basically I don't see whether
current approach has a big advantages over the idea you proposed.

I also asked my friend, who is many years working with the clients with big
productions. He said that this is super important to process such huge tables
with maximum "intensity". I.e. each a/v worker should have ability to launch
as many parallel workers as required. I guess that this is an argument in
favor of your idea.

The only argument against this idea that I could come up with is that some
users may abuse our parallel a/v feature. For instance, the user can set
"autovacuum_parallel_workers" reloption not only for large tables, but also
for many smaller ones. In this case the max_parallel_workers_per_av_worker
must be pretty large (in order to process the huge table). Thus, the user
can face a situation when all a/v workers are launching additional parallel
workers => there is high CPU consumption and possibly max_parallel_workers
shortage. The only way to deal with it is to go through a large amount of
smaller tables and reduce "autovacuum_parallel_workers" reloption for each
of them. IMHO, this is a pretty unpleasant experience for the user. On the
other hand, the user himself is to blame for the occurrence of such a
situation.

Let's summarize.
Proposed idea has several strong advantages over current implementation.
The only disadvantage I came up with can be avoided by writing recommendations
on how to use this feature in the documentation. So, if I didn't messed up
anything and you don't have any doubts, I would rather implement the
proposed idea.

> > 2)
> > I suggest adding a separate log that will be emitted every time we are
> > unable to start workers due to a shortage of av_max_parallel_workers.
>
> For (2), do you mean that the worker writes these logs regardless of
> log_autovacuum_min_duration setting? I'm concerned that the server
> logs would be flooded with these logs especially when multiple
> autovacuum workers are working very actively and the system is facing
> a shortage of av_max_parallel_workers.

Oh, I didn't take that into account. But this is not a problem - we can
accumulate such statistics just as we do now for the "nreserved" ones. And
then we will log this value with all other stats.

> > Possibly we can introduce a new injection point, or a new log for it.
> > But I assume that the subject of discussion in patch 0002 is the
> > "nreserved" logic, and "nlaunched/nplanned" logic does not raise any
> > questions.
> >
> > I suggest splitting the 0002 patch into two parts : 1) basic logic and
> > 2) additional logic with nreserved or something else. The second part can be
> > discussed in isolation from the patch set. If we do this, we may not have to
> > change the tests. What do you think?
>
> Assuming the basic logic means nlaunched/nplanned logic, yes, it would
> be a nice idea. I think user-facing logging stuff can be developed as
> an improvement independent from the main parallel autovacuum patch.
> It's ideal if we can implement the main patch (with tests) without
> relying on the user-facing logging.

OK, actually we can do it.

Thank you very much for the review!
Please, see attached patches. The changes are :
1) Fixed segfault with accessing outdated pv_shared_cost_params pointer.
2) "Logging for autovacuum" is divided into two patches - basic logging
(nplanned/nlaunched) and advanced logging (nreserved).
3) Tests are now independent of logging.

By now I didn't try to change the core logic. I think that first we need to
agree on the use of the new GUC parameter.

--
Best regards,
Daniil Davydov