Re: Piggybacking vacuum I/O

Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> Pavan Deolasee wrote:
>> It would also be interesting to investigate whether early setting of
>> hint bits can reduce subsequent writes of blocks. A typical case would
>> be a large table being updated heavily for a while, followed by SELECT
>> queries. The SELECT queries would set hint bits for the previously
>> UPDATEd tuples (old and new versions) and thus cause subsequent
>> writes of those blocks for what could have been read-only queries.

> This has been suggested before, but I don't see how this could work.
> How does the UPDATE transaction go back to the pages it wrote to update
> the hint bits, _after_ it committed?

I think what he's suggesting is deliberately not updating the hint bits
during a SELECT ... but that's surely misguided. If you don't set the
hint bit after discovering the transaction commit state, then the next
visitor of the tuple will have to repeat the clog lookup, meaning that
any such policy greatly increases clog read traffic and contention.

regards, tom lane