MIME-Version: 1.0
References: 
 <CAApHDvp1=FOs6GneTzLSCHnCmC7z1_80=U3M=CKd82-pwS3YHg@mail.gmail.com>
 <aPuWev3D9M4iGCUt@nathan>
 <CAApHDvoM5MEHHBc0TNdrzkpq39WdEHSZhdWrtnx9zOWNXTSFGw@mail.gmail.com>
 <aP-YgrcPi0EhgR9x@nathan>
 <CAA5RZ0u2Mbks+O2DKBYen94AH3OMUcg+A7wvxrXYkmjTddBx4g@mail.gmail.com>
 <aP_g61kSkGAQOu3F@nathan>
 <CAA5RZ0sybfRyKp+DY+r=2U+-r7HfSF4GL1oVOOcVtEWmk2ewUw@mail.gmail.com>
 <CAApHDvpVE5F-_8rpPC+-L98mA0yK0S_jtQGqLn69fkRevf726g@mail.gmail.com>
 <aQEwRD5XW4wfJE6G@nathan>
 <CAA5RZ0uGbpaom3v+2K=bNuitnSGX_Lw4yPj3SbJdABYKgGur_A@mail.gmail.com>
 <aQI7tGEs8IOPxG64@nathan>
In-Reply-To: <aQI7tGEs8IOPxG64@nathan>
From: wenhui qiu <qiuwenhuifx@gmail.com>
Date: Thu, 30 Oct 2025 10:58:44 +0800
Message-ID: 
 <CAGjGUAJ-XqLN6VU+HeXn0iooiJrrnN4Pqp+xVU8VKKNsZcfR2w@mail.gmail.com>
Subject: Re: another autovacuum scheduling thread
To: Nathan Bossart <nathandbossart@gmail.com>
Cc: Sami Imseih <samimseih@gmail.com>, David Rowley <dgrowleyml@gmail.com>,
	Robert Haas <robertmhaas@gmail.com>,
 Jeremy Schneider <schneider@ardentperf.com>,
	pgsql-hackers@postgresql.org
Content-Type: multipart/alternative; boundary="00000000000019e69d0642576e4b"
Archived-At: 
 <https://www.postgresql.org/message-id/CAGjGUAJ-XqLN6VU%2BHeXn0iooiJrrnN4Pqp%2BxVU8VKKNsZcfR2w%40mail.gmail.com>
Precedence: bulk

--00000000000019e69d0642576e4b
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

HI Nathan

> That approach would begin aggressively scaling the priority of tables
> sooner, but I don't know if that's strictly better.  In any case, I'd lik=
e
> to avoid making the score calculation too magical.
In fact, with the introduction of the vacuum_max_eager_freeze_failure_rate
feature, if a table=E2=80=99s age still exceeds more than 1.x times the
autovacuum_freeze_max_age, it suggests that the vacuum freeze process is
not functioning properly. Once the age surpasses vacuum_failsafe_age,
wraparound issues are likely to occur soon.Taking the average of
vacuum_failsafe_age and autovacuum_freeze_max_age is not a complex
approach. Under the default configuration, this average already exceeds
four times the autovacuum_freeze_max_age. At that stage, a DBA should have
already intervened to investigate and resolve why the table age is not
decreasing.

Thanks

On Thu, Oct 30, 2025 at 12:07=E2=80=AFAM Nathan Bossart <nathandbossart@gma=
il.com>
wrote:

> On Wed, Oct 29, 2025 at 10:24:17AM -0500, Sami Imseih wrote:
> > I think we do need some documentation about this behavior, which v6 is
> > still missing.
>
> Would you be interested in giving that part a try?
>
> > Another thing I have been contemplating about is the change in
> prioritization
> > and the resulting difference in the order in which tables are vacuumed
> > is what it means for workloads in which autovacuum tuning that was
> > done with the current assumptions will no longer be beneficial.
> >
> > Let's imagine staging tables that get created and dropped during
> > some batch processing window and they see huge data
> > ingestion/changes. The current scan will make these less of a priority
> > naturally in relation to other permanent tables, but with the new
> priority,
> > we are making these staging tables more of a priority. Users will now
> > need to maybe turn off autovacuum on a per-table level to prevent this
> > scenario. That is just one example.
> >
> > What I am also trying to say is should we provide a way, I hate
> > to say a GUC, for users to go back to the old behavior? or am I
> > overstating the risk here?
>
> It's probably worth testing out this scenario, but I can't say I'm terrib=
ly
> worried.  Those kinds of tables are already getting chosen by autovacuum
> earlier due to reltuples =3D=3D -1, and this patch will just move them to=
 the
> front of the list that autovacuum creates.  In any case, I'd really like =
to
> avoid a GUC or fallback switch here.
>
> --
> nathan
>
>
>

--00000000000019e69d0642576e4b
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">HI Nathan<div><span class=3D"gmail-im"><br></span>&gt; Tha=
t approach would begin aggressively scaling the priority of tables<br>&gt; =
sooner, but I don&#39;t know if that&#39;s strictly better.=C2=A0 In any ca=
se, I&#39;d like<br>&gt; to avoid making the score calculation too magical.=
<font color=3D"#888888"><br></font></div><div>In fact, with the introductio=
n of the vacuum_max_eager_freeze_failure_rate feature, if a table=E2=80=99s=
 age still exceeds more than 1.x times the autovacuum_freeze_max_age, it su=
ggests that the vacuum freeze process is not functioning properly. Once the=
 age surpasses vacuum_failsafe_age, wraparound issues are likely to occur s=
oon.Taking the average of vacuum_failsafe_age and autovacuum_freeze_max_age=
 is not a complex approach. Under the default configuration, this average a=
lready exceeds four times the autovacuum_freeze_max_age. At that stage, a D=
BA should have already intervened to investigate and resolve why the table =
age is not decreasing.<br></div><div><span style=3D"color:rgb(0,0,0);font-f=
amily:-webkit-standard;font-size:medium"><br></span></div><div><font color=
=3D"#000000" face=3D"-webkit-standard" size=3D"3"><span style=3D"caret-colo=
r: rgb(0, 0, 0);">Thanks=C2=A0</span></font></div></div><br><div class=3D"g=
mail_quote gmail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">On =
Thu, Oct 30, 2025 at 12:07=E2=80=AFAM Nathan Bossart &lt;<a href=3D"mailto:=
nathandbossart@gmail.com">nathandbossart@gmail.com</a>&gt; wrote:<br></div>=
<blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-=
left-width:1px;border-left-style:solid;border-left-color:rgb(204,204,204);p=
adding-left:1ex">On Wed, Oct 29, 2025 at 10:24:17AM -0500, Sami Imseih wrot=
e:<br>
&gt; I think we do need some documentation about this behavior, which v6 is=
<br>
&gt; still missing.<br>
<br>
Would you be interested in giving that part a try?<br>
<br>
&gt; Another thing I have been contemplating about is the change in priorit=
ization<br>
&gt; and the resulting difference in the order in which tables are vacuumed=
<br>
&gt; is what it means for workloads in which autovacuum tuning that was<br>
&gt; done with the current assumptions will no longer be beneficial.<br>
&gt; <br>
&gt; Let&#39;s imagine staging tables that get created and dropped during<b=
r>
&gt; some batch processing window and they see huge data<br>
&gt; ingestion/changes. The current scan will make these less of a priority=
<br>
&gt; naturally in relation to other permanent tables, but with the new prio=
rity,<br>
&gt; we are making these staging tables more of a priority. Users will now<=
br>
&gt; need to maybe turn off autovacuum on a per-table level to prevent this=
<br>
&gt; scenario. That is just one example.<br>
&gt; <br>
&gt; What I am also trying to say is should we provide a way, I hate<br>
&gt; to say a GUC, for users to go back to the old behavior? or am I<br>
&gt; overstating the risk here?<br>
<br>
It&#39;s probably worth testing out this scenario, but I can&#39;t say I=
9;m terribly<br>
worried.=C2=A0 Those kinds of tables are already getting chosen by autovacu=
um<br>
earlier due to reltuples =3D=3D -1, and this patch will just move them to t=
he<br>
front of the list that autovacuum creates.=C2=A0 In any case, I&#39;d reall=
y like to<br>
avoid a GUC or fallback switch here.<br>
<br>
-- <br>
nathan<br>
<br>
<br>
</blockquote></div>

--00000000000019e69d0642576e4b--