Feedback-ID: i675e48f3:Fastmail
MIME-Version: 1.0
Date: Thu, 19 Mar 2026 13:36:29 -0400
From: "Greg Burd" <greg@burd.me>
To: "Nathan Bossart" <nathandbossart@gmail.com>
Cc: "David Rowley" <dgrowleyml@gmail.com>,
 "Sami Imseih" <samimseih@gmail.com>, "Robert Haas" <robertmhaas@gmail.com>,
 "Robert Treat" <rob@xzilla.net>,
 "Jeremy Schneider" <schneider@ardentperf.com>,
 pgsql-hackers <pgsql-hackers@postgresql.org>
Message-Id: <74279eb3-8e3a-4b76-a56c-10efce2ff41e@app.fastmail.com>
In-Reply-To: <abwbDTu6OO6Izp4o@nathan>
References: <abGP87A3JPIXDG2I@nathan>
 <CAA5RZ0tWonKY=E5U51i-J8s2EcRoLqtUQyWv+pQ4_rjFKSn+AA@mail.gmail.com>
 <abGmNAVIPSqX124E@nathan>
 <CAA5RZ0sZE-gfJ0c9HJkOk9XeFQwZL2wuJwrtOX+ZfUBLDcpFMA@mail.gmail.com>
 <abMR6MQGQyyKWep4@nathan>
 <CAApHDvqHXYCb=x0hHeR1e21ksqFNk4q3_zoPk6V-LLu0VL491Q@mail.gmail.com>
 <abrOO8GCgkhMXvcq@nathan>
 <bff7dc5a-f839-449b-b56a-aab36cd3e39d@app.fastmail.com>
 <CAApHDvpnR5zKb7LSB0idj4RPsV+=Dhtk+c5H0cZ4evN2hFDwCQ@mail.gmail.com>
 <3ca1e398-c787-47e9-9afc-8e298b94dac0@app.fastmail.com>
 <abwbDTu6OO6Izp4o@nathan>
Subject: Re: another autovacuum scheduling thread
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/74279eb3-8e3a-4b76-a56c-10efce2ff41e%40app.fastmail.com>
Precedence: bulk


On Thu, Mar 19, 2026, at 11:49 AM, Nathan Bossart wrote:
> On Thu, Mar 19, 2026 at 09:49:34AM -0400, Greg Burd wrote:
>> My concern isn't that wraparound vacuums are inherently alarming, I a=
gree
>> with you that reaching freeze_max_age isn't a crisis. The issue is a
>> scoring-scale problem in the gap between freeze_max_age (200M) and
>> failsafe age (1.6B).
>>=20
>> In that 1.4B XID window, force_vacuum tables have XID scores of 1.0=E2=
=80=938.0
>> (age/freeze_max_age), while typical active tables accumulate dead-tup=
le
>> scores of 18=E2=80=9370+ within hours of their last vacuum. The expon=
ential boost
>> doesn't activate until failsafe age, so force_vacuum tables are
>> systematically outranked by routine bloat cleanup for what could be d=
ays
>> or weeks in production.
>
> I think "systematically outranked" makes the problem sound worse than =
it
> is.  Once the freeze age is reached, the table is going to get added t=
o the
> list no matter what, it just might be sorted lower.

Yeah, that was a bit of hyperbole on my part. :)

>>> Having said that, I'd not realised that Nathan capped the new GUCs at
>>> 1.0. I think we should allow those to be set higher, likely at least
>>> to 10.0.
>>=20
>> That would definitely help. If autovacuum_freeze_score_weight could be
>> set to 8.0=E2=80=9310.0, DBAs could manually restore the priority we =
want.
>
> Done in the attached.

+1

>>> Maybe we could consider adjusting the code that's setting the
>>> xid_score/mxid_score so that we start scaling the score aggressively
>>> when if (xid_age >=3D effective_xid_failsafe_age /
>>> Max(autovacuum_freeze_score_weight,1.0)) becomes true
>>=20
>> This is clever, it would make the aggressive scaling kick in earlier =
when
>> the weight is higher. At weight=3D8.0, you'd get exponential boost st=
arting
>> at 200M (failsafe/8) instead of 1.6B.
>
> Seems reasonable.  I've added this, too.

+1

> Something else we might want to
> consider is scaling the score once the freeze age is reached, just much
> less aggressively than we do at the failsafe age.  It probably doesn't=
 make
> sense to start scaling too much at 200M, but at 1.5B, yeah, we should
> probably process the table sooner than later.

So a scaling factor relative to some point like 200M?  Maybe... but for =
now I think what you have in v13 is about right and a solid improvement =
over what's there now.

> --=20
> nathan
>
> Attachments:
> * v13-0001-autovacuum-scheduling-improvements.patch

LGTM!

best.

-greg