pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feeddump/restore vs vacuum full vs cluster
2+ messages / 2 participants
[nested] [flat]
* dump/restore vs vacuum full vs cluster
@ 2011-09-06 21:06 MirrorX <mirrorx@gmail.com>
2011-09-07 15:12 ` Re: dump/restore vs vacuum full vs cluster Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: MirrorX @ 2011-09-06 21:06 UTC (permalink / raw)
To: 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.htm...
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: dump/restore vs vacuum full vs cluster
2011-09-06 21:06 dump/restore vs vacuum full vs cluster MirrorX <mirrorx@gmail.com>
@ 2011-09-07 15:12 ` Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Scott Marlowe @ 2011-09-07 15:12 UTC (permalink / raw)
To: MirrorX <mirrorx@gmail.com>; +Cc: pgsql-admin
On Tue, Sep 6, 2011 at 3:06 PM, MirrorX <mirrorx@gmail.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...;
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2011-09-07 15:12 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-09-06 21:06 dump/restore vs vacuum full vs cluster MirrorX <mirrorx@gmail.com>
2011-09-07 15:12 ` Scott Marlowe <scott.marlowe@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox