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

Re: Very long deletion time on a 200 GB database

From: Samuel Gendler <sgendler(at)ideasculptor(dot)com>
To: "Reuven M(dot) Lerner" <reuven(at)lerner(dot)co(dot)il>
Cc: sthomas(at)peak6(dot)com, pgsql-performance(at)postgresql(dot)org
Subject: Re: Very long deletion time on a 200 GB database
Date: 2012-02-27 21:13:57
Message-ID: CAEV0TzCfZzZ68EOz9DXP6cekByb=B3JnTY-f=9vKtFTQJa6CUQ@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Mon, Feb 27, 2012 at 6:59 AM, Reuven M. Lerner <reuven(at)lerner(dot)co(dot)il>wrote:

>
> So for now, we'll just try to DELETE faster than we INSERT, and combined
> with autovacuum, I'm hoping that this crisis will be averted.  That said,
> the current state of affairs with these machines is pretty fragile, and I
> think that we might want to head off such problems in the future, rather
> than be surprised by them.
>
>
>
For the record, one very effective long term solution for doing this and
continuing to be able to do this no matter how many rows have accumulated
is to partition the data tables over time so that you can just drop older
partitions.  It does require code changes since relying on a trigger on the
parent table to distribute the inserts to the correct partition is much
slower than simply modifying your code to insert/copy into the correct
partition directly.  But it is well worth doing if you are accumulating
large volumes of data.  You can even leave old partitions around if you
don't need the disk space, since well-constructed queries will simply
ignore their existence, anyway, if you are only ever going back 30 days or
less.  Indexes are on individual partitions, so you needn't worry about
indexes getting too large, either.

In response to

Responses

pgsql-performance by date

Next:From: Scott MarloweDate: 2012-02-27 21:28:33
Subject: Re: Very long deletion time on a 200 GB database
Previous:From: Claudio FreireDate: 2012-02-27 19:01:10
Subject: Re: Very long deletion time on a 200 GB database

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