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>
 <CAA5RZ0vF+Lr-jU1LAZWTGUjboUETk8oLvaNBbA5ozX6dau+how@mail.gmail.com>
 <CAJDiXggueLSGMNRmLshbmFRfbo4jzks0W8bLDfUSRZ-61fPVEQ@mail.gmail.com>
 <CAFY6G8cJ=DRTX75pOGerH6sk39dRt+7MSH+y_qppDdhPs=qdQA@mail.gmail.com>
 <CAJDiXgg1t6wk9NjyMUTm1iKqM9GtdQ_wrEchBtz3xjWBZM8W8A@mail.gmail.com>
 <CAD21AoAC0=Xi38RQcAO4A+vdmoXToZMoHfbS=KLT49fAOTH_gA@mail.gmail.com>
 <CAJDiXgiD+AZKhJSn-FSRVQxtDLmJd95wDu4wtKniQF5==1JcjQ@mail.gmail.com>
 <CAD21AoAM8KsqNhrZYJuf7odvxcTC0TumXazJc-r_wC5KnDFDPg@mail.gmail.com>
 <CAJDiXghbcOC9OOj3ampxuyqXH0geggnosnrYUHGygkpss-RtxA@mail.gmail.com>
 <CAD21AoAPnq0vrcGgeN++r1GoL8Kza7jaGL=TNzuBn6+MkR=rUQ@mail.gmail.com>
 <CAJDiXghmsbTmnm--9B5bbuZXa1OL7SZ0HYppX3tx9XsdwfJBhA@mail.gmail.com>
 <DB3C67FCRLOO.1R5NLYCNEA6BF@gmail.com>
 <CAJDiXgiYiX+azuR76DcVx8fZn57m_4v6cB14-GW34mWa=qudFQ@mail.gmail.com>
 <CAD21AoDtPpkkQ_h1yf4oTx1qn4SRdTeVY3qs+9J07fYqa_4Gww@mail.gmail.com>
 <CAJDiXgi7KB7wSQ=Ux=ngdaCvJnJ5x-ehvTyiuZez+5uKHtV6iQ@mail.gmail.com>
 <CAD21AoCcHKKXsr9Oh736ejckqqS1i430xGEyJ=JP5OL0ExyP1A@mail.gmail.com>
 <CAJDiXghaFT_1sSv3q8mjyZ_RLZDgiogg0mWRvLxSWvkUi2CcLg@mail.gmail.com>
 <CAA5RZ0u63W41OmcEO+HLs4CSo-Sd3J+Q-4=04iud8V=xX4iUrA@mail.gmail.com>
 <CAJDiXgin1TXniVGJKzOTA=F9K342uVfm6O0EmubTVB=F+XSrbA@mail.gmail.com>
 <CAD21AoDadzAwibxf-+urjx=XL+eVu8=Ut-Lh2GxXUt32LbPG3Q@mail.gmail.com>
 <CAD21AoD6HhraqhOgkQJOrr0ixZkAZuqJRpzGv-B+_-ad6d5aPw@mail.gmail.com>
 <CAJDiXgiGSpqMQSOx-cVO_LtcB5GWHBy9ph7oOR4ebbX8A==kgw@mail.gmail.com>
 <CAD21AoBRRXbNJEvCjS-0XZgCEeRBzQPKmrSDjJ3wZ8TN28vaCQ@mail.gmail.com>
 <CAPpHfduBJfMcojvmYHUo8b_C=0cxRy1N+tNiNGoA3RAZq2ApaA@mail.gmail.com>
In-Reply-To: 
 <CAPpHfduBJfMcojvmYHUo8b_C=0cxRy1N+tNiNGoA3RAZq2ApaA@mail.gmail.com>
From: Masahiko Sawada <sawada.mshk@gmail.com>
Date: Tue, 16 Sep 2025 11:30:22 -0700
Message-ID: 
 <CAD21AoC82NeHKXc965pPUZO2eyo1U7P6cmfRJbrcPDcnd7_6hw@mail.gmail.com>
Subject: Re: POC: Parallel processing of indexes in autovacuum
To: Alexander Korotkov <aekorotkov@gmail.com>
Cc: Daniil Davydov <3danissimo@gmail.com>, Sami Imseih <samimseih@gmail.com>,
	Matheus Alcantara <matheusssilv97@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/CAD21AoC82NeHKXc965pPUZO2eyo1U7P6cmfRJbrcPDcnd7_6hw%40mail.gmail.com>
Precedence: bulk

On Mon, Sep 15, 2025 at 11:50=E2=80=AFAM Alexander Korotkov
<aekorotkov@gmail.com> wrote:
>
> Hi!
>
> On Tue, Aug 19, 2025 at 12:04=E2=80=AFAM Masahiko Sawada <sawada.mshk@gma=
il.com> wrote:
> >
> > On Mon, Aug 18, 2025 at 1:31=E2=80=AFAM Daniil Davydov <3danissimo@gmai=
l.com> wrote:
> > >
> > >
> > > On Fri, Aug 15, 2025 at 3:41=E2=80=AFAM Masahiko Sawada <sawada.mshk@=
gmail.com> wrote:
> > > >
> > >
> > > > 2. when an autovacuum worker (not parallel vacuum worker) who uses
> > > > parallel vacuum gets SIGHUP, it errors out with the error message
> > > > "parameter "max_stack_depth" cannot be set during a parallel
> > > > operation". Autovacuum checks the configuration file reload in
> > > > vacuum_delay_point(), and while reloading the configuration file, i=
t
> > > > attempts to set max_stack_depth in
> > > > InitializeGUCOptionsFromEnvironment() (which is called by
> > > > ProcessConfigFileInternal()). However, it cannot change
> > > > max_stack_depth since the worker is in parallel mode but
> > > > max_stack_depth doesn't have GUC_ALLOW_IN_PARALLEL flag. This doesn=
't
> > > > happen in regular backends who are using parallel queries because t=
hey
> > > > check the configuration file reload at the end of each SQL command.
> > > >
> > >
> > > Hm, this is a really serious problem. I see only two ways to solve it=
 (both are
> > > not really good) :
> > > 1)
> > > Do not allow processing of the config file during parallel autovacuum
> > > execution.
> > >
> > > 2)
> > > Teach the autovacuum to enter parallel mode only during the index vac=
uum/cleanup
> > > phase. I'm a bit wary about it, because the design says that we shoul=
d
> > > be in parallel
> > > mode during the whole parallel operation. But actually, if we can mak=
e
> > > sure that all
> > > launched workers are exited, I don't see reasons, why can't we just
> > > exit parallel mode
> > > at the end of parallel_vacuum_process_all_indexes.
> > >
> > > What do you think about it?
> >
> > Hmm, given that we're trying to support parallel heap vacuum on
> > another thread[1] and we will probably support it in autovacuums, it
> > seems to me that these approaches won't work.
> >
> > Another idea would be to allow autovacuum workers to process the
> > config file even in parallel mode. GUC changes in the leader worker
> > would not affect parallel vacuum workers, but it is fine to me. In the
> > context of autovacuum, only specific GUC parameters related to
> > cost-based delays need to be affected also to parallel vacuum workers.
> > Probably we need some changes to compute_parallel_delay() so that
> > parallel workers can compute the sleep time based on the new
> > vacuum_cost_limit and vacuum_cost_delay after the leader process
> > (i.e., autovacuum worker) reloads the config file.
> >
> > >
> > > Again, thank you for the review. Please, see v10 patches (only 0001
> > > has been changed) :
> > > 1) Reserve and release workers only inside parallel_vacuum_process_al=
l_indexes.
> > > 2) Add try/catch block to the parallel_vacuum_process_all_indexes, so=
 we can
> > > release workers even after an error. This required adding a static
> > > variable to account
> > > for the total number of reserved workers (av_nworkers_reserved).
> > > 3) Cap autovacuum_max_parallel_workers by max_worker_processes only i=
nside
> > > autovacuum code. Assign hook has been removed.
> > > 4) Use shmem value for determining the maximum number of parallel aut=
ovacuum
> > > workers (eliminate race condition between launcher and leader process=
).
> >
> > Thank you for updating the patch! I'll review the new version patches.
>
> I've rebased this patchset to the current master.  That required me to mo=
ve the new GUC definition to guc_parameters.dat.  Also, I adjusted typedefs=
.list and made pgindent.  Some notes about the patch.

Thank you for updating the patch!

> I see parallel_vacuum_process_all_indexes() have a TRY/CATCH block.  As I=
 heard, the overhead of setting/doing jumps is platform-dependent, and not =
harmless on some platforms.  Therefore, can we skip TRY/CATCH block for non=
-autovacuum vacuum?  Possibly we could move it to AutoVacWorkerMain(), that=
 would save us from repeatedly setting a jump in autovacuum workers too.

I wonder if using the TRY/CATCH block is not enough to ensure that
autovacuum workers release the reserved parallel workers in FATAL
cases.

> In general, I think this patchset badly lack of testing.  I think it need=
s tap tests checking from the logs that autovacuum has been done in paralle=
l.  Also, it would be good to set up some injection points, and check that =
reserved autovacuum parallel workers are getting released correctly in the =
case of errors.

+1

IIUC the patch still has one problem in terms of reloading the
configuration parameters during parallel mode as I mentioned
before[1].

Regards,

[1] https://www.postgresql.org/message-id/CAD21AoBRRXbNJEvCjS-0XZgCEeRBzQPK=
mrSDjJ3wZ8TN28vaCQ%40mail.gmail.com

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