Message-ID: <4D9209BF.4010104@pinpointresearch.com>
Date: Tue, 29 Mar 2011 09:33:03 -0700
From: Steve Crawford <scrawford@pinpointresearch.com>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.2.14) Gecko/20110223 Lightning/1.0b2 Thunderbird/3.1.8
MIME-Version: 1.0
To: "Plugge, Joe R." <JRPlugge@west.com>
CC: Nic Chidu <nic@chidu.net>,
	"pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Subject: Re: full vacuum of a very large table
References: <1301414165.2351.1435136873@webmail.messagingengine.com>
	<BD69807DAE0CE44CA00A8338D0FDD08302F6C1D9CE@oma00cexmbx03.corp.westworlds.com>
In-Reply-To: 
 <BD69807DAE0CE44CA00A8338D0FDD08302F6C1D9CE@oma00cexmbx03.corp.westworlds.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 03/29/2011 09:04 AM, Plugge, Joe R. wrote:
> Personally, provided you have the room, I would build a new table off to the side and then migrate what you need to keep to the new table, when done, and satisfied that you have all of the candidate rows, ranem the original to table to "x_tablename" and rename the newly created table into place to take over.... if all is good .. simply drop the x_tablename table.
This looks attractive but can cause issues if there are views, 
foreign-keys, etc. that depend on this table.

> -----Original Message-----
> From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Nic Chidu
> Sent: Tuesday, March 29, 2011 10:56 AM
> To: pgsql-admin@postgresql.org
> Subject: [ADMIN] full vacuum of a very large table
>
> Got a situation where a 130 mil rows (137GB) table needs to be brought down in size to  10 mil records (most recent) with the least amount of downtime.
>
> Doing a full vacuum would be faster on:
>   - 120 mil rows deleted and 10 mil active (delete most of them then full vacuum)
>   - 10 mil deleted and 120 mil active. (delete small batches and full vacuum after each delete).
>
> Any other suggestions?
>
The phrase "most recent" leads me to believe this is time-based data. 
This might be a good time to partition your table to avoid this issue in 
the future. If you are clever about it (and try it out on a test 
environment), you might be able to create inherited tables off your main 
table and then move the "live" data from the parent to the child in 
unobtrusive sized chunks. When no live data remains in the parent table, 
truncate the parent table (only).

Moving forward, set up your application/processes to put the data in to 
the appropriately sized (day?, week?, month? year?) child table. When 
the time comes, just archive and drop the child.

Cheers,
Steve