public inbox for [email protected]
help / color / mirror / Atom feedFrom: Greg Burd <[email protected]>
To: Nathan Bossart <[email protected]>
Cc: David Rowley <[email protected]>
Cc: Sami Imseih <[email protected]>
Cc: Robert Haas <[email protected]>
Cc: Robert Treat <[email protected]>
Cc: Jeremy Schneider <[email protected]>
Cc: pgsql-hackers <[email protected]>
Subject: Re: another autovacuum scheduling thread
Date: Thu, 19 Mar 2026 13:36:29 -0400
Message-ID: <[email protected]> (raw)
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>
<[email protected]>
<CAApHDvpnR5zKb7LSB0idj4RPsV+=Dhtk+c5H0cZ4evN2hFDwCQ@mail.gmail.com>
<[email protected]>
<abwbDTu6OO6Izp4o@nathan>
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 agree
>> 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).
>>
>> In that 1.4B XID window, force_vacuum tables have XID scores of 1.0–8.0
>> (age/freeze_max_age), while typical active tables accumulate dead-tuple
>> scores of 18–70+ within hours of their last vacuum. The exponential boost
>> doesn't activate until failsafe age, so force_vacuum tables are
>> systematically outranked by routine bloat cleanup for what could be days
>> 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 to 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.
>>
>> That would definitely help. If autovacuum_freeze_score_weight could be
>> set to 8.0–10.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 >= effective_xid_failsafe_age /
>>> Max(autovacuum_freeze_score_weight,1.0)) becomes true
>>
>> This is clever, it would make the aggressive scaling kick in earlier when
>> the weight is higher. At weight=8.0, you'd get exponential boost starting
>> 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.
> --
> nathan
>
> Attachments:
> * v13-0001-autovacuum-scheduling-improvements.patch
LGTM!
best.
-greg
view thread (143+ messages) latest in thread
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]
Subject: Re: another autovacuum scheduling thread
In-Reply-To: <[email protected]>
* 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