dump/restore vs vacuum full vs cluster

From: MirrorX <mirrorx(at)gmail(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: dump/restore vs vacuum full vs cluster
Date: 2011-09-06 21:06:08
Message-ID: 1315343168569-4776084.post@n5.nabble.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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

--
View this message in context: http://postgresql.1045698.n5.nabble.com/dump-restore-vs-vacuum-full-vs-cluster-tp4776084p4776084.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message marvin.deoliveira 2011-09-06 21:12:32 Re: corrupted rows in pg_attribute deleted
Previous Message Lukasz Brodziak 2011-09-06 20:17:51 Re: corrupted rows in pg_attribute deleted