SQL Paritioning, and expiring "in the future"....

[Bill Waters]

I want to give my users the ability to set a custom retention period for
quarantined mail.  Some will want it for 2 weeks, some for 4, some for a
year.

But of course, that makes using partitions more difficult, right? Because I
can't just drop the partition for data that's x weeks old.

So I was thinking of something, and I can't figure out if I'm missing
something here.  What if I store each message into a partition that is
based on the week it is to expire.  In other words, the partition isn't
chosen based on today's date, but on the future date of when it should
expire....

Right now we are in ISO week 12.  If I had a customer with a 2 week
retention period, I could store the message in the partition for ISO week
14.  If it was for a customer with a 4 week retention period, I could store
it in the partition for ISO week 16.  Or course, I wrap around at the end
of the year.

And then, instead of expiring past dates, at the end of each week, I expire
that current week.  At the end of week 12, I expire week 12.  That way, I'm
deleting messages when they are supposed to expire.

Anybody see any issues with this?  Too many partitions, perhaps?  Poor
database performance because of messages being written to all sorts of
different partitions all the time?  Any mysql experts want to weigh in?
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.amavis.org/pipermail/amavis-users/attachments/20120322/eff4bc39/attachment.html>