Message-ID: <4F4B9721.6050808@peak6.com>
Date: Mon, 27 Feb 2012 08:45:53 -0600
From: Shaun Thomas <sthomas@peak6.com>
Reply-To: <sthomas@peak6.com>
Organization: Peak6
User-Agent: Mozilla/5.0 (X11; Linux i686;
	rv:10.0.2) Gecko/20120216 Thunderbird/10.0.2
MIME-Version: 1.0
To: "Reuven M. Lerner" <reuven@lerner.co.il>
CC: <pgsql-performance@postgresql.org>
Subject: Re: Very long deletion time on a 200 GB database
References: <4F45FB55.1020600@lerner.co.il>
	<4F47C6C4.6030102@pinpointresearch.com>
	<4F4B39EF.2080004@lerner.co.il>
In-Reply-To: <4F4B39EF.2080004@lerner.co.il>
Content-Type: text/plain; charset="UTF-8"; format=flowed
content-transfer-encoding: 7bit

On 02/27/2012 02:08 AM, Reuven M. Lerner wrote:

> In the end, it was agreed that we could execute the deletes over
> time, deleting items in the background, or in parallel with the
> application's work. After all, if the disk is filling up at the rate
> of 2 GB/day, then so long as we delete 4 GB/day (which is pretty easy
> to do), we should be fine.

Please tell me you understand deleting rows from a PostgreSQL database 
doesn't work like this. :) The MVCC storage system means you'll 
basically just be marking all those deleted rows as reusable, so your 
database will stop growing, but you'll eventually want to purge all the 
accumulated dead rows.

One way to see how many there are is to use the pgstattuple contrib 
module. You can just call it on the table name in question:

SELECT * FROM pgstattuple('my_table');

You may find that after your deletes are done, you'll have a free_pct of 
80+%. In order to get rid of all that, you'll need to either run CLUSTER 
on your table(s) or use the select->truncate->insert method anyway.

-- 
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604
312-444-8534
sthomas@peak6.com

______________________________________________

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email