Thread: tool for incrementally shrinking bloated tables

(Originally posted on -hackers, but they were too busy or not interested.)

I am thinking about attempting to code this if it is not too difficult
for a newbie, but I wanted to see if anyone has any input or ideas first.

Goal: on a prduction server, to gradually shrink a table (no matter how
large) back to < 10% free space without "noticeably" interrupting write
access to it.  ("noticeably" = without taking any exclusive locks for
more than a few seconds at a time.)

To accomplish this, tuples need to be moved into free space in the
beginning of the table, and the table must be shrunk using ftruncate().

It seems that I could make these two changes:

(a) Modifying the VACUUM command to take an option that means "pack the
free space map with the pages that occur earliest in
the table rather than the pages with the most free space."

(b) Create a command that will take an exclusive lock, scan a table
backwards until it comes to a tuple that cannot be removed (i.e., a
tuple that is not HEAPTUPLE_DEAD (see scan_heap() in
src/backend/commands/vacuum.c)) or until some preset amount of time has
elapsed, and then ftruncate() the table.

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.

Comments?  Am I missing some obvious way of accomplishing this goal?  Is
anyone working on something like this?

Paul Tillotson



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?)

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

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@decibel.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?"

I like the idea generally, just some comments:

On Tue, Dec 21, 2004 at 07:09:39PM -0500, Paul Tillotson wrote:
> (1) VACUUM KEEP_EARLY_FREE_PAGES mybloatedtable; -- item (a)

This may not be necessary anyway, but could improve performance.

> (2) UPDATE mybloatedtable SET foo = foo WHERE ctid > '(nnnnn, 0)';
> --move tuples in end of the table to the front.

This is a neat way ot acheive the moving safely, but like you said
you'd need to invent the appropriate operator. And make sure it does
get put near the beginning of the table.

You have to make sure the tuple is really dead and you have to make
sure you update the index at the same time, although maybe dead tuples
are already excluded from the index.

> (3) SHRINK TABLE mybloatedtable; -- item (b)

I assume you're going to need a EXCLUSIVE table lock here. You don't
want this happening:

Process 1: Look at table to find last active tuple
Process 2: Insert tuple at end of table
Process 1: ftruncate

Whoops, you just blatted the new tuple.

That said, it wouldn't be too hard to write something like:

1. LOCK EXCLUSIVE table (actually allowing reads would be ok)
2. Start scanning from 100KB from end of file and note last used page
3. ftruncate

> Then repeat as many times as necessary to accomplish the desired shrinking.

I'm wondering about the value of all this anyway, the number of places
this would be useful doesn't seem especially large anyway. Essentially
when you've stuffed up the FSM configuration...

Hope this helps,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.