partitioning dilema

[tomaz bracic]

Hi,

we are developing a solution to store large amounts of data for statistic purposes.
Data should be kept for approximately 45 days (let say 6 weeks). We assume that there will be around 20.000.000 inserts per day. So if I rounded we are talking about 1 billion records.

Our first idea was to use partitioning (by RANGE) where range would be defined as one week
This would be somehow implemented dynamically with Java.

There are still questions like how to index everything, how to speedup the process of dropping old data and preparing space (partition) for new.

One of suggested solutions would be to cycle with partitions in a way that one partition holds data for one week. At the end of weekend we would drop the oldest and created the new one. So we would have 6-7 partitions (which we would cycle)

We would really appreciate any suggestions. Is this the right way.

Kind regards,
Tomaz