Re: Proposal for background vacuum full/cluster

Jim C. Nasby wrote:

>On Wed, Apr 20, 2005 at 08:10:23PM -0400, Paul Tillotson wrote:
>
>
>>P. S.
>>
>>The last time I thought about it, I decided that the best solution is
>>probably one that works just like vacuum full except that it scans the
>>table in reverse order. It would do something like this:
>>
>>- Wait for exclusive lock.
>>
>>
>That's exactly what I want to avoid. The reality of cluster and vacuum
>full is that many (if not most) installs can't use them because of how
>they disrupt the system. I'd like a version that doesn't do that.
>
>
>
The version I outlined releases its exclusive lock every time it
successfully moves all the tuples out of a page. This means that it
will only hold one long enough to find free space for the tuples in the
page that it is currently trying to clear, which should not take long if
the table is bloated.

After that, it releases it, and then every transaction waiting for that
lock gets to go again before it takes an exclusive lock. On a lightly
loaded system, this should be unnoticeable.

The use-case which I was targeting is when you are trying to shrink a
table that is being used for a web application--a wait of 1 second is
ok, but wait of 5 minutes isn't.

>>- Start at the end of the table -- call this page I.
>>- If page I is completely empty, shrink the heap and go to step 1 again.
>> (Page I is not empty now.)
>>- *Scan forward in the table until you find a page that is empty. (Call
>>it J)
>> If no such page is found, there is no more free space in the
>>table. Exit.
>>- Move the tuples from page I to page J.
>>- Drop the exclusive lock. and go to step one.
>>
>>
>
>Same basic idea. I haven't gone into specific details because I want to
>see how feasable it is. And since I can't code it myself the best I can
>hope for is a TODO; and IMO I shouldn't try and tell whoever takes that
>TODO how exactly to make this work.
>
>
Regards,
Paul Tillotson