Re: POC: Parallel processing of indexes in autovacuum

On Thu, May 22, 2025 at 12:44 AM Daniil Davydov <3danissimo(at)gmail(dot)com> wrote:
>
> Hi,
>
> On Wed, May 21, 2025 at 5:30 AM Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
> >
> > I have some comments on v2-0001 patch
>
> Thank you for reviewing this patch!
>
> > + {
> > + {"autovacuum_reserved_workers_num", PGC_USERSET,
> > RESOURCES_WORKER_PROCESSES,
> > + gettext_noop("Number of worker processes, reserved for
> > participation in parallel index processing during autovacuum."),
> > + gettext_noop("This parameter is depending on
> > \"max_worker_processes\" (not on \"autovacuum_max_workers\"). "
> > + "*Only* autovacuum workers can use these
> > additional processes. "
> > + "Also, these processes are taken into account
> > in \"max_parallel_workers\"."),
> > + },
> > + &av_reserved_workers_num,
> > + 0, 0, MAX_BACKENDS,
> > + check_autovacuum_reserved_workers_num, NULL, NULL
> > + },
> >
> > I find that the name "autovacuum_reserved_workers_num" is generic. It
> > would be better to have a more specific name for parallel vacuum such
> > as autovacuum_max_parallel_workers. This parameter is related to
> > neither autovacuum_worker_slots nor autovacuum_max_workers, which
> > seems fine to me. Also, max_parallel_maintenance_workers doesn't
> > affect this parameter.
> > .......
> > I've also considered some alternative names. If we were to use
> > parallel_maintenance_workers, it sounds like it controls the parallel
> > degree for all operations using max_parallel_maintenance_workers,
> > including CREATE INDEX. Similarly, vacuum_parallel_workers could be
> > interpreted as affecting both autovacuum and manual VACUUM commands,
> > suggesting that when users run "VACUUM (PARALLEL) t", the system would
> > use their specified value for the parallel degree. I prefer
> > autovacuum_parallel_workers or vacuum_parallel_workers.
> >
>
> This was my headache when I created names for variables. Autovacuum
> initially implies parallelism, because we have several parallel a/v
> workers.

I'm not sure if it's parallelism. We can have multiple autovacuum
workers simultaneously working on different tables, which seems not
parallelism to me.

> So I think that parameter like
> `autovacuum_max_parallel_workers` will confuse somebody.
> If we want to have a more specific name, I would prefer
> `max_parallel_index_autovacuum_workers`.

It's better not to use 'index' as we're trying to extend parallel
vacuum to heap scanning/vacuuming as well[1].

>
> > + /*
> > + * If we are running autovacuum - decide whether we need to process indexes
> > + * of table with given oid in parallel.
> > + */
> > + if (AmAutoVacuumWorkerProcess() &&
> > + params->index_cleanup != VACOPTVALUE_DISABLED &&
> > + RelationAllowsParallelIdxAutovac(rel))
> >
> > I think that this should be done in autovacuum code.
>
> We need params->index cleanup variable to decide whether we need to
> use parallel index a/v. In autovacuum.c we have this code :
> ***
> /*
> * index_cleanup and truncate are unspecified at first in autovacuum.
> * They will be filled in with usable values using their reloptions
> * (or reloption defaults) later.
> */
> tab->at_params.index_cleanup = VACOPTVALUE_UNSPECIFIED;
> tab->at_params.truncate = VACOPTVALUE_UNSPECIFIED;
> ***
> This variable is filled in inside the `vacuum_rel` function, so I
> think we should keep the above logic in vacuum.c.

I guess that we can specify the parallel degree even if index_cleanup
is still UNSPECIFIED. vacuum_rel() would then decide whether to use
index vacuuming and vacuumlazy.c would decide whether to use parallel
vacuum based on the specified parallel degree and index_cleanup value.

>
> > +#define AV_PARALLEL_DEADTUP_THRESHOLD 1024
> >
> > These fixed values really useful in common cases? I think we already
> > have an optimization where we skip vacuum indexes if the table has
> > fewer dead tuples (see BYPASS_THRESHOLD_PAGES).
>
> When we allocate dead items (and optionally init parallel autocuum) we
> don't have sane value for `vacrel->lpdead_item_pages` (which should be
> compared with BYPASS_THRESHOLD_PAGES).
> The only criterion that we can focus on is the number of dead tuples
> indicated in the PgStat_StatTabEntry.

My point is that this criterion might not be useful. We have the
bypass optimization for index vacuuming and having many dead tuples
doesn't necessarily mean index vacuuming taking a long time. For
example, even if the table has a few dead tuples, index vacuuming
could take a very long time and parallel index vacuuming would help
the situation, if the table is very large and has many indexes.

>
> ----
>
> > I guess we can implement this parameter as an integer parameter so
> > that the user can specify the number of parallel vacuum workers for
> > the table. For example, we can have a reloption
> > autovacuum_parallel_workers. Setting 0 (by default) means to disable
> > parallel vacuum during autovacuum, and setting special value -1 means
> > to let PostgreSQL calculate the parallel degree for the table (same as
> > the default VACUUM command behavior).
> > ...........
> > The patch includes the changes to bgworker.c so that we can reserve
> > some slots for autovacuums. I guess that this change is not
> > necessarily necessary because if the user sets the related GUC
> > parameters correctly the autovacuum workers can use parallel vacuum as
> > expected. Even if we need this change, I would suggest implementing
> > it as a separate patch.
> > ..........
> > +#define AV_PARALLEL_DEADTUP_THRESHOLD 1024
> > +#define NUM_INDEXES_PER_PARALLEL_WORKER 30
> >
> > These fixed values really useful in common cases? Given that we rely on
> > users' heuristics which table needs to use parallel vacuum during
> > autovacuum, I think we don't need to apply these conditions.
> > ..........
>
> I grouped these comments together, because they all relate to a single
> question : how much freedom will we give to the user?
> Your opinion (as far as I understand) is that we allow users to
> specify any number of parallel workers for tables, and it is the
> user's responsibility to configure appropriate GUC variables, so that
> autovacuum can always process indexes in parallel.
> And we don't need to think about thresholds. Even if the table has a
> small number of indexes and dead rows - if the user specified table
> option, we must do a parallel index a/v with requested number of
> parallel workers.
> Please correct me if I messed something up.
>
> I think that this logic is well suited for the `VACUUM (PARALLEL)` sql
> command, which is manually called by the user.

The current idea that users can use parallel vacuum on particular
tables based on their heuristic makes sense to me as the first
implementation.

> But autovacuum (as I think) should work as stable as possible and
> `unnoticed` by other processes. Thus, we must :
> 1) Compute resources (such as the number of parallel workers for a
> single table's indexes vacuuming) as efficiently as possible.
> 2) Provide a guarantee that as many tables as possible (among
> requested) will be processed in parallel.

I think these ideas could be implemented on top of the current idea.

> (1) can be achieved by calculating the parameters on the fly.
> NUM_INDEXES_PER_PARALLEL_WORKER is a rough mock. I can provide more
> accurate value in the near future.

I think it requires more things than the number of indexes on the
table to achieve (1). Suppose that there is a very large table that
gets updates heavily and has a few indexes. If users want to avoid the
table from being bloated, it would be a reasonable idea to use
parallel vacuum during autovacuum and it would not be a good idea to
disallow using parallel vacuum solely because it doesn't have more
than 30 indexes. On the other hand, if the table had got many updates
but not so now, users might want to use resources for autovacuums on
other tables. We might need to consider autovacuum frequencies per
table, the statistics of the previous autovacuum, or system loads etc.
So I think that in order to achieve (1) we might need more statistics
and using only NUM_INDEXES_PER_PARALLEL_WORKER would not work fine.

> (2) can be achieved by workers reserving - we know that N workers
> (from bgworkers pool) are *always* at our disposal. And when we use
> such workers we are not dependent on other operations in the cluster
> and we don't interfere with other operations by taking resources away
> from them.

Reserving some bgworkers for autovacuum could make sense. But I think
it's better to implement it in a general way as it could be useful in
other use cases too. That is, it might be a good to implement
infrastructure so that any PostgreSQL code (possibly including
extensions) can request allocating a pool of bgworkers for specific
usage and use bgworkers from them.

Regards,

[1] https://www.postgresql.org/message-id/CAD21AoAEfCNv-GgaDheDJ%2Bs-p_Lv1H24AiJeNoPGCmZNSwL1YA%40mail.gmail.com

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