MIME-Version: 1.0
References: 
 <CACG=ezZOrNsuLoETLD1gAswZMuH2nGGq7Ogcc0QOE5hhWaw=cw@mail.gmail.com>
 <CAD21AoCdx5ZNS_cO7bYz1Zfb+Kw1kuJV2wtewrz7T1pPpjcWGw@mail.gmail.com>
 <CAJDiXgi6ZQOoSEqj9RyZMEh+HHBtmW0+PHD85UNPtKch8ubvdg@mail.gmail.com>
 <CAD21AoBcoA-i-pJ_=y+jg14R8_QaJA1iwktCnu5i-C=yXDFPdA@mail.gmail.com>
 <CAJDiXgjnUdE6Sk4M0unmT+9dULyFAxcum2txQKpWTuo4uQ_oXQ@mail.gmail.com>
 <CAD21AoBTZdVR93JBo620B=MX-K8cdm3VRbjrBr_Vcpngk3AjVw@mail.gmail.com>
 <CAA5RZ0vfBg=c_0Sa1Tpxv8tueeBk8C5qTf9TrxKBbXUqPc99Ag@mail.gmail.com>
 <CAD21AoBgvUeWS8ZsXBahA1XdYayK6DJ6dx49d6Xpii-iH+Hrwg@mail.gmail.com>
In-Reply-To: 
 <CAD21AoBgvUeWS8ZsXBahA1XdYayK6DJ6dx49d6Xpii-iH+Hrwg@mail.gmail.com>
From: Sami Imseih <samimseih@gmail.com>
Date: Tue, 6 May 2025 15:11:38 -0500
Message-ID: 
 <CAA5RZ0vF+Lr-jU1LAZWTGUjboUETk8oLvaNBbA5ozX6dau+how@mail.gmail.com>
Subject: Re: POC: Parallel processing of indexes in autovacuum
To: Masahiko Sawada <sawada.mshk@gmail.com>
Cc: Daniil Davydov <3danissimo@gmail.com>, Maxim Orlov <orlovmg@gmail.com>,
	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/CAA5RZ0vF%2BLr-jU1LAZWTGUjboUETk8oLvaNBbA5ozX6dau%2Bhow%40mail.gmail.com>
Precedence: bulk

> On Mon, May 5, 2025 at 5:21=E2=80=AFPM Sami Imseih <samimseih@gmail.com> =
wrote:
> >
> >
> >> On Sat, May 3, 2025 at 1:10=E2=80=AFAM Daniil Davydov <3danissimo@gmai=
l.com> wrote:
> >> >
> >> > On Sat, May 3, 2025 at 5:28=E2=80=AFAM Masahiko Sawada <sawada.mshk@=
gmail.com> wrote:
> >> > >
> >> > > > In current implementation, the leader process sends a signal to =
the
> >> > > > a/v launcher, and the launcher tries to launch all requested wor=
kers.
> >> > > > But the number of workers never exceeds `autovacuum_max_workers`=
.
> >> > > > Thus, we will never have more a/v workers than in the standard c=
ase
> >> > > > (without this feature).
> >> > >
> >> > > I have concerns about this design. When autovacuuming on a single
> >> > > table consumes all available autovacuum_max_workers slots with
> >> > > parallel vacuum workers, the system becomes incapable of processin=
g
> >> > > other tables. This means that when determining the appropriate
> >> > > autovacuum_max_workers value, users must consider not only the num=
ber
> >> > > of tables to be processed concurrently but also the potential numb=
er
> >> > > of parallel workers that might be launched. I think it would more =
make
> >> > > sense to maintain the existing autovacuum_max_workers parameter wh=
ile
> >> > > introducing a new parameter that would either control the maximum
> >> > > number of parallel vacuum workers per autovacuum worker or set a
> >> > > system-wide cap on the total number of parallel vacuum workers.
> >> > >
> >> >
> >> > For now we have max_parallel_index_autovac_workers - this GUC limits
> >> > the number of parallel a/v workers that can process a single table. =
I
> >> > agree that the scenario you provided is problematic.
> >> > The proposal to limit the total number of supportive a/v workers see=
ms
> >> > attractive to me (I'll implement it as an experiment).
> >> >
> >> > It seems to me that this question is becoming a key one. First we ne=
ed
> >> > to determine the role of the user in the whole scheduling mechanism.
> >> > Should we allow users to determine priority? Will this priority affe=
ct
> >> > only within a single vacuuming cycle, or it will be more 'global'?
> >> > I guess I don't have enough expertise to determine this alone. I wil=
l
> >> > be glad to receive any suggestions.
> >>
> >> What I roughly imagined is that we don't need to change the entire
> >> autovacuum scheduling, but would like autovacuum workers to decides
> >> whether or not to use parallel vacuum during its vacuum operation
> >> based on GUC parameters (having a global effect) or storage parameters
> >> (having an effect on the particular table). The criteria of triggering
> >> parallel vacuum in autovacuum might need to be somewhat pessimistic so
> >> that we don't unnecessarily use parallel vacuum on many tables.
> >
> >
> > Perhaps we should only provide a reloption, therefore only tables speci=
fied
> > by the user via the reloption can be autovacuumed  in parallel?
> >
> > This gives a targeted approach. Of course if multiple of these allowed =
tables
> > are to be autovacuumed at the same time, some may not get all the worke=
rs,
> > But that=E2=80=99s not different from if you are to manually vacuum in =
parallel the tables
> > at the same time.
> >
> > What do you think ?
>
> +1. I think that's a good starting point. We can later introduce a new
> GUC parameter that globally controls the maximum number of parallel
> vacuum workers used in autovacuum, if necessary.

and I this reloption should also apply to parallel heap vacuum in
non-failsafe scenarios.
In the failsafe case however, all tables will be eligible for parallel
vacuum. Anyhow, that
discussion could be taken in that thread, but wanted to point that out.

--
Sami Imseih
Amazon Web Services (AWS)