Re: Very long deletion time on a 200 GB database

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.