MIME-Version: 1.0
References: <CACG=ezZOrNsuLoETLD1gAswZMuH2nGGq7Ogcc0QOE5hhWaw=cw@mail.gmail.com>
In-Reply-To: <CACG=ezZOrNsuLoETLD1gAswZMuH2nGGq7Ogcc0QOE5hhWaw=cw@mail.gmail.com>
From: Masahiko Sawada <sawada.mshk@gmail.com>
Date: Wed, 30 Apr 2025 18:02:21 -0700
Message-ID: <CAD21AoCdx5ZNS_cO7bYz1Zfb+Kw1kuJV2wtewrz7T1pPpjcWGw@mail.gmail.com>
Subject: Re: POC: Parallel processing of indexes in autovacuum
To: Maxim Orlov <orlovmg@gmail.com>
Cc: Postgres hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAD21AoCdx5ZNS_cO7bYz1Zfb%2BKw1kuJV2wtewrz7T1pPpjcWGw%40mail.gmail.com>
Precedence: bulk

Hi,

On Wed, Apr 16, 2025 at 4:05=E2=80=AFAM Maxim Orlov <orlovmg@gmail.com> wro=
te:
>
> Hi!
>
> The VACUUM command can be executed with the parallel option. As documenta=
tion states, it will perform index vacuum and index cleanup phases of VACUU=
M in parallel using integer background workers. But such an interesting fea=
ture is not used for an autovacuum. After a quick look at the source codes,=
 it became clear to me that when the parallel option was added, the corresp=
onding option for autovacuum wasn't implemented, although there are no clea=
r obstacles to this.
>
> Actually, one of our customers step into a problem with autovacuum on a t=
able with many indexes and relatively long transactions. Of course, long tr=
ansactions are an ultimate evil and the problem can be solved by calling ru=
nning vacuum and a cron task, but, I think, we can do better.
>
> Anyhow, what about adding parallel option for an autovacuum? Here is a PO=
C patch for proposed functionality. For the sake of simplicity's, several G=
UC's have been added. It would be good to think through the parallel launch=
 condition without them.
>
> 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_Lv1H=
24AiJeNoPGCmZNSwL1YA%40mail.gmail.com

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