Instead of something like 'shake' (which more or less works, even
though it doesn't use fallocate and friends) I frequently use either
CLUSTER (which is what Greg Smith is suggesting) or a series of ALTER
TABLE ... ALTER COLUMN... which rewrites the table. With PG 9 perhaps
VACUUM FULL is more appropriate. Of course, the advice regarding
using 'shake' (or any other defragmenter) on a "live" postgresql data
directory is excellent - the potential for causing damage if the
database is active during that time is very high.
I agree that unless it makes sure there are no open file handles before
moving the file, there is a high chance of corrupting data, and if it
does check, there is little chance it will do anything useful on a live
DB, since it will skip every open file.
Does vacuum full rewrite the whole table, or only the blocks with free
space? If it only rewrites the blocks with free space, the only
solution may be exclusive table lock, alter table to new name, create
old table name as select * from new table name. I also like the cluster
idea, but I am not sure if it rewrites everything, or just the blocks
that have out of order rows, in which case, it would not work well the
second time.
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
