X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 7F844558CF for ; Thu, 17 Mar 2005 23:47:41 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 24589-01 for ; Thu, 17 Mar 2005 23:47:32 +0000 (GMT) Received: from polaris.pinpointresearch.com (66-7-238-130.cust.telepacific.net [66.7.238.130]) by svr1.postgresql.org (Postfix) with ESMTP id 9F87B568AA for ; Thu, 17 Mar 2005 23:47:31 +0000 (GMT) Received: from betelgeuse.pinpointresearch.com (betelgeuse.pinpointresearch.com [192.168.1.179]) by polaris.pinpointresearch.com (Postfix) with ESMTP id B7FD81038E; Thu, 17 Mar 2005 15:47:34 -0800 (PST) From: Steve Crawford Organization: Pinpoint Research To: Oleg Bartunov , Lee Wu Subject: Re: Vacuum full on a big table Date: Thu, 17 Mar 2005 15:47:34 -0800 User-Agent: KMail/1.5.1 Cc: Gaetano Mendola , pgsql-admin@postgresql.org References: In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200503171547.34485.scrawford@pinpointresearch.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.098 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200503/279 X-Sequence-Number: 16777 > 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