Re: heap vacuum & cleanup locks

On Tue, Nov 8, 2011 at 3:26 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Tue, Nov 8, 2011 at 2:26 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>>> I think we need to avoid long pin hold times generally.
>
>> In the case of a suspended sequential scan, which is the case where
>> this has most recently bitten me on a production system, it actually
>> seems rather unnecessary to hold the pin for a long period of time.
>> If we release the buffer pin, then someone could vacuum the buffer.
>
> This seems unlikely to be a productive line of thought.  The only way
> you could release buffer pin is if you first copied all the tuples you
> need out of the page, and that seems like an unacceptable performance
> hit.  We should not be penalizing foreground query operations for the
> benefit of background maintenance like VACUUM.  (The fact that we do
> an equivalent thing in btree index scans isn't an argument for doing
> it here, because the tradeoffs are very different.  In the index case,
> the amount of data to be copied is a great deal less; the length of
> time the lock would have to be held is often a great deal more; and
> releasing the lock quickly gives a performance benefit for other
> foreground operations, not only background maintenance.)
>
> It strikes me that the only case where vacuum now has to wait is where
> it needs to freeze an old XID.  Couldn't it do that without insisting on
> exclusive access?  We only need exclusive access if we're going to move
> data around, but we could have a code path in vacuum that just replaces
> old XIDs with FrozenXID without moving/deleting anything.

Holding buffer pins for a long time is a problem in Hot Standby also,
not just vacuum.

AFAIK seq scans already work page at a time for normal tables. So the
issue is when we *aren't* using a seq scan, e.g. nested loops joins.

Is there a way to solve that?

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services