Should I partition?

[Jacob Steinberger]

I'm looking to have a database that processes 20G worth of CDRs per day, with a revolving window of a month. I was thinking about setting up partitioning based on day, and then compressing each partition after that day passes in order to speed up searching and limit the amount of physical storage is required. Before the day comes back to current, I'd truncate the table.

From what I can find, compressing partitions isn't an option.

I then thought about doing an Archive storage type, but that sounds like it will help with storage space but can (greatly) increase SELECT times due to the lack of indexing.

At this point, I'm wondering if I'm going to have to do something more non-standard in order to insert into separate tables, compress those tables when I'm done inserting, then write a front-end that would select from multiple tables (if needed). Then more junk to handle truncation.

Any suggestions (from a generic level) to manage this kind of task? Can you compress a single partition out of a set (and I'm just not finding the documentation)?

Edit: I should add that the biggest limitation I have right now is drive space (call it lack of funding for a pet project).



Edited 1 time(s). Last edit at 03/19/2013 03:00PM by Jacob Steinberger.