Message-ID: <519990A0.6030402@2ndQuadrant.com>
Date: Sun, 19 May 2013 22:55:28 -0400
From: Greg Smith <greg@2ndQuadrant.com>
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.7;
	rv:14.0) Gecko/20120713 Thunderbird/14.0
MIME-Version: 1.0
To: Rob Emery <re-pgsql@codeweavers.net>
CC: pgsql-performance@postgresql.org
Subject: Re: Deleting Rows From Large Tables
References: 
 <CAPCETps_ur3KM=8z0fQovKG-eKDVfsp9Xjt-9PEQd5BVMusr4Q@mail.gmail.com>
In-Reply-To: 
 <CAPCETps_ur3KM=8z0fQovKG-eKDVfsp9Xjt-9PEQd5BVMusr4Q@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

On 5/17/13 7:26 AM, Rob Emery wrote:
> I can keep decreasing the size of
> the window I'm deleting but I feel I must be doing something either
> fundamentally wrong or over-complicating this enormously.

I've had jobs like this where we ended up making the batch size cover 
only 4 hours at a time.  Once you've looked at the EXPLAIN plans for the 
row selection criteria and they're reasonable, dropping the period 
that's deleted per pass is really the only thing you can do.  Do some 
DELETEs, then pause to let the disk cache clear; repeat.

The other useful thing to do here is get very aggressive about settings 
for shared_buffers, checkpoint_segments, and checkpoint_timeout.  I'll 
normally push for settings like 8GB/256/15 minutes when doing this sort 
of thing.  The usual situation with a checkpoint every 5 minutes may not 
be feasible when you've got this type of work going on in the background.

-- 
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance