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>
 <CAD21AoC82NeHKXc965pPUZO2eyo1U7P6cmfRJbrcPDcnd7_6hw@mail.gmail.com>
 <CAJDiXghP2kXnEz+cj3rAWNM3NdKSB_4WtnngFXpVz2omPhGr5A@mail.gmail.com>
 <CAD21AoA0bnRZC_OqKMnH-Ln+OZ9z9k56j2c_MXj8pw69O-wkBw@mail.gmail.com>
 <CAA5RZ0sSXDza7_nUUbhHL_Sws+M+HR1daKJPXHpdLuNCkwUgUg@mail.gmail.com>
 <CAJDiXggrBsbzOisf+Nu8pZkYGrpUZaFbosL1Wbm3kKxzTm4xgw@mail.gmail.com>
 <CAA5RZ0tbiPcgQEjnhdnjz6qSjfRsGrr8jGCaMcrMaoPpax3wig@mail.gmail.com>
 <CAJDiXgjt5ZmK2uvS0E8Ztt5ePYmq8Ze_dG05Zo2NUsKLHCEuYA@mail.gmail.com>
 <CAD21AoB7v5tLPXLK=qmtt6PaEC1f+Fb-gh+MwAbXfm6x4eZGNw@mail.gmail.com>
 <CAJDiXghwtUbiFnAh3nSaxTk8KFupQuMbp+g4z3wOLoQfMuqgDg@mail.gmail.com>
 <CAJDiXgjoNd4BF19HNY_FAcDUqiqsfw8cGhNOJwBxahB8P38E3Q@mail.gmail.com>
 <CAD21AoBT1LWqPZkcHpVMVh0ZOXUneO=p61t0i8cQ+kOP9qfODQ@mail.gmail.com>
 <CAJDiXggL=J0nV7PfBsMW9+UOU3KUp1jNBM9Gov1JvAX7aG_U1g@mail.gmail.com>
 <CAD21AoDz-1Zf9DOJJrdcB2=eNA4UdywthkowNp_dHmOGC-yV_g@mail.gmail.com>
 <CAJDiXgjzphJ313=aDwbvryHpmTi6AqE+-5crysTtzKv01-vkzA@mail.gmail.com>
 <CAD21AoD7_4gsQ2a82zO3SaRwjdw_3tyiYDHNFPUKQ5DAA5HOtA@mail.gmail.com>
 <CAJDiXggY1QzNde6_HhpzneLc9dYqmWZ+PY39cuBXYdcCTuoJBA@mail.gmail.com>
 <CAD21AoCFPiS2jcMA1JaV1kT8xrGz5BpN7iBP_gCgRuaANEbciA@mail.gmail.com>
 <CAJDiXgh6jmNGR3uOB_6YeGhNkR2=HdTdEYjmHXdumNzyY4MckQ@mail.gmail.com>
 <CAD21AoDs3SOXeAEoCRizfEKybpRkE7t7poX0+iZ6MM1MFWMsfA@mail.gmail.com>
 <CAJDiXgjTkuqSPerC_nasxDz6d2Komf1ipYKV6SupDRnc9yhO9w@mail.gmail.com>
 <CAD21AoAXMjX03h5K84u0heBLU+fqGgWBGBDwnBDGSs=DhyF9pQ@mail.gmail.com>
 <CAJDiXghjZEAYboGhujgGvY9=RiFD01ERHVVF+NQMuuAKVZDmDQ@mail.gmail.com>
 <CAD21AoAD+N5SxBr0qL7TeWnvq4iYmFT=DyWdNLQPB-XntYkwEg@mail.gmail.com>
 <CAJDiXgjgn87sH1-MmONPKkeYJG83C0ChrYkYn9UcRonLhOOfOw@mail.gmail.com>
 <CAD21AoCoJYauWO78M-CGdHpYfcqEZVV5a1Z-7wWB=-G-x8EVFg@mail.gmail.com>
 <CAJDiXghaazbrQMZZS08d9Ffh2y4w05TgH9dpBhqChv1qNTp+xA@mail.gmail.com>
In-Reply-To: 
 <CAJDiXghaazbrQMZZS08d9Ffh2y4w05TgH9dpBhqChv1qNTp+xA@mail.gmail.com>
From: Masahiko Sawada <sawada.mshk@gmail.com>
Date: Mon, 16 Mar 2026 09:46:19 -0700
Message-ID: 
 <CAD21AoDbaNtLrFRxG9OG5WrBd7DCs4q+CfJd8AJTBEqRri4WeQ@mail.gmail.com>
Subject: Re: POC: Parallel processing of indexes in autovacuum
To: Daniil Davydov <3danissimo@gmail.com>
Cc: Sami Imseih <samimseih@gmail.com>,
 Alexander Korotkov <aekorotkov@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/CAD21AoDbaNtLrFRxG9OG5WrBd7DCs4q%2BCfJd8AJTBEqRri4WeQ%40mail.gmail.com>
Precedence: bulk

On Mon, Mar 16, 2026 at 5:34=E2=80=AFAM Daniil Davydov <3danissimo@gmail.co=
m> wrote:
>
> Hi,
>
> On Thu, Mar 12, 2026 at 2:05=E2=80=AFAM Masahiko Sawada <sawada.mshk@gmai=
l.com> wrote:
> >
> > BTW thes discussion made me think to change av_max_parallel_workers to
> > control the number of workers per-autovacuum worker instead (with
> > renaming it to say max_parallel_workers_per_autovacuum_worker). Users
> > can compute the maximum number of parallel workers the system requires
> > by (autovacuum_worker_slots *
> > max_parallel_workers_per_autovacuum_worker). We would no longer need
> > the reservation and release logic. I'd like to hear your opinion.
> >
>
> IIUC, one of the main autovacuum's goals is to be "inconspicuous" for the
> rest of the system. I mean that it should not try to vacuum all the table=
s
> as fast as possible. Instead it should try to interfere with other backen=
ds
> as little as possible and try to avoid high resource consumption (assumin=
g
> there is no hazard of wraparound).
>
> I propose to reason based on the case for which the parallel a/v will
> actually be used :
> We have a 3 tables which has 80+ indexes each and require a
> parallel a/v. Ideally, each of these tables should be processed with 20
> parallel workers. This is a real example which can be encountered in
> different productions, where such tables take up about half of all the da=
ta
> in the database.
>
> How parallel a/v will handle such a situation?
> 1. Our current implementation
> We can set av_max_parallel_workers to 60 and autovacuum_parallel_workers
> reloption to 20 for each table.
> 2. Proposed idea
> We can set max_parallel_workers_per_av_worker to 20 and
> autovacuum_parallel_workers reloption to 20 for each table.
>
> In both cases we have guarantee that all tables will be processed with th=
e
> desired number of parallel workers. And both cases allows us to limit the
> CPU consumption via reducing the "av_max_parallel_workers" parameter (for
> current implementation) or via reducing the "autovacuum_parallel_workers"
> reloption for each table (for proposed idea). So basically I don't see wh=
ether
> current approach has a big advantages over the idea you proposed.
>
> I also asked my friend, who is many years working with the clients with b=
ig
> productions. He said that this is super important to process such huge ta=
bles
> with maximum "intensity". I.e. each a/v worker should have ability to lau=
nch
> as many parallel workers as required. I guess that this is an argument in
> favor of your idea.
>
> The only argument against this idea that I could come up with is that som=
e
> users may abuse our parallel a/v feature. For instance, the user can set
> "autovacuum_parallel_workers" reloption not only for large tables, but al=
so
> for many smaller ones. In this case the max_parallel_workers_per_av_worke=
r
> must be pretty large (in order to process the huge table). Thus, the user
> can face a situation when all a/v workers are launching additional parall=
el
> workers =3D> there is high CPU consumption and possibly max_parallel_work=
ers
> shortage. The only way to deal with it is to go through a large amount of
> smaller tables and reduce "autovacuum_parallel_workers" reloption for eac=
h
> of them. IMHO, this is a pretty unpleasant experience for the user. On th=
e
> other hand, the user himself is to blame for the occurrence of such a
> situation.
>
> Let's summarize.
> Proposed idea has several strong advantages over current implementation.
> The only disadvantage I came up with can be avoided by writing recommenda=
tions
> on how to use this feature in the documentation. So, if I didn't messed u=
p
> anything and you don't have any doubts, I would rather implement the
> proposed idea.

Thank you for the analysis on the new idea.

While both ideas can achieve our goal of this feature in general, the
new idea doesn't require an additional layer of reserve/release logic
on top of the existing bgworker pool, which is good. I've not tried
coding this idea but I believe the patch can be simplified very much.
So I agree to move to this idea.

>
> > > 2)
> > > I suggest adding a separate log that will be emitted every time we ar=
e
> > > unable to start workers due to a shortage of av_max_parallel_workers.
> >
> > For (2), do you mean that the worker writes these logs regardless of
> > log_autovacuum_min_duration setting? I'm concerned that the server
> > logs would be flooded with these logs especially when multiple
> > autovacuum workers are working very actively and the system is facing
> > a shortage of av_max_parallel_workers.
>
> Oh, I didn't take that into account. But this is not a problem - we can
> accumulate such statistics just as we do now for the "nreserved" ones. An=
d
> then we will log this value with all other stats.
>
> > > Possibly we can introduce a new injection point, or a new log for it.
> > > But I assume that the subject of discussion in patch 0002 is the
> > > "nreserved" logic, and "nlaunched/nplanned" logic does not raise any
> > > questions.
> > >
> > > I suggest splitting the 0002 patch into two parts : 1) basic logic an=
d
> > > 2) additional logic with nreserved or something else. The second part=
 can be
> > > discussed in isolation from the patch set. If we do this, we may not =
have to
> > > change the tests. What do you think?
> >
> > Assuming the basic logic means nlaunched/nplanned logic, yes, it would
> > be a nice idea. I think user-facing logging stuff can be developed as
> > an improvement independent from the main parallel autovacuum patch.
> > It's ideal if we can implement the main patch (with tests) without
> > relying on the user-facing logging.
>
> OK, actually we can do it.
>
>
>
> Thank you very much for the review!
> Please, see attached patches. The changes are :
> 1) Fixed segfault with accessing outdated pv_shared_cost_params pointer.
> 2) "Logging for autovacuum" is divided into two patches - basic logging
> (nplanned/nlaunched) and advanced logging (nreserved).
> 3) Tests are now independent of logging.

Thank you for updating the patches. I'll wait for the new
implementation and will review the patches as soon as the patches are
updated.

Regards,

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