The big thing we needed to do was a rolling archive to progressively broader timeframes. As metrics come in, we keep every single datapoint for the first 6 hours. After 6 hours, data gets rolled up into 5-minute archive. Each datapoint in the 5-minute archive then contains avg, min, max, etc for all the points that lived within that 5-minute span.
The archiving carries on through progressively broader windows as time goes on -- a 10-minute archive, 1-hr archive, etc. This progressive aggregation is the only sane approach to storing the massive amount of data we get. And, it reflects the need for higher resolution for recent events -- it's rare you need to see what happened at one exact minute 6 months ago.
It was this progressive archiving that bit us, specifically as DB performance degraded over time with lots of insertions/deletions. Nested set didn't/wouldn't help with aggregation costs and degradation from churn during the archiving process.
Hope this helps -- I'm going to try to do a more technical post on this in the future.
Sure, sometimes I've had cleanup/integrity/whatever tasks that run every few minutes, but the amount of records affected has always been pretty small.
That's an interesting conundrum. See, this is why we're all messing around with Cassandra et al; sometimes, in SQL, the answer is "don't do that", because it'd be too hard to tailor the db's behavior to suit your needs. Although frankly, with a design that deletes and updates a significant percentage of records in the system on a certain schedule, I can see any number of storage solutions having trouble.
That's interesting. I'll be thinking about this at "work" today. ;) I have a bunch of comments, but they're of the half-baked "oh, what about this?" variety.