> back and forth the data between an archive table and the live
> table, based on how active the groups are, I can't imagine
> any other way of partitioning it. And that would also mean
> some quite big load given the pretty high dynamics of the groups.
You said the activity comes in bursts per group, so the obvious
partitioning would be per group.
If you have too many groups to have one partition per group you could
try to find some modulo or other rule to spread them into separate
partitions.
Andreas