0

I'm not going to ask the difference between partitioning and archiving as there are countless of information out there on the net discussing them, and I have read the details of partitioning as compared to archiving. My question is more on a specific scenario.

Let's say you have a table with 50 million record and 95% of the time, apps are only interested querying between only 1 million of those records. The 49 million are what you would cal l the "old" records.

Logical answer to solve this is to partition them. Partition A would contain the 49 million stale records and Partition B would contain the 1 million records.

Would it be a better idea just to automatically archive records into an archive table? if a record satisfies the same condition as the 49 million records, then move them in real time (or by batch) to an archive table?

Which do you think would be the best solution here? currently there's no performance issues but we are anticipating the growth, where at some point there's going to be issues in queries.

Thanks!

1
  • "we are anticipating the growth...there's going to be issues in queries" - Just because the data grows doesn't mean you should assume you're going to have performance issues. 1 million out of 50 million records is only 2% of the table. If your total data grows, but the amount of data the app cares about stays the same then that % gets even smaller. If amount of data the app cares about grows proportionally as well, that % stays about the same. Regardless it's a small % which means as long as your table is indexed properly you should not be over the tipping point and should get index seeks.
    – J.D.
    Aug 10, 2022 at 13:31

1 Answer 1

2

currently there's no performance issues but we are anticipating the growth, where at some point there's going to be issues in queries.

Partitioning isn't a general query performance feature - if you try to use it as such you will face disappointment. As a for-instance: any query that scans a whole index, or the whole table, will do so just the same on a partitioned table as on a standard one.

Partitioning is an infrastructure/admin performance feature: being able to (depending in you DBMS) swap partitions in/out, backing up & restoring individual partitions, being able to keep archive partitions on slower media to save on hardware costs (or the other way around: making faster media for you active data more affordable as you don't need to provision it for the whole system), and so on.

Would it be a better idea just to automatically archive records into an archive table?

The purist in me days no: just make sure the data is properly indexed and your queries nicely written. Having a separate archive table will complicate queries that do need to consider older data - you'll have to decide which to reference or union the two and hope you still get a decent query plan (for simple queries you will, for others maybe not).

Of course there is the risk of bad queries chewing IO on a large table: you'll have to decide depending on your code/users/etc. if you are happy with that risk.

If you truly don't need the older data in general application use, perhaps go one further and explicitly state that the general application won't access old data - move that sort of reporting function to a separate date warehouse, though of course those is now an extra thing you need to design & develop.

The closest I can come to a straight answer is: only use partitioning if you'll benefit from is core benefits (of which fixing bad query performance is not one), and if you do use partitioning use the built in feature instead of your own manual archive table arrangement. If truly archiving, perhaps do it to a different DB (this last part in particular is opinion, and one I know others have valid disagreements with, YMMV).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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