DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=D+pZJ479y//tb8V4hKRcst1C4QO56wB5IXnxXTQUE6gtLjGpR6gTQdETWYz9jU+Oljp/UnYV1S1nr0johQ99eqIM1DlhSyV3bQcoANQJUvTUQWvqv54QgYhK5Ea9sxqx07pUuR1GnTqsqiFpBVTUURXbWIRD/nwYGOvWplMqDGg=;
Message-ID: <967268.12623.qm@web39705.mail.mud.yahoo.com>
Date: Tue, 29 Mar 2011 09:38:20 -0700 (PDT)
From: Bob Lunney <bob_lunney@yahoo.com>
Subject: Re: full vacuum of a very large table
To: pgsql-admin@postgresql.org, Nic Chidu <nic@chidu.net>
In-Reply-To: <1301414165.2351.1435136873@webmail.messagingengine.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

=0A--- On Tue, 3/29/11, Nic Chidu <nic@chidu.net> wrote:=0A=0A> From: Nic C=
hidu <nic@chidu.net>=0A> Subject: [ADMIN] full vacuum of a very large table=
=0A> To: pgsql-admin@postgresql.org=0A> Date: Tuesday, March 29, 2011, 11:5=
6 AM=0A> Got a situation where a 130 mil rows=0A> (137GB) table needs to be=
 brought down in size to=A0 10=0A> mil records (most recent)=0A> with the l=
east amount of downtime. =0A> =0A> Doing a full vacuum would be faster on:=
=0A>  - 120 mil rows deleted and 10 mil active (delete most of=0A> them the=
n full vacuum)=0A>  - 10 mil deleted and 120 mil active. (delete small batc=
hes=0A> and full vacuum after each delete). =0A> =0A> Any other suggestions=
?=0A> =0A> Thanks,=0A> =0A> Nic=0A> =0A> -- =0A=0ANic,=0A=0ASince you know =
the where clause to delete the 120 mil rows why not use the converse of tha=
t to select the 10 mil rows to retain into another table, then drop the ori=
ginal table?  No vacuum required!=0A=0ABe sure to use the "create table as =
select..." syntax to avoid WAL during creation of the new table, and use a =
transaction to drop the original table and rename the new one.  That way us=
ers will be querying the original table right up until the switch over, whe=
n they will start using the new table.  =0A=0AForeign keys and other constr=
aints may complicate things a bit, so check those out first.  Also, don't f=
orget to index and analyze the new table before the switch over.=0A=0AGood =
luck!=0A=0ABob Lunney=0A=0A=0A