Re: POC: Parallel processing of indexes in autovacuum

Hi,

On Wed, Apr 16, 2025 at 4:05 AM Maxim Orlov <orlovmg@gmail.com> wrote:
>
> Hi!
>
> The VACUUM command can be executed with the parallel option. As documentation states, it will perform index vacuum
andindex cleanup phases of VACUUM in parallel using integer background workers. But such an interesting feature is not
usedfor an autovacuum. After a quick look at the source codes, it became clear to me that when the parallel option was
added,the corresponding option for autovacuum wasn't implemented, although there are no clear obstacles to this. 
>
> Actually, one of our customers step into a problem with autovacuum on a table with many indexes and relatively long
transactions.Of course, long transactions are an ultimate evil and the problem can be solved by calling running vacuum
anda cron task, but, I think, we can do better. 
>
> Anyhow, what about adding parallel option for an autovacuum? Here is a POC patch for proposed functionality. For the
sakeof simplicity's, several GUC's have been added. It would be good to think through the parallel launch condition
withoutthem. 
>
> As always, any thoughts and opinions are very welcome!

As I understand it, we initially disabled parallel vacuum for
autovacuum because their objectives are somewhat contradictory.
Parallel vacuum aims to accelerate the process by utilizing additional
resources, while autovacuum is designed to perform cleaning operations
with minimal impact on foreground transaction processing (e.g.,
through vacuum delay).

Nevertheless, I see your point about the potential benefits of using
parallel vacuum within autovacuum in specific scenarios. The crucial
consideration is determining appropriate criteria for triggering
parallel vacuum in autovacuum. Given that we currently support only
parallel index processing, suitable candidates might be autovacuum
operations on large tables that have a substantial number of
sufficiently large indexes and a high volume of garbage tuples.

Once we have parallel heap vacuum, as discussed in thread[1], it would
also likely be beneficial to incorporate it into autovacuum during
aggressive vacuum or failsafe mode.

Although the actual number of parallel workers ultimately depends on
the number of eligible indexes, it might be beneficial to introduce a
storage parameter, say parallel_vacuum_workers, that allows control
over the number of parallel vacuum workers on a per-table basis.

Regarding implementation: I notice the WIP patch implements its own
parallel vacuum mechanism for autovacuum. Have you considered simply
setting at_params.nworkers to a value greater than zero?

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