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

Alvaro Herrera <alvherre(at)2ndquadrant(dot)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