5

I have a table with 1M+ records. New records are created and updated each day each time an event occurs for each type of event (which there are many). I frequently need to find sums across many records and the time to perform these queries has progressively gotten slower, even though there are multiple indexes in places. Since there are now a couple years of data being stored, I am considering migrating records that are older than 6 months to a separate "archive" table and creating new records for each event type that includes the monthly aggregates (meaning the sum of the rows in the 31 records stored for January 2014 would be stored in 1 record). This will hopefully improve search speed, but is there a better strategy? Is this type of archiving common?

  • it's better to use partitioning, e.g. one table per month. Too many deletes and updates will not reduce the on-disk size of your db. But if that is an issue depends on the specifics: writes/selects/updates/sec, row size, etc. You don't mention any specifics. – sivann Jul 5 '15 at 8:33
1

it's better to use partitioning, e.g. one table per month. You can then truncate archive tables which is an instant operation which frees disk space, or move them to a tablespace on a cheaper device and/or replace them with an aggregate row in an aggregates table. The table seen by your app will usually be a view of the union of the monthly tables. You need careful index design though.

You could also calculate a rolling (cumulative) sum in real time with a trigger. On the other hand doing deletes and updates on the same table will not reduce the on-disk size of your db and may impact performance. But if that is an issue depends on the specifics: writes/selects/updates/sec, row size, drive hardware, indexing, memory etc. You don't mention any specifics.

| improve this answer | |
  • Thanks @sivann. Another answer (that seems to be deleted now) had this suggestion as well and I am in the process of implementing it. Will add a full answer with the system that I use shortly. – Evan Appleby Jul 5 '15 at 21:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.