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.
Tracking the INSERTs as a reason to VACUUM is also very natural when you
consider the need to update BRIN indexes. I am a bit worried that if we
focus just on if the VM needs to be updated or not that we might miss
out on cases where we need to VACUUM because the BRIN indexes are out of
date.
Thanks!
Stephen