MIME-Version: 1.0
References: 
 <CAApHDvoM5MEHHBc0TNdrzkpq39WdEHSZhdWrtnx9zOWNXTSFGw@mail.gmail.com>
 <aP-YgrcPi0EhgR9x@nathan>
 <CAApHDvpOq09uVq7aXcuSBPAhZBTfAL-m2c4FOF2PphFe-YcnRg@mail.gmail.com>
 <aQEvm40W3aVizp5Q@nathan>
 <CAA5RZ0sdhUjVVKYbBGs1qFsYC3-Mn+as=K5v8ydVGR5iziabFQ@mail.gmail.com>
 <aQI39ln_jZ8qorLE@nathan>
 <CA+Tgmoa_5aC0w1fG7pLhev+F-GtRhQ2OzePy7t059c9JTnvjow@mail.gmail.com>
 <aQPSYD11NDoREZsg@nathan>
 <CAA5RZ0uo-nU9KqgXV5Tcf8aWWQkxsb5BDpkb-2Qfwbw-UVVaUA@mail.gmail.com>
 <CAA5RZ0tpV3PRHejTGG5-LSsqNKKV0qP=SDvurs8wj7pTk7jYJw@mail.gmail.com>
 <aQUYP1WjrEP3buQz@nathan>
 <CAApHDvqe7ee=vobWe4GVAt2gm_H6eiGNZeo_dEMptvHYAkibBA@mail.gmail.com>
 <CAApHDvoCFQxaQjUncTAtCRFAeANe2tpc-WCkJue=FaXRYOkV=Q@mail.gmail.com>
 <CAA5RZ0sw+9rEaW9taNpRZWvuLYMjRa9iibneGfB2ftNSUHT0Ww@mail.gmail.com>
 <CAApHDvq_j+GVqX_ZAmvn236Mgg5OYQ6_s9kVsyoo1tJa2RJ=2w@mail.gmail.com>
 <CAA5RZ0udjEYJupob5tv3286e28bMpajgsy+4nAbxg73YyigZFw@mail.gmail.com>
 <CAApHDvq9ecYSetXUhjMc7E_Jcc7gxxRkA0LosX9PP1-jM_Ak8A@mail.gmail.com>
In-Reply-To: 
 <CAApHDvq9ecYSetXUhjMc7E_Jcc7gxxRkA0LosX9PP1-jM_Ak8A@mail.gmail.com>
From: Sami Imseih <samimseih@gmail.com>
Date: Tue, 11 Nov 2025 14:25:36 -0600
Message-ID: 
 <CAA5RZ0sJGg209gSpEzLpO3DPiF8r8n_xVbQDnd_92BV+-5kvDA@mail.gmail.com>
Subject: Re: another autovacuum scheduling thread
To: David Rowley <dgrowleyml@gmail.com>
Cc: Nathan Bossart <nathandbossart@gmail.com>,
 Robert Haas <robertmhaas@gmail.com>,
	Jeremy Schneider <schneider@ardentperf.com>, pgsql-hackers@postgresql.org
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAA5RZ0sJGg209gSpEzLpO3DPiF8r8n_xVbQDnd_92BV%2B-5kvDA%40mail.gmail.com>
Precedence: bulk

> On Sat, 8 Nov 2025 at 08:23, Sami Imseih <samimseih@gmail.com> wrote:
> > > I'm confused at why we'd have set up our autovacuum trigger points as
> > > they are today because we think those are good times to do a
> > > vacuum/analyze, but then prioritise on something completely different=
.
> > > Surely if we think 20% dead tuples is worth a vacuum, we must
> > > therefore think that 40% dead tuples are even more worthwhile?!
> >
> > Sure, but thresholds alone don't indicate anything about the how quick
> > the table can be vacuumed, # of indexes, per table a/v settings, etc.
> > The average a/v time is a good proxy to determine this.
> >
> > What I am suggesting here is we think beyond thresholds for
> > prioritization, and to give a chance for more eligible tables to get
> > autovacuumed rather than workers being saturated on some
> > of the slowest-to-vacuum tables.
>
> Can you define "more eligible" here?

What I mean by =E2=80=9Cmore eligible=E2=80=9D is that once a worker has it=
s list of tables
that meet the autovacuum thresholds, it=E2=80=99s trying to get through as =
many
of them as possible within some time window.

If the workers always go after the slowest tables first, they=E2=80=99ll sp=
end most
of that time on just a few heavy ones, and a lot of other eligible tables m=
ight
end up waiting much longer to get processed.

Eventually the slow tables will be the bottleneck anyway.

> I think I'm not really grasping this because I don't understand why
> faster-to-vacuum tables should be prioritised over slower-to-vacuum
> tables. Can you explain why you think this is important?

The thing I=E2=80=99m hoping to address is something I=E2=80=99ve seen many=
 times in practice.
Autovacuum workers can get stuck on specific large or slow tables, and when
that happens, users often end up running manual vacuums on those tables
just to keep things moving for the smaller/faster vacuumed tables.

Now, I am not so sure any type of autovacuum prioritization could actually
help in these cases. What does help is adding more autovacuum workers.

> if we have the autovacuum worker refresh the list and scores after
> it's done with a table and autovacuum_naptime has elapsed since the
> list was last refreshed?

That is an interesting idea, but refreshing the list that often may not
be such a good idea, it could be quite expensive on large catalogs.

--
Sami Imseih
Amazon Web Services (AWS)