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

Alvaro Herrera <alvherre@2ndquadrant.com> 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.)

Wouldn't we get substantially the same thing just by counting tuple
inserts toward the autovacuum vacuum threshold?  I mean, it unless
the table is due for wraparound prevention autovacuum, it will only
visit pages that don't have the all-visible bit set, right?  And
how much work would that do beyond what you're describing if none
of the tuples are dead?

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company