Re: another autovacuum scheduling thread

On Fri, Oct 10, 2025 at 3:44 PM Nathan Bossart <nathandbossart(at)gmail(dot)com> wrote:
> On Fri, Oct 10, 2025 at 02:42:57PM -0400, Robert Haas wrote:
> > I think this is a reasonable starting point, although I'm surprised
> > that you chose to combine the sub-scores using + rather than Max.
>
> My thinking was that we should consider as many factors as we can in the
> score, not just the worst one. If a table has medium bloat and medium
> wraparound risk, should it always be lower in priority to something with
> large bloat and small wraparound risk? It seems worth exploring. I am
> curious why you first thought of Max.

The right answer depends a good bit on how exactly you do the scoring,
but it seems to me that it would be easy to overweight secondary
problems. Consider a table with an XID age of 900m and an MXID age of
900m and another table with an XID age of 1.8b. I think it is VERY
clear that the second one is MUCH worse; but just adding things up
will make them seem equal.

> Agreed. I need to think about this some more. While I'm optimistic that
> we could come up with some sort of normalization framework, I deperately
> want to avoid super complicated formulas and GUCs, as those seem like
> sure-fire ways of ensuring nothing ever gets committed.

IMHO, the trick here is to come up with something that's neither too
simple nor too complicated. If it's too simple, we'll easily come up
with cases where it sucks, and possibly where it's worse than what we
do now (an impressive achievement, to be sure). If it's too
complicated, it will be full of arbitrary things that will provoke
dissent and probably not work out well in practice. I don't think we
need something dramatically awesome to make a change to the status
quo, but if it's extremely easy to think up simple scenarios in which
a given idea will fail spectacularly, I'd be inclined to suspect that
there will be a lot of real-world spectacular failures.

--
Robert Haas
EDB: http://www.enterprisedb.com