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>
In-Reply-To: 
 <CAA5RZ0udjEYJupob5tv3286e28bMpajgsy+4nAbxg73YyigZFw@mail.gmail.com>
From: David Rowley <dgrowleyml@gmail.com>
Date: Tue, 11 Nov 2025 13:58:22 +1300
Message-ID: 
 <CAApHDvq9ecYSetXUhjMc7E_Jcc7gxxRkA0LosX9PP1-jM_Ak8A@mail.gmail.com>
Subject: Re: another autovacuum scheduling thread
To: Sami Imseih <samimseih@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"
Archived-At: 
 <https://www.postgresql.org/message-id/CAApHDvq9ecYSetXUhjMc7E_Jcc7gxxRkA0LosX9PP1-jM_Ak8A%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?

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?

I do understand that in your script that the OLTP tables received less
attention than unpatched, but it wasn't obvious to me why this was an
issue. If it's a case of autovacuum acting on a stale score after it
obtained the list of tables and their scores, do things look different
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?

David