Re: tool for incrementally shrinking bloated tables

On Tue, Dec 21, 2004 at 07:09:39PM -0500, Paul Tillotson wrote:
> To use this system one would do this:
>
> (1) VACUUM KEEP_EARLY_FREE_PAGES mybloatedtable; -- item (a)
>
> (2) UPDATE mybloatedtable SET foo = foo WHERE ctid > '(nnnnn, 0)';
> --move tuples in end of the table to the front.
>
> (3) SHRINK TABLE mybloatedtable; -- item (b)
>
> Then repeat as many times as necessary to accomplish the desired shrinking.

It would be nice to have one command that would do all 3 (maybe in the
backgound). I'd probably also keep KEEP_EARLY_FREE_PAGES available too,
as it could be useful outside this context. ISTM that 2 and 3 are the
brute-force way to accomplish this and that it could be done much more
elegantly in the backend with some extra code.

> P. S. Possible snags that I have thought of include:
>
> - I don't know for sure that UPDATE will use the free space map (will it
> put the new tuple right back in the same page if there is room?)

It's very likely it'll use the same page, but I don't really know.

> - There is currently no < or > operator for tid's, so WHERE ctid >
> '(nnnn, 0)' doesn't work as mentioned.

AFAIK it should be easy to create < and > operators for tid's, though
there's some hidden gotchas there with wraparound.
--
Jim C. Nasby, Database Consultant decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"