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>
In-Reply-To: 
 <CAJDiXgiGSpqMQSOx-cVO_LtcB5GWHBy9ph7oOR4ebbX8A==kgw@mail.gmail.com>
From: Masahiko Sawada <sawada.mshk@gmail.com>
Date: Mon, 18 Aug 2025 14:03:19 -0700
Message-ID: 
 <CAD21AoBRRXbNJEvCjS-0XZgCEeRBzQPKmrSDjJ3wZ8TN28vaCQ@mail.gmail.com>
Subject: Re: POC: Parallel processing of indexes in autovacuum
To: Daniil Davydov <3danissimo@gmail.com>
Cc: 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/CAD21AoBRRXbNJEvCjS-0XZgCEeRBzQPKmrSDjJ3wZ8TN28vaCQ%40mail.gmail.com>
Precedence: bulk

On Mon, Aug 18, 2025 at 1:31=E2=80=AFAM Daniil Davydov <3danissimo@gmail.co=
m> wrote:
>
>
> On Fri, Aug 15, 2025 at 3:41=E2=80=AFAM Masahiko Sawada <sawada.mshk@gmai=
l.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, it
> > 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 they
> > 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 (bo=
th 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 vacuum/=
cleanup
> phase. I'm a bit wary about it, because the design says that we should
> be in parallel
> mode during the whole parallel operation. But actually, if we can make
> 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_all_in=
dexes.
> 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 insid=
e
> autovacuum code. Assign hook has been removed.
> 4) Use shmem value for determining the maximum number of parallel autovac=
uum
> workers (eliminate race condition between launcher and leader process).

Thank you for updating the patch! I'll review the new version patches.

Regards,

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

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