Re: new autovacuum criterion for visible pages

On 8/11/16 10:59 AM, Jeff Janes 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.

I'm wondering if also creating the same options for all-frozen pages
would be worthwhile. I don't see an obvious use for that, but maybe
someone else does (and adding both at once would presumably be the least
amount of work...)
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532) mobile: 512-569-9461