From: Steve Crawford <scrawford@pinpointresearch.com>
To: Oleg Bartunov <oleg@sai.msu.su>
To: Lee Wu <Lwu@mxlogic.com>
Cc: Gaetano Mendola <mendola@bigfoot.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Vacuum full on a big table
Date: Thu, 17 Mar 2005 15:47:34 -0800
Message-ID: <200503171547.34485.scrawford@pinpointresearch.com> (raw)
In-Reply-To: <Pine.GSO.4.62.0503180055480.5508@ra.sai.msu.su>
References: <ECAB83AA52BCC043A0E24BBC00001024D2E6A2@mxhq-exch.corp.mxlogic.com>
<Pine.GSO.4.62.0503180055480.5508@ra.sai.msu.su>
> In my case ( I have more than 500,000,000 rows) I had to
> 'select * into new_big_table from big_table'
> it was faster and didn't kill server.
> As a bonus, you could 'CLUSTER' your big table if add
> 'order by somekey';
>
> After that, dont' forget to recreate indices and then you could
> drop big_table and 'alter table new_big_table rename to big_table'.
That's OK under certain circumstances. If you have lots of
dependencies (views, rules, triggers, etc.) it can turn into a royal
pain.
For example if you have big_table_view which refers to big_table you
might expect to be able to:
alter table big_table rename to big_table_old;
create table big_table as select * from big_table_old;
drop table big_table_old;
But you would be wrong. Instead you will find that big_table_view now
refers to big_table_old so you can't drop the old table till you drop
the view. You will then have to recreate the view. Of course there
can be numerous views referring to the table and other views
referring to some of those views and pretty soon the whole thing can
become a terrible mess.
Cheers,
Steve
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: scrawford@pinpointresearch.com, oleg@sai.msu.su, Lwu@mxlogic.com, mendola@bigfoot.com
Subject: Re: Vacuum full on a big table
In-Reply-To: <200503171547.34485.scrawford@pinpointresearch.com>
References: <ECAB83AA52BCC043A0E24BBC00001024D2E6A2@mxhq-exch.corp.mxlogic.com> <Pine.GSO.4.62.0503180055480.5508@ra.sai.msu.su> <200503171547.34485.scrawford@pinpointresearch.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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