Re: new autovacuum criterion for visible pages

On Thu, Aug 11, 2016 at 9:29 PM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
> On Thu, Aug 11, 2016 at 8:32 AM, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>> On Thu, Aug 11, 2016 at 2:09 AM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
>>> I wanted to create a new relopt named something like
>>> autovacuum_vacuum_pagevisible_factor which would cause autovacuum to
>>> vacuum a table once less than a certain fraction of the relation's
>>> pages are marked allvisible.
>>>
>>
>> Why would it more convenient for a user to set such a parameter as
>> compare to existing parameters (autovacuum_vacuum_threshold +
>> autovacuum_vacuum_scale_factor)?
>
> Insertions and HOT-updates clear vm bits but don't increment the
> counters that those existing parameters are compared to.
>
> Also, the relationship between number of updated/deleted rows and the
> number of hint-bits cleared can be hard to predict due to possible
> clustering of the updates into the same blocks. So it would be hard
> to know what to set the values to.
>

Okay. What I was slightly worried about was that how many users can
understand *pagevisible_* parameters as compare to what we have now
(number of updated/deleted tuples). However if we have some mechanism
where autovacuum can be triggered automatically based on
pagevisibility, then I think that would be quite beneficial (not sure,
if such a mechanism can be feasible).

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