Re: autovacuum truncate exclusive lock round two

Jan,

* Jan Wieck (JanWieck(at)Yahoo(dot)com) wrote:
> The problem case this patch is dealing with is rolling window tables
> that experienced some bloat. The typical example is a log table,
> that has new data constantly added and the oldest data constantly
> purged out. This data normally rotates through some blocks like a
> rolling window. If for some reason (purging turned off for example)
> this table bloats by several GB and later shrinks back to its normal
> content, soon all the used blocks are at the beginning of the heap
> and we find tens of thousands of empty pages at the end. Only now
> does the second scan take more than 1000ms and autovacuum is at risk
> to get killed while at it.

My concern is that this could certainly also happen to a heavily updated
table in an OLTP type of environment where the requirement to take a
heavy lock to clean it up might prevent it from ever happening.. I was
simply hoping we could find a mechanism to lock just those pages we're
getting ready to nuke rather than the entire relation. Perhaps we can
consider how to make those changes alongside of changes to eliminate or
reduce the extent locking that has been painful (for me at least) when
doing massive parallel loads into a table.

> Since we have experienced this problem several times now on our
> production systems, something clearly needs to be done. But IMHO it
> doesn't happen often enough to take any risk here.

I'm not advocating a 'do-nothing' approach, was just looking for another
option that might allow for this work to happen on the heap in parallel
with regular access. Since we havn't got any way to do that currently,
+1 for moving forward with this as it clearly improves the current
situation.

Thanks,

Stephen