Re: Deleting Rows From Large Tables

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(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com