AMaViS and MySQL - partition types

The correct answer is pretty short, however it took me some time to evaluate all pros and cons. Using HASH and KEY partition styles you are unable to DROP and re-ADD partitions, at least not in a way making sense for an AMaViS-setup with many GBs (50, 100, even more) of data.

RANGE partitions cannot be created between or in front of existing partitions, LIST partitions are therefore the only remaining option.

Now you're probably wondering how to partition events occurring over time BY LIST?! That's pretty easy - tell your AMaViS to us ISO8601 week numbers as partition tag:

$sql_partition_tag =
  sub { my($msginfo)=@_; iso8601_week($msginfo->rx_time) };

Your partitions could then look as follows:

ALTER TABLE quarantine PARTITION BY LIST (partition_tag) (
  PARTITION p0 VALUES IN (0,1,2,3,4,5),
  PARTITION p1 VALUES IN (6,7,8,9,10,11),
  PARTITION p2 VALUES IN (12,13,14,15,16,17),
  PARTITION p3 VALUES IN (18,19,20,21,22,23),
  PARTITION p4 VALUES IN (24,25,26,27,28,29),
  PARTITION p5 VALUES IN (30,31,32,33,34,35),
  PARTITION p6 VALUES IN (36,37,38,39,40,41),
  PARTITION p7 VALUES IN (42,43,44,45,46,47),
  PARTITION p8 VALUES IN (48,49,50,51,52,53)
);

One partition would carry up to six weeks, you can change their size whenever you want to do so. An alternative found on ML was as follows:

ALTER TABLE quarantine PARTITION BY LIST (partition_tag) (
  PARTITION p0 VALUES IN (1,5,9,13,17,21,25,29,33,37,41,45,49),
  PARTITION p1 VALUES IN (2,6,10,14,18,22,26,30,34,38,42,46,50),
  PARTITION p2 VALUES IN (3,7,11,15,19,23,27,31,35,39,43,47),
  PARTITION p3 VALUES IN (4,8,12,16,20,24,28,32,36,40,44,48,52)
);

Even if this could provide better performance in setups with a dedicated storage per partition, I consider it less flexible and also more dangerous when it comes to garbage collection. It could happen that one day you realize that your disks are filling up as your cleanup cronjob wasn't running for whatever reason for some time. You need to immediately drop a partition and cannot do so, as on each of them is recent data you need to conserve. Also resizing / re-ordering partitions is trickier with such a config.