On Tue, Sep 6, 2011 at 3:06 PM, MirrorX <mirrorx@xxxxxxxxx> 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...; -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
