public inbox for [email protected]
help / color / mirror / Atom feedFrom: Masahiko Sawada <[email protected]>
To: Daniil Davydov <[email protected]>
Cc: Alexander Korotkov <[email protected]>
Cc: SATYANARAYANA NARLAPURAM <[email protected]>
Cc: Bharath Rupireddy <[email protected]>
Cc: Sami Imseih <[email protected]>
Cc: Matheus Alcantara <[email protected]>
Cc: Maxim Orlov <[email protected]>
Cc: Postgres hackers <[email protected]>
Subject: Re: POC: Parallel processing of indexes in autovacuum
Date: Thu, 9 Apr 2026 11:37:04 -0700
Message-ID: <CAD21AoACxi0vpPTbF3xea2gwxPO4sc4phW8vt1qssODN1=FNRQ@mail.gmail.com> (raw)
In-Reply-To: <CAJDiXghde6v427BzoNbWeeNRyzkTA6jaZdu=DsrD669+cfsBwg@mail.gmail.com>
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>
<[email protected]>
<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>
<CAD21AoDbaNtLrFRxG9OG5WrBd7DCs4q+CfJd8AJTBEqRri4WeQ@mail.gmail.com>
<CAJDiXgjjd1jL86B--AyRo2tDM1Wiu+7Pduwh5d0u_UM8GRugvw@mail.gmail.com>
<CAD21AoBo_wS7y0X7_7ajEFkptzo9ZrF8RFNRnu2Xe8XL74o0SQ@mail.gmail.com>
<CAJDiXggH1bW=4n+55CGLvs_sRU4SYNXwYLZ37wvJ5H_3yURSPw@mail.gmail.com>
<CAD21AoDxhN8Z6Lx1ZicBXKkbMsRQqEXiq4ALs4uaD648iSvXoA@mail.gmail.com>
<CAJDiXgh3Dg2f5k3xRJnzoY39jQENUhh125ArYapXkSu5D7JJuw@mail.gmail.com>
<CAD21AoBYc7L7W4dRdxeoJzOH5OgpiCAtKz-54iX4Ufn8PnQoww@mail.gmail.com>
<CAJDiXgi8X-DMb92v5WHLCNxDHxH9gO8WQxOMtdpmU7X=WXCiuQ@mail.gmail.com>
<CAD21AoDKxs0UrTwa3rkP+kE9AzccabpK7G-Tk=HYneaFTZBtiA@mail.gmail.com>
<CALj2ACUJ0TtYWtFuXXVf0aLES8tfZePXnB8WQ=0KCrNaABzQVg@mail.gmail.com>
<CAJDiXgj=-R1z7H7+npm-o+q6YBkr5_6Qe=1wcy47ovAqej4TkA@mail.gmail.com>
<CAHg+QDehxaJEd1Yp1MpW8UO71xmbasy7t2GZGvqOYwkr0md8DQ@mail.gmail.com>
<CAJDiXgi73x7h0=UoXriFjskRB6htZ-uqXKqvWN3RefuxbP93gA@mail.gmail.com>
<CAJDiXghbc38pMFCNNbVx1N=6ZKdsO5u3SF8pZrKm2mJvX71OKw@mail.gmail.com>
<CAD21AoAqkBtCYwuwbiJ6qA+Ny-pO4idpfgqZBDxE6tGgHTpm1A@mail.gmail.com>
<CAJDiXgj3A=wNC-S0z3TixmnVUkifs=07yLLHJ7_+dDsakft1tA@mail.gmail.com>
<CAD21AoAvZc6Rwi1hZ7x+U3vz7AMMSpcbQ2JBn6+WmQp-3yfKMg@mail.gmail.com>
<CAJDiXggvE=e=0+HnZ1XjwUcXYTb0dw77pRUts5gqY997YaxVjQ@mail.gmail.com>
<CAPpHfdu=xY_wpPitxFTH0kaGiFsd2JsXhL4=s_SO-TPRouVzPA@mail.gmail.com>
<CAJDiXgjctmV-vLSjSQFxkANT-VZzwjJCmYGcJQ_y3+TkjxcrWg@mail.gmail.com>
<CAD21AoAm806fQhMH49T_Ft9z1HSAdTi=gRcoxS9DW+nVQ9iCMQ@mail.gmail.com>
<CAJDiXgh0py4DcbJOW5VaCeHtyofFSAaR2Mx3BhyJHppG15Lr3g@mail.gmail.com>
<CAD21AoA_eRa9nVCenw3tnVwUCgR-Jk4=W4GuV36+oYKgBmRwNA@mail.gmail.com>
<CAJDiXgjG9-LR194LF+h6qwVPUkDWTmQuhTPx7JjzB2DQfQ6k6Q@mail.gmail.com>
<CAD21AoBwzPC-oo=yyfdVBC6hadjOSbKyHSs7bH7DqPUamAEZfg@mail.gmail.com>
<CAJDiXggPFohjMA1XKJrpR7=UpTeDCzzObTYoPUkDid+4+Vhn0w@mail.gmail.com>
<CAD21AoBon929k5bfwX=sT397kyFyHyGgaXGCDOj2+7cDiqZa=w@mail.gmail.com>
<CAJDiXghde6v427BzoNbWeeNRyzkTA6jaZdu=DsrD669+cfsBwg@mail.gmail.com>
On Tue, Apr 7, 2026 at 6:32 AM Daniil Davydov <[email protected]> wrote:
>
> Hi,
>
> On Tue, Apr 7, 2026 at 10:49 AM Masahiko Sawada <[email protected]> wrote:
> >
> > > >
> > > > While some autovacuum parameters do override GUCs, those are typically
> > > > local to the process (like cost delay). Parallel workers, however, are
> > > > a shared system-wide resource. In a multi-tenant environment, allowing
> > > > a single table's reloption to bypass the
> > > > autovacuum_max_parallel_workers = 0 limit could lead to unexpected
> > > > exhaustion of the worker pool.
> > >
> > > Will this exhaustion really be unexpected? If we describe such an ability in
> > > the documentation, and the user uses it, then everything is fair. Even if
> > > administrator forgets that he enabled av_parallel_workers reloption somewhere,
> > > then he can :
> >
> > How can DBAs prevent parallel workers from being exhaustly used if
> > users set a high value to the reloption?
> >
>
> Only manual control. Since DBA increased reloption manually, it is OK to ask
> him to manually decrease it.
In multi-tenant environments, the roles of table owners and DBAs are
often separated. Tenants can freely set reloptions via ALTER TABLE,
but a DBA cannot easily revert those settings on user-owned tables.
Even if a DBA tries to use ALTER TABLE to fix a misconfigured
reloption, it would cancel any currently running autovacuum on that
table. Furthermore, if the table is undergoing an anti-wraparound
vacuum, the ALTER TABLE command itself will be blocked, making it
impossible to resolve a resource crisis quickly. If a single tenant
could exhaust the entire parallel worker pool by setting a high
reloption value, the DBA would have no effective way to prevent or
mitigate it under an override model.
While I understand the use case for enabling parallel vacuum only on
specific tables, this is already achievable under the cap model (by
setting a global GUC > 0 and using the reloption to disable it on
others), even if the initial configuration is more tedious.
Also, I'm concerned that the override behavior would be inconsistent
with other parallel-query-related features. While some autovacuum
reloptions (like autovacuum_vacuum_scale_factor) do override GUCs,
those parameters only affect the local behavior of that specific
process and do not impact shared system-wide resources. In contrast,
autovacuum_max_parallel_workers takes workers from the
max_parallel_workers pool. Allowing a single table to monopolize this
shared pool by bypassing the GUC cap creates a significant risk that
cannot be easily managed.
While this isn't exclusively about multi-tenancy, I think that we
cannot simply introduce a behavior that creates such a high risk for
system-wide resource exhaustion.
>
> > > 1)
> > > Check the logfile (if log level is not too high) searching for logs like
> > > "parallel workers: index vacuum: N planned, N launched in total".
> > > 2)
> > > Run a query that selects all tables which have av_parallel_workers > 0.
> >
> > Does that mean DBAs would need to run these queries periodically?
>
> Not really. I say that even if DBA has lost control on the parallel a/v
> workers, it has an ability to find these bottlenecks.
>
> > I don't think that in a multi-tenant environment, DBAs can (or should)
> > execute ALTER TABLE on user-owned tables just to fix resource issues.
> >
>
> Well, the people I talked to had a different opinion which is based on clients
> feedback : what is acceptable and what is not. I don't think that we can
> convince each other, so let it be as it is :)
>
> But if you don't mind continuing to discuss this topic (perhaps with the
> involvement of other people), I would love to create a new thread for it.
Okay.
Regards,
--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com
view thread (112+ messages)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: POC: Parallel processing of indexes in autovacuum
In-Reply-To: <CAD21AoACxi0vpPTbF3xea2gwxPO4sc4phW8vt1qssODN1=FNRQ@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox