Postgresql 8.0.4 on FreeBSD 5.4
I have a table consisting of some 300million rows that, every couple of
months, has 100 million rows deleted from it (an immediately vacuumed
afterward). Even though it gets routinely vacuumed (the only
deletions/updates are just the quarterly ones), the freespace map was
not increased in size to keep up with the growing size of the other
tables in the database which do experience many updates,etc.
I suspect that the table is suffering from bloat (not the indexes though
as I drop them prior to the huge delete, then create them anew). What
would be the recommended method for reclaiming the disk space lost due
to bloat? Dropping the 5 indexes on the table and doing a VACUUM FULL,
keeping the indexes and doing a VACUUM FULL (does FULL perform the same
disk moving operations on the indexes as it does on the actual table?),
dropping the indexes except the primary key and CLUSTER ON primary key,
keeping the indexes and doing a CLUSTER ON primary key (again, does
CLUSTER ON just operation on the table proper?)
What are the caveats on using one over the other? I imagine any of the
options I listed above will involve a full table lock. Are there any
differences in the amount of free disk space required for each method?
Thanks,
Sven