Re: another autovacuum scheduling thread - Mailing list pgsql-hackers

On Fri, 21 Nov 2025 at 10:16, Robert Haas <robertmhaas@gmail.com> wrote:
>
> On Thu, Nov 20, 2025 at 3:58 PM David Rowley <dgrowleyml@gmail.com> wrote:
> > before we need to make a decision. My vote is to use as much of that
> > time as possible rather than using it to allow people to dream up
> > hypothetical problems that might or might not exist.
>
> That seems a little harsh.

It wasn't intended to be offensive. It's an observation that there've
been quite a few posts on this thread about various extra things we
should account for in the score without any evidence that they're
worthy of a special case. I used "dream up" since I don't recall any
of those posts arriving with evidence of an actual problem or that the
proposed solution was a valid fix for it, and that the proposed
solution didn't make something else worse.

> That said, I accept your point that even if we were to agree that
> something ought to made tunable here, we would still have the problem
> of deciding exactly what GUCs or reloptions to add, and that might be
> hard to figure out without more information. Unfortunately, I have a
> feeling that unless you or someone here is planning to make a
> determined testing effort over the coming months, we're more likely to
> get feedback after final release than during development or even beta.

You might be right. Or after a week we might discover a good reason
why the percentage-over-threshold method is rubbish and revert it. The
key is probably in the way we act from getting no negative feedback.

I suspect the most likely area the new prioritisation order could
cause issues is from the lack of randomness. Will multiple workers
working into the same database be more likely to bump into each other
somehow in a bad way? Maybe that's a good area to focus testing.

> But I do also understand that you don't want us to be paralyzed and
> never move forward.

Yeah partly, but mostly I just really doubt that this matters that
much. It's been said on this thread already that prioritisation isn't
as important as the autovacuum-configured-to-run-too-slowly issue, and
I agree with that. I just find it hard to believe that the highly
volatile pg_class order has been just perfect all these years and that
sorting by percentage-over-threshold-desc will make things worse
overall. There was mention that pg_catalog tables are first in
pg_class, but I don't really agree with that as if I create some new
tables on a fresh database, I see those getting lower ctids than any
pg_catalog table. The space for that is finite, but there's no
shortage of other reasons for user tables to become mentioned in
pg_class before catalogue tables as the database gets used. I see that
table_beginscan_catalog() uses SO_ALLOW_SYNC too, so there's an extra
layer of randomness from sync scans. I don't recall any complaints
from the order autovacuum works on tables, so, to me, it just seems
strange to think that the volatile order of pg_class just happened to
be right all these years. I suspect what's happening is that the extra
bloat or stale statistics that people get as a result of the
pg_class-order autovacuum just gets unnoticed, ignored or attended to
via adjustments to the corresponding scale_factor reloption.

David