Re: random observations while testing with a 1,8B row

Ühel kenal päeval, R, 2006-03-10 kell 12:23, kirjutas Steve Atkins:

> I get bitten by this quite often (customer machines, one giant table,
> purge out a lot of old data).
>
> CLUSTER is great for that, given the headroom, though I've often
> resorted to a dump and restore because I've not had the headroom
> for cluster, and it's a lot less downtime than a full vacuum.
>
> While the right fix there is to redo the application engine side to use
> table partitioning, I keep wondering whether it would be possible
> to move rows near the end of the table to the beginning in one, non-
> locking
> phase (vacuum to populate FSM with free space near beginning of table,
> touch rows starting at end of table, repeat) and then finish off with a
> vacuum full to tidy up the remainder and truncate the files (or a
> simpler
> "lock the table and truncate anything unused at the end").

At some point I had to compress a very busily updated table. I used the
following approach:

1) VACUUM buzytable; (lazy not full)

2) SELECT primary_key_value, ctid FROM buzytable;

3) Extract N last records from there and for each keep repeating

3A) UPDATE buzytable
SET primary_key_value = primary_key_value
WHERE primary_key_value = extracted_value

3B) SELECT ctid FROM buzytable
WHERE primary_key_value = extracted_value

until the tuple is moved to another pages, hopefully nearer to
the beginning of table

repeat from 1) until the page for last row (extracted from ctid) is
smaller than some thresold.

This was the only way I was able to get a table back to small enough
size without service interruption.

--------------
Hannu