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

Andres Freund wrote:
> On 2014-10-09 18:03:00 -0300, 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.
> 
> Isn't that *precisely* what a plain vacuum run does?

Well, it also scans for dead tuples, removes them, and needs to go
through indexes to remove their references.  I'm thinking in something
very lightweight.  Otherwise, why don't we just reduce the
vacuum_scale_factor default to something very small, so that vacuum is
triggered more often?

-- 
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services