Re: Berserk Autovacuum (let's save next Mandrill)

On Wed, Mar 27, 2019 at 5:32 PM Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> wrote:
> * certain tables would have some sort of partial scan that sets the
> visibility map. There's no reason to invoke the whole vacuuming
> machinery. I don't think this is limited to append-only tables, but
> rather those are just the ones that are affected the most.

I think this is a really good idea, but in order for it to work well I
think we would need to have some kind of estimate of autovacuum
pressure.

If we know that we're currently fairly on top of things, and there is
not much for autovacuum to do, periodically vacuuming a chunk of some
table that has a lot of unset visibility-map bits is probably a good
idea. However, we can't possibly guess how aggressively to do this if
we have no idea how long it's going to be before we need to vacuum
that table for real. If the number of XIDs remaining until the table
gets a wraparound vacuum is X, and the number of XIDs being consumed
per day is Y, we can estimate that in roughly X/Y days, we're going to
need to do a wraparound vacuum. That value might be in the range of
months, or in the range of hours.

If it's months, we probably want limit vacuum to working at a pretty
slow rate, say 1% of the table size per hour or something. If it's in
hours, we need to be a lot more aggressive. Right now we have no
information to tell us which of those things is the case, so we'd just
be shooting in the dark.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company