MIME-Version: 1.0
References: 
 <CAA5RZ0s7k6yve1Bq1OY+aXU3ifc_fvOiYkpE2VxwrUcHr2AtTg@mail.gmail.com>
 <CAApHDvpvkKYB6z-i1kiO9iePF0fz18-CaS9DZEWnSx1eO0RGFQ@mail.gmail.com>
 <CAA5RZ0tPvWLVXhDMC3t3LhwTM-OnWHSvPo7tnZzOphR=HB4kgQ@mail.gmail.com>
 <ab2uoj5yU8CVUJ_H@nathan>
 <CALj2ACV0HboeYiT1Z-=rb509WL3N0H9LsWRPqc_xUMEM-TM85g@mail.gmail.com>
 <acFan6knPZljm1Ay@nathan>
 <CAA5RZ0tz55uoQfcvLwvRWRPM0ry6SGfZ=-enQ4F0kaUgfPh+Vg@mail.gmail.com>
 <acGqIhkP6xo4Vv1l@nathan>
 <CAMFBP2q-oMzpEua9Hdcpag15VGZn7aEO-uaHZ4yVEO_HCm55_Q@mail.gmail.com>
 <CAApHDvqQN-B2sQov8nsfZOmx-VeJMauSf4kLa3A8LsK1tUyBNw@mail.gmail.com>
 <acLGoktU2lp4SIlU@nathan>
In-Reply-To: <acLGoktU2lp4SIlU@nathan>
From: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
Date: Wed, 25 Mar 2026 14:12:16 -0700
Message-ID: 
 <CALj2ACUTuHZugAgbDk-NNPY_CPbw+c4T=HkRvvqq-FKqnVb24A@mail.gmail.com>
Subject: Re: another autovacuum scheduling thread
To: Nathan Bossart <nathandbossart@gmail.com>
Cc: David Rowley <dgrowleyml@gmail.com>, Jim Nasby <jnasby@upgrade.com>,
	Sami Imseih <samimseih@gmail.com>, Greg Burd <greg@burd.me>,
 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/CALj2ACUTuHZugAgbDk-NNPY_CPbw%2Bc4T%3DHkRvvqq-FKqnVb24A%40mail.gmail.com>
Precedence: bulk

Hi,

On Tue, Mar 24, 2026 at 10:15=E2=80=AFAM Nathan Bossart
<nathandbossart@gmail.com> wrote:
>
> Agreed.  Here's a first try at that.  I also updated the DEBUG3 at the en=
d
> of relation_needs_vacanalyze() to show the individual scores.  The commen=
t
> above that function might need some work, and we might need a bit of
> additional commentary elsewhere.

Sorry for the late response. Thank you for sending the latest patch.

+1 for getting the main patch in first and then the scoring stats view.

I looked closely at the v15 patch today and it LGTM.

A couple of thoughts (I don't intend to block the main patch from
getting in :)):

1/ If a large bloated table scores highest and takes hours to vacuum,
other tables' XID ages keep advancing (not to the failsafe limits yet,
but approaching close to them). By the time the autovacuum worker
finishes, a table that now needs freezing is still stuck at its old
position in the sorted table list.

Would it make sense to recompute scores and re-sort the remaining
table list after each table is processed in do_autovacuum()'s main
loop - say, after a certain amount of time spent vacuuming the large
table(s)? This would catch the above scenarios. I see that the scores
per table are being calculated in relation_needs_vacanalyze, but they
are ignored in the recheck path (table_recheck_autovac ->
recheck_relation_needs_vacanalyze -> relation_needs_vacanalyze).

IMHO, this could be a useful future addition.

2/ Nit: IIUC, autovacuum_vacuum_score_weight and other existing vacuum
thresholds and scale factor GUCs are the ones to tune to make the
tables prioritized first. I still think it would be nice to have a
document explaining these scenarios - maybe after the main patch gets
in.

--
Bharath Rupireddy
Amazon Web Services: https://aws.amazon.com