Re: Autovacuum fails to keep visibility map up-to-date in mostly-insert-only-tables

On 10/9/14, 4:03 PM, Alvaro Herrera wrote:
> Bruce Momjian wrote:
>
>> I agree this is a serious problem.  We have discussed various options,
>> but have not decided on anything.  The TODO list has:
>>
>>     https://wiki.postgresql.org/wiki/Todo
>>
>>     Improve setting of visibility map bits for read-only and insert-only
>>     workloads
>>     
>>       http://www.postgresql.org/message-id/20130906001437.GA29264@momjian.us
>
> I hate to repeat myself, but I think autovacuum could be modified to run
> actions other than vacuum and analyze.  In this specific case we could
> be running a table scan that checks only pages that don't have the
> all-visible bit set, and see if it can be set.  (Of course, this idea
> needs refinement to avoid running over and over when the bit cannot be
> set on some pages for whatever reason.)

If we go down that road we should also think about having it proactively set hint bits...
-- 
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com