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
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
