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>
In-Reply-To: 
 <CAA5RZ0seLxDmH9UftUA3vS8DrW+7T9HV5XK8yAuQxA8BaRkg2g@mail.gmail.com>
From: Sami Imseih <samimseih@gmail.com>
Date: Tue, 10 Mar 2026 20:08:26 -0500
Message-ID: 
 <CAA5RZ0vfhAnFBp4HrBQc+ALaJMx6vCvMtnBi39ST_4nH9PZEjA@mail.gmail.com>
Subject: Re: another autovacuum scheduling thread
To: Nathan Bossart <nathandbossart@gmail.com>
Cc: 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: text/plain; charset="UTF-8"
Archived-At: 
 <https://www.postgresql.org/message-id/CAA5RZ0vfhAnFBp4HrBQc%2BALaJMx6vCvMtnBi39ST_4nH9PZEjA%40mail.gmail.com>
Precedence: bulk

> 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 >= effective_xid_failsafe_age)
+                       xid_score = pow(xid_score, Max(1.0, (double)
xid_age / 100000000));
+               if (mxid_age >= effective_mxid_failsafe_age)
+                       mxid_score = 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 goes
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 >= effective_xid_failsafe_age)
                       xid_score += (double) xid_age;
               if (mxid_age >= effective_mxid_failsafe_age)
                       mxid_score += (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)