I don't know if this would work, but if you just want to restructure your rows, your could do this:


        UPDATE table SET id = id WHERE id BETWEEN 0 AND 20000;
        VACUUM table;
        UPDATE table SET id = id WHERE id BETWEEN 20001 AND 40000;
        VACUUM table;

        wash, rinse, repeat.

The idea is that an update rewrites the rows (in your new format) and that VACUUM (not FULL) is quite fast when you just modified a part of the table, and non-locking.

        Would this work ?


"Iain" <[EMAIL PROTECTED]> writes:
another way to speed up full vacuum?

Hmmm... a full vacuum may help to re-organize the structure of modified
tables, but whether this is significant or not is another matter.

Actually, VACUUM FULL is designed to work nicely for the situation where a table has say 10% wasted space and you want the wasted space all compressed out. When there is a lot of wasted space, so that nearly all the rows have to be moved to complete the compaction operation, VACUUM FULL is not a very good choice. And it simply moves rows around, it doesn't modify the rows internally; so it does nothing at all to reclaim space that would have been freed up by DROP COLUMN operations.

CLUSTER is actually a better bet if you want to repack a table that's
suffered a lot of updates or deletions.  In PG 8.0 you might also
consider one of the rewriting variants of ALTER TABLE.

                        regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

               http://archives.postgresql.org




---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match

Reply via email to