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

On 10/21/14, 4:36 PM, Jeff Janes wrote:
> On Mon, Oct 20, 2014 at 5:46 PM, Andres Freund <andres@2ndquadrant.com <mailto:andres@2ndquadrant.com>> wrote:
>
>     On 2014-10-20 17:43:26 -0700, Josh Berkus wrote:
>     > On 10/20/2014 05:39 PM, Jim Nasby wrote:
>     > > Or maybe vacuum isn't the right way to handle some of these scenarios.
>     > > It's become the catch-all for all of this stuff, but maybe that doesn't
>     > > make sense anymore. Certainly when it comes to dealing with inserts
>     > > there's no reason we *have* to do anything other than set hint bits and
>     > > possibly freeze xmin.
>     >
>     > +1
>
>     A page read is a page read. What's the point of heaving another process
>     do it?
>
>
> It is only a page read if you have to read the page.  It would seem optimal to have bgwriter adventitiously set hint
bitsand vm bits, because that is the last point at which the page can be changed without risking that it be written out
twice.At that point, it has been given the maximum amount of time it can be given for the interested transactions to
havecommitted and to have aged past the xmin horizon.  I seem to recall that the main problem with that, though, is
thatyou must be attached to a database in order to determine visibility, and bgwriter is not attached to a database.
 

It's also a bit more complex than a simple question of "is the page still in shared buffers". Our *real* last chance is
whenthe page is about to be evicted from the filesystem cache; after that reading it back it will be extremely
expensive(relatively speaking).
 

I think it's worth considering this, because if you have any moderate length transactions on a busy database bgwriter
won'tbe able to help much; you'll be burning through shared buffers too quickly.
 
-- 
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com