Re: [HACKERS] new autovacuum criterion for visible pages

All,

* Simon Riggs (simon@2ndquadrant.com) wrote:
> On 12 August 2016 at 01:01, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > Michael Paquier <michael.paquier@gmail.com> writes:
> >> In short, autovacuum will need to scan by itself the VM of each
> >> relation and decide based on that.
> >
> > That seems like a worthwhile approach to pursue.  The VM is supposed to be
> > small, and if you're worried it isn't, you could sample a few pages of it.
> > I do not think any of the ideas proposed so far for tracking the
> > visibility percentage on-the-fly are very tenable.
>
> Sounds good, but we can't scan the VM for every table, every minute.
> We need to record something that will tell us how many VM bits have
> been cleared, which will then allow autovac to do a simple SELECT to
> decide what needs vacuuming.
>
> Vik's proposal to keep track of the rows inserted seems like the best
> approach to this issue.

I tend to agree with Simon on this.  I'm also worried that an approach
which was based off of a metric like "% of table not all-visible" might
result in VACUUM running over and over on a table because it isn't able
to actually make any progress towards improving that percentage.  We'd
have to have some kind of "cool-off" period or something.

Tracking INSERTs and then kicking off a VACUUM based on them seems to
address that in a natural way and also seems like something that users
would generally understand as it's very similar to what we do for
UPDATEs and DELETEs.