Re: autovacuum not prioritising for-wraparound tables

On Fri, Jan 25, 2013 at 9:19 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>
> I think that to do this right, we need to consider not only the status
> quo but the trajectory. For example, suppose we have two tables to
> process, one of which needs a wraparound vacuum and the other one of
> which needs dead tuples removed. If the table needing the wraparound
> vacuum is small and just barely over the threshold, it isn't urgent;

But it being small, it also won't take long to vacuum. Why not just do it?

> but if it's large and way over the threshold, it's quite urgent.
> Similarly, if the table which needs dead tuples removed is rarely
> updated, postponing vacuum is not a big deal, but if it's being
> updated like crazy, postponing vacuum is a big problem.

I don't see this as being the case. If it is being updated like
crazy, it doesn't matter whether it meets the threshold to have tuples
removed *right at the moment* or not. It will meet that threshold
soon. If you can't keep up with that need with your current settings,
you have a steady-state problem. Changing the order, or not changing
the order, isn't going to make a whole lot of difference, you need to
overcome the steady-state problem.

> Categorically
> putting autovacuum wraparound tables ahead of everything else seems
> simplistic, and thinking that more dead tuples is more urgent than
> fewer dead tuples seems *extremely* simplistic.
>
> I ran across a real-world case where a user had a small table that had
> to be vacuumed every 15 seconds to prevent bloat. If we change the
> algorithm in a way that gives other things priority over that table,

Eventually an anti-wrap around is going to be done, and once it starts
it does have priority, because things already underway don't get
preempted. Have they ever reached that point? Did it cause problems?

> then that user could easily get hosed when they install a maintenance
> release containing this change.

Yeah, I don't know that back-patching is a good idea, or at least not soon.

Cheers,

Jeff