Re: autovacuum not prioritising for-wraparound tables

Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> writes:
> So if we're to discuss this, here's what I had in mind:

> 1. for-wraparound tables always go first; oldest age(relfrozenxid) are
> sorted earlier. For tables of the same age, consider size as below.

It seems unlikely that age(relfrozenxid) will be identical for multiple
tables often enough to worry about, so the second part of that seems
like overcomplication.

> 2. for other tables, consider floor(log(size)). This makes tables of
> sizes in the same ballpark be considered together.

> 3. For tables of similar size, consider
> (n_dead_tuples - threshold) / threshold.
> "threshold" is what gets calculated as the number of tuples over which
> a table is considered for vacuuming. This number, then, is a relative
> measure of how hard is vacuuming needed.

The floor(log(size)) part seems like it will have rather arbitrary
behavioral shifts when a table grows just past a log boundary. Also,
I'm not exactly sure whether you're proposing smaller tables first or
bigger tables first, nor that either of those orderings is a good thing.

I think sorting by just age(relfrozenxid) for for-wraparound tables, and
just the n_dead_tuples measurement for others, is probably reasonable
for now. If we find out that has bad behaviors then we can look at how
to fix them, but I don't think we have enough understanding yet of what
the bad behaviors might be.

regards, tom lane