On Mon, Feb 27, 2012 at 6:59 AM, Reuven M. Lerner
<reuven@lerner.co.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.