MIME-Version: 1.0
References: <abAzhNqs5F8sJDdm@nathan> <abBEiOICOdbbhsEI@nathan>
 <CAA5RZ0seLxDmH9UftUA3vS8DrW+7T9HV5XK8yAuQxA8BaRkg2g@mail.gmail.com>
 <CAA5RZ0vfhAnFBp4HrBQc+ALaJMx6vCvMtnBi39ST_4nH9PZEjA@mail.gmail.com>
 <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>
In-Reply-To: <bff7dc5a-f839-449b-b56a-aab36cd3e39d@app.fastmail.com>
From: David Rowley <dgrowleyml@gmail.com>
Date: Fri, 20 Mar 2026 00:44:25 +1300
Message-ID: 
 <CAApHDvpnR5zKb7LSB0idj4RPsV+=Dhtk+c5H0cZ4evN2hFDwCQ@mail.gmail.com>
Subject: Re: another autovacuum scheduling thread
To: Greg Burd <greg@burd.me>
Cc: Nathan Bossart <nathandbossart@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>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAApHDvpnR5zKb7LSB0idj4RPsV%2B%3DDhtk%2Bc5H0cZ4evN2hFDwCQ%40mail.gmail.com>
Precedence: bulk

On Thu, 19 Mar 2026 at 22:57, Greg Burd <greg@burd.me> wrote:
> I'm late in the review process. I know David Rowley proposed the unified =
scoring approach that became the foundation of this patch, and I think that=
's a great direction. However, I'm concerned that the patch's default scori=
ng weights don't give XID-age urgency sufficient priority over dead-tuple u=
rgency. The weight GUCs (autovacuum_vacuum_score_weight, etc.) can address =
this, but they max at 1.0, meaning you can only reduce dead-tuple priority,=
 not increase XID priority.

I think that it would be good if you could state *why* you disagree
with the proposed scoring rather than *that* you disagree. All this
stuff was talked about around [1]. For me, I don't see what's
particularly alarming about a table reaching
autovaccum_max_freeze_age. That GUC is set to less than 10% of the
total transaction ID space of where the table must be frozen. Why is
it you think these should take priority over everything else? SLRU
buffers are configurable since v17, so having to lookup the clog for a
wider range of xids isn't as big an issue as it used to be, plus
memory and L3 sizes are bigger than they used to be. Is slow clog
lookups what you're concerned about? You didn't really say.

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. 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. Then, if people
want to play it safer, then they can set
autovacuum_freeze_score_weight =3D 2.0 and have the aggressive scaling
kick in at 800 million, or whatever half of effective_xid_failsafe_age
is set to. You could set yours to 8.0, if you really want tables over
autovacuum_freeze_max_age to take priority over everything else. I
just don't see or understand the reason why you'd want to.

It's a fairly common misconception that a wraparound vacuum is
something to be alarmed about. Maybe you've fallen for that? I recall
a few proposals to adjust the wording that's shown in pg_stat_activity
to make them seem less alarming.

David

[1] https://www.postgresql.org/message-id/CAApHDvqobtKMwJbhKB_c%3D3-TM%3DTg=
S3bcuvzcWMm3ee1c0mz9hw%40mail.gmail.com