Re: dump/restore vs vacuum full vs cluster

On Tue, Sep 6, 2011 at 3:06 PM, MirrorX <mirrorx(at)gmail(dot)com> wrote:
> hello to all
>
> i am stuck in the following situation. i have a table which is 500GB. due to
> some deleted rows the actual size is about 350-400GB and i would like to
> reclaim that disk space since from now on this table will remain at this
> level (350-400GB). the system is in production but the specific table
> doesn't affect a lot of procedures on the server so it would be ok to do
> this maintenance 'online'. this table also has 2 indexes. one for its
> primary key and one for another column, about 100GB each. as i understand my
> options are:
>
> 1) dump the data of the table, truncate the table, restore it (what happens
> to the indexes in this case?)
> 2)vacuum full and then reindex
> 3)cluster (based on the 2nd index)
>
> what would you suggest if the most important factor is to minimize the total
> time for this procedure?and, is there is any other option except the
> mentioned above? thx in advance

If there are no dependencies on this table like views or fks, you can also do:

select * into somenewtable from oldtable order by somefield;
drop oldtable;
alter table somenewtable rename to oldtable;
create index xyz...;