pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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




view thread (8+ messages)

Message-ID: <200503171547.34485.scrawford@pinpointresearch.com>
Permalink:  ../200503171547.34485.scrawford@pinpointresearch.com/
Also on:    postgresql.org/message-id/200503171547.34485.scrawford@pinpointresearch.com

 · 

reply

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