Re: new autovacuum criterion for visible pages

On Sun, Jan 22, 2017 at 3:27 AM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> All,
>
> * Simon Riggs (simon(at)2ndquadrant(dot)com) wrote:
>> On 12 August 2016 at 01:01, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> > Michael Paquier <michael(dot)paquier(at)gmail(dot)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.
>

Another possible advantage of tracking INSERTs is for hash indexes
where after split we need to remove tuples from buckets that underwent
split recently.

--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com