Skip site navigation (1) Skip section navigation (2)

Re: Very long deletion time on a 200 GB database

From: David Kerr <dmk(at)mr-paradox(dot)net>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Very long deletion time on a 200 GB database
Date: 2012-02-28 17:06:08
Message-ID: 4F4D0980.9010104@mr-paradox.net (view raw or flat)
Thread:
Lists: pgsql-performance
On 02/27/2012 12:08 AM, Reuven M. Lerner wrote:
> Hi, everyone. I wanted to thank you again for your help on the huge
> delete problem that I was experiencing.
>
> After a lot of trial and error, we finally came to the conclusion that
> deleting this much data in the time frame that they need, on
> underpowered hardware that is shared with an application, with each test
> iteration taking 5-9 hours to run (but needing to run in 2-3), is just
> not going to happen. We tried many of the options that people helpfully
> suggested here, but none of them gave us the performance that we needed.
>
> (One of the developers kept asking me how it can possibly take so long
> to delete 200 GB, when he can delete files of that size in much less
> time. I had to explain to him that deleting rows from a database, is a
> far more complicated task, and can't really be compared to deleting a
> few files.)
>
> 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. Adding RAM or another disk are simply out of the question, which
> is really a shame for a database of this size.
>

Howdy,

I'm coming a little late to the tread but i didn't see anyone propose 
some tricks I've used in the past to overcome the slow delete problem.

First - if you can drop your FKs, delete, re-create your FKs you'll find 
that you can delete an amazing amount of data very quickly.

second - if you can't do that - you can try function that loops and 
deletes a small amount at a time, this gets around the deleting more 
data then you can fit into memory problem. It's still slow but just not 
as slow.

third - don't delete, instead,
create new_table as select * from old_table where <records are not the 
ones you want to delete>
rename new_table to old_table;
create indexes and constraints
drop old_table;

fourth - I think some folks mentioned this, but just for completeness, 
partition the table and make sure that your partition key is such that 
you can just drop an entire partition.

Hope that helps and wasn't redundant.

Dave

In response to

Responses

pgsql-performance by date

Next:From: Jon NelsonDate: 2012-02-28 19:16:27
Subject: problems with set_config, work_mem, maintenance_work_mem, and sorting
Previous:From: Claudio FreireDate: 2012-02-28 16:42:11
Subject: Re: [PERFORM] Re: [PERFORM] Re: [PERFORM] Re: [PERFO?==?UTF-8?Q?RM] Re: 回复: [PERFORM] PG as in-memory db? How to warm ?==?UTF-8?Q?up and re-populate buffers? How to read in all tuples into m?==?UTF-8?Q?emory?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group