Re: heap vacuum & cleanup locks

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.

regards, tom lane