MIME-Version: 1.0
References: 
 <CAApHDvqK=dUa35oZjG8kh+A-aPof2pfsNmb-WXTMVHduKpm6bQ@mail.gmail.com>
 <CA+TgmoYCLqE-1vX9uhryF7NhOAT0v6+RP8E303u6rRgpFUiWyg@mail.gmail.com>
 <CAApHDvqFyEdWyEDT7NCKcqEi2sdchhOsA-+yWv_Zk=dRek4kgg@mail.gmail.com>
 <CA+TgmoZE=-Pw=85u+eX6UpMdY02o1otEhQba0n2T78yVyV2N9g@mail.gmail.com>
 <CAA5RZ0vzu4vfRP0podGDCBOn-OaK9Qs-oyksER5OtzanacqMQw@mail.gmail.com>
 <CA+TgmoZ44jzFBB4zxoXrX6iMxv7TPzv5duPe44UPpoyBBDL48g@mail.gmail.com>
 <CAApHDvo97hqpZR+vgVVSLQsPhVCA=yEerAGn9wzB_67vjmu6cA@mail.gmail.com>
 <CA+TgmoaAgv5D6u+_4KKqQFGH1n+jsFZ1mFWh+CSGqDUtyBNsXg@mail.gmail.com>
 <aam3dmZcia95-vB6@nathan> <abAzhNqs5F8sJDdm@nathan> <abBEiOICOdbbhsEI@nathan>
 <CAA5RZ0seLxDmH9UftUA3vS8DrW+7T9HV5XK8yAuQxA8BaRkg2g@mail.gmail.com>
 <CAA5RZ0vfhAnFBp4HrBQc+ALaJMx6vCvMtnBi39ST_4nH9PZEjA@mail.gmail.com>
In-Reply-To: 
 <CAA5RZ0vfhAnFBp4HrBQc+ALaJMx6vCvMtnBi39ST_4nH9PZEjA@mail.gmail.com>
From: wenhui qiu <qiuwenhuifx@gmail.com>
Date: Wed, 11 Mar 2026 11:56:04 +0800
Message-ID: 
 <CAGjGUAJ-PBzayrf793PRdnX52KvMJ8xVmQhm6BwKsf0-5qC3aQ@mail.gmail.com>
Subject: Re: another autovacuum scheduling thread
To: Sami Imseih <samimseih@gmail.com>
Cc: Nathan Bossart <nathandbossart@gmail.com>,
 Robert Haas <robertmhaas@gmail.com>,
	David Rowley <dgrowleyml@gmail.com>, Robert Treat <rob@xzilla.net>,
	Jeremy Schneider <schneider@ardentperf.com>, pgsql-hackers@postgresql.org
Content-Type: multipart/alternative; boundary="000000000000183b55064cb79e96"
Archived-At: 
 <https://www.postgresql.org/message-id/CAGjGUAJ-PBzayrf793PRdnX52KvMJ8xVmQhm6BwKsf0-5qC3aQ%40mail.gmail.com>
Precedence: bulk

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

HI Sami
> Do you think it will be better to just to add the age to the
> score?
>
>                if (xid_age >=3D effective_xid_failsafe_age)
>                        xid_score +=3D (double) xid_age;
>                if (mxid_age >=3D effective_mxid_failsafe_age)
>                        mxid_score +=3D (double) mxid_age
>
> For most cases, this should be high enough to to make the
> score high enough to sort to the top, as mentioned in the
> comments:
Agree, +1.The calculation resulted in a very large number. I previously
suggested modifying the algorithm like this:
effective_xid_failsafe_age =3D (vacuum_failsafe_age +
autovacuum_freeze_max_age) / 2.0. Typically, the `vacuum_failsafe_age`
parameter is rarely adjusted by DBAs.My view has always been that tables
whose age cannot be reduced should be prioritized, while we should try to
avoid tables whose age is already close to vacuum_failsafe_age.


Thanks


On Wed, Mar 11, 2026 at 9:08=E2=80=AFAM Sami Imseih <samimseih@gmail.com> w=
rote:

> > Just a few things:
> >
> > 1/
> > +        Oid            xid_age;
> > +        Oid            mxid_age;
> >
> > Is using Oid here intentional? I'm curious why not use uint32 for
> clarity?
> >
> > 2/
> > The new GUC docs says  "...component of the score...", but without
> > introducing the concept of the prioritization score.
> > I think we should expand a bit more on this topic to help a user
> > understand and tune these more effectively. Attached is my
> > proposal for the docs. I tried to keep it informative without
> > being too verbose, and avoided making specific recommendations.
>
> My apologies. I found something else that may need
> addressing.
>
> +               if (xid_age >=3D effective_xid_failsafe_age)
> +                       xid_score =3D pow(xid_score, Max(1.0, (double)
> xid_age / 100000000));
> +               if (mxid_age >=3D effective_mxid_failsafe_age)
> +                       mxid_score =3D pow(mxid_score, Max(1.0, (double)
> mxid_age / 100000000));
> +
>
> The current scaling calculation for force_vacuum could lead to
> exorbitantly high scores.
> Using DEBUG3 and consume_xids_until(2000000000), notice how the score goe=
s
> from 7.93 to 661828682916018.125 once past failsafe age.
>
> 36), anl: 0 (threshold 97929), score: 7.930
> 2026-03-10 19:41:11.979 CDT [74007] DEBUG:  foo: vac: 0 (threshold
> 195809), ins: 0 (threshold 176836), anl: 0 (threshold 97929), score:
> 7.930
> 2026-03-10 19:41:32.062 CDT [74038] DEBUG:  foo: vac: 0 (threshold
> 195809), ins: 0 (threshold 176836), anl: 0 (threshold 97929), score:
> 661828682916018.125
> 2026-03-10 19:41:32.063 CDT [74038] DEBUG:  foo: vac: 0 (threshold
> 195809), ins: 0 (threshold 176836), anl: 0 (threshold 97929), score:
> 661828682916018.125
> 2026-03-10 19:41:51.961 CDT [74066] DEBUG:  foo: vac: 0 (threshold
> 195809), ins: 0 (threshold 176836), anl: 0 (threshold 97929), score:
> 26761249940789168.000
>
> Do you think it will be better to just to add the age to the
> score?
>
>                if (xid_age >=3D effective_xid_failsafe_age)
>                        xid_score +=3D (double) xid_age;
>                if (mxid_age >=3D effective_mxid_failsafe_age)
>                        mxid_score +=3D (double) mxid_age
>
> For most cases, this should be high enough to to make the
> score high enough to sort to the top, as mentioned in the
> comments:
>
> +                * As in vacuum_xid_failsafe_check(), the effective
> failsafe age is no
> +                * less than 105% the value of the respective
> *_freeze_max_age
> +                * parameter.  Note that per-table settings could
> result in a low
> +                * score even if the table surpasses the failsafe
> settings.  However,
> +                * this is a strange enough corner case that we don't
> bother trying to
> +                * handle it.
> +                */
>
> --
> Sami Imseih
> Amazon Web Services (AWS)
>
>
>

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

<div dir=3D"ltr">HI Sami<div>&gt; Do you think it will be better to just to=
 add the age to the<br>&gt; score?<br>&gt; <br>&gt; =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0if (xid_age &gt;=3D effective_xid_failsaf=
e_age)<br>&gt; =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0xid_score +=3D (double) xid_age;<br>&gt; =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0if (mxid_age &gt;=3D effective=
_mxid_failsafe_age)<br>&gt; =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0mxid_score +=3D (double) mxid_age<br>=
&gt; <br>&gt; For most cases, this should be high enough to to make the<br>=
&gt; score high enough to sort to the top, as mentioned in the<br>&gt; comm=
ents:<br></div><div>Agree, +1.The calculation resulted in a very large numb=
er.=C2=A0I previously suggested modifying the algorithm like this:</div><di=
v>effective_xid_failsafe_age =3D (vacuum_failsafe_age + autovacuum_freeze_m=
ax_age) / 2.0. Typically, the `vacuum_failsafe_age` parameter is rarely adj=
usted by DBAs.My view has always been that tables whose age cannot be reduc=
ed should be prioritized, while we should try to avoid tables whose age is =
already close to vacuum_failsafe_age.<br></div><div><br></div><div><br></di=
v><div><br></div><div><br></div><div>Thanks<br><div><br></div></div></div><=
br><div class=3D"gmail_quote gmail_quote_container"><div dir=3D"ltr" class=
=3D"gmail_attr">On Wed, Mar 11, 2026 at 9:08=E2=80=AFAM Sami Imseih &lt;<a =
href=3D"mailto:samimseih@gmail.com">samimseih@gmail.com</a>&gt; wrote:<br><=
/div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;bo=
rder-left-width:1px;border-left-style:solid;border-left-color:rgb(204,204,2=
04);padding-left:1ex">&gt; Just a few things:<br>
&gt;<br>
&gt; 1/<br>
&gt; +=C2=A0 =C2=A0 =C2=A0 =C2=A0 Oid=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 xid_age;<br>
&gt; +=C2=A0 =C2=A0 =C2=A0 =C2=A0 Oid=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 mxid_age;<br>
&gt;<br>
&gt; Is using Oid here intentional? I&#39;m curious why not use uint32 for =
clarity?<br>
&gt;<br>
&gt; 2/<br>
&gt; The new GUC docs says=C2=A0 &quot;...component of the score...&quot;, =
but without<br>
&gt; introducing the concept of the prioritization score.<br>
&gt; I think we should expand a bit more on this topic to help a user<br>
&gt; understand and tune these more effectively. Attached is my<br>
&gt; proposal for the docs. I tried to keep it informative without<br>
&gt; being too verbose, and avoided making specific recommendations.<br>
<br>
My apologies. I found something else that may need<br>
addressing.<br>
<br>
+=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0if (xid_age &gt;=3D=
 effective_xid_failsafe_age)<br>
+=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0xid_score =3D pow(xid_score, Max(1.0, (double)<br>
xid_age / 100000000));<br>
+=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0if (mxid_age &gt;=
=3D effective_mxid_failsafe_age)<br>
+=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0mxid_score =3D pow(mxid_score, Max(1.0, (double)<br>
mxid_age / 100000000));<br>
+<br>
<br>
The current scaling calculation for force_vacuum could lead to<br>
exorbitantly high scores.<br>
Using DEBUG3 and consume_xids_until(2000000000), notice how the score goes<=
br>
from 7.93 to 661828682916018.125 once past failsafe age.<br>
<br>
36), anl: 0 (threshold 97929), score: 7.930<br>
2026-03-10 19:41:11.979 CDT [74007] DEBUG:=C2=A0 foo: vac: 0 (threshold<br>
195809), ins: 0 (threshold 176836), anl: 0 (threshold 97929), score:<br>
7.930<br>
2026-03-10 19:41:32.062 CDT [74038] DEBUG:=C2=A0 foo: vac: 0 (threshold<br>
195809), ins: 0 (threshold 176836), anl: 0 (threshold 97929), score:<br>
661828682916018.125<br>
2026-03-10 19:41:32.063 CDT [74038] DEBUG:=C2=A0 foo: vac: 0 (threshold<br>
195809), ins: 0 (threshold 176836), anl: 0 (threshold 97929), score:<br>
661828682916018.125<br>
2026-03-10 19:41:51.961 CDT [74066] DEBUG:=C2=A0 foo: vac: 0 (threshold<br>
195809), ins: 0 (threshold 176836), anl: 0 (threshold 97929), score:<br>
26761249940789168.000<br>
<br>
Do you think it will be better to just to add the age to the<br>
score?<br>
<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0if (xid_age &gt;=3D =
effective_xid_failsafe_age)<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0xid_score +=3D (double) xid_age;<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0if (mxid_age &gt;=3D=
 effective_mxid_failsafe_age)<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0mxid_score +=3D (double) mxid_age<br>
<br>
For most cases, this should be high enough to to make the<br>
score high enough to sort to the top, as mentioned in the<br>
comments:<br>
<br>
+=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 * As in vacuum_xid=
_failsafe_check(), the effective<br>
failsafe age is no<br>
+=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 * less than 105% t=
he value of the respective *_freeze_max_age<br>
+=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 * parameter.=C2=A0=
 Note that per-table settings could<br>
result in a low<br>
+=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 * score even if th=
e table surpasses the failsafe<br>
settings.=C2=A0 However,<br>
+=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 * this is a strang=
e enough corner case that we don&#39;t<br>
bother trying to<br>
+=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 * handle it.<br>
+=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 */<br>
<br>
--<br>
Sami Imseih<br>
Amazon Web Services (AWS)<br>
<br>
<br>
</blockquote></div>

--000000000000183b55064cb79e96--