Re: autovacuum prioritization

On Wed, Jan 26, 2022 at 6:46 PM Greg Stark <stark(at)mit(dot)edu> wrote:
> One thing I've been wanting to do something about is I think
> autovacuum needs to be a little cleverer about when *not* to vacuum a
> table because it won't do any good.

I agree.

> I was thinking of just putting a check in before kicking off a vacuum
> and if the globalxmin is a significant fraction of the distance to the
> relfrozenxid then instead log a warning. Basically it means "we can't
> keep the bloat below the threshold due to the idle transactions et al,
> not because there's insufficient i/o bandwidth".

Unfortunately, XID distances don't tell us much, because the tuples
need not be uniformly distributed across the XID space. In fact, it
seems highly likely that they will be very non-uniformly distributed,
with a few transactions having created a lot of dead tuples and most
having created none. Therefore, it's pretty plausible that a vacuum
that permits relfrozenxid++ could solve every problem we have. If we
knew something about the distribution of dead XIDs in the table, then
we could make an intelligent judgement about whether vacuuming would
be useful. But otherwise I feel like we're just guessing, so instead
of really fixing the problem we'll just be making it happen in a set
of cases that's even harder to grasp.

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