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

On Mon, Oct 27, 2014 at 5:51 PM, Alvaro Herrera
<alvherre@2ndquadrant.com> wrote:
> Jeff Janes wrote:
>> It is only a page read if you have to read the page.  It would seem optimal
>> to have bgwriter adventitiously set hint bits and 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 have committed and
>> to have aged past the xmin horizon.  I seem to recall that the main problem
>> with that, though, is that you must be attached to a database in order to
>> determine visibility, and bgwriter is not attached to a database.
>
> Regarding tuple hint bits, I couldn't find any such limitation in
> SetHintBits, other than in MarkBufferDirtyHint there being some code
> that would cause trouble: it accesses MyPgXact, which bgwriter would
> obviously not have.  Maybe worth some experimentation ...
>
> I'm not sure about vm bits, though.  That's a whole different topic.

From a theoretical point of view, hint bits and VM bits present
mostly-similar issues; they are basically dependent on the state of
the transaction machinery, which for the most part is cluster-wide, so
it should be fine for a process to do that sort of work without being
bound to a specific database.   I think the biggest problem for either
is that the bgwriter can't look at the system catalogs to find out
whether a particular buffer contains a heap page or an index page (or
something else) and it needs to know that, which probably requires
another buffer flag.

From a practical point of view, there's a major performance difference
between the two operations: setting a VM bit requires emitting a WAL
record, whereas setting hint bits does not.  That might make it too
expensive to be worth doing, at least in some situations.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company