0

I have a transaction table storing large amount of transaction data. Only the most recent data of one month are required to be available for transactional use in real-time. Less recent data may only be used for report generation or offline analysis.

What is the best practice for handling such transaction data?

Should I create a history table with same columns and move data with age over one month from the transaction table to the history table with a batch job? Should I create a history table per year? Or just use a single history table to host all past data?

Or Should I use database partitioning instead of history tables?

Or should I create a data warehouse and move data into it?

6
  • how long do you need to retain the older historical data? In any case, if you are working with time-sliced partitioning data, I can't recommend this article enough. I implemented it in our environment and it works great. kejser.org/table-pattern-rotating-log-ring-buffer Commented Feb 14, 2021 at 16:13
  • 1
    Side note: partitioning rarely leads to performance benefits for the query side (takes a lot of work and is finicky). However, it really helps on the management side, especially like this where the data ages out predictably. Commented Feb 14, 2021 at 16:14
  • What do you consider large amount of data? What kinds of queries will you be running on the most current month of data vs on the older data? What is your database schema? The specific answers to these key questions will help determine what you should be doing to maximize performance.
    – J.D.
    Commented Feb 14, 2021 at 22:51
  • @JonathanFite The current data are required for real-time transaction enquiry by external customers. The older historical data are expected to be kept for a long period as long as storage is sufficient for OLAP or data mining by internal users.
    – bboy
    Commented Feb 15, 2021 at 4:18
  • @J.D. The degree of "large" depends on the actual transaction volume by external customers, which could be a hundred thousand per day. The current data are required for real-time transaction enquiry by external customers. The older historical data are expected to be kept for a long period as long as storage is sufficient for OLAP or data mining by internal users.
    – bboy
    Commented Feb 15, 2021 at 4:23

1 Answer 1

0

You should specify which database system and version you're using as this will affect your actual choices available for how you structure and manage the data.

You mentioned large in your context means 100,000 new records a day, so let's plan for something an order of magnitude bigger, 1 million records a day. In a month that's 30 million new records, in a year that's ~360 million records. While 360 million records is starting to get a little beefy, it's by no means something unmanageable or needing special treatment in most modern database systems.

It's going to depend on the actual queries you're running, the concurrency of your server with writes vs reads, and the acceptable runtime for reporting off the data, but 360 million records in a year shouldn't scare you with modern RDBMS. I've managed tables with 10s of billions of records in them, and reported directly off them usually querying for about 1 million records at a time in under 10 seconds on fairly modest hardware.

If you want to reduce the concurrency of reporting then you can look into archiving the older data (i.e. anything older than the last one month of data) or migrating it into a data warehouse. Depending on the type of querying being done, and the database system you're using, you might have features available like columnstore indexing and filtered indexes that could help you improve reporting directly from the table itself without the need to archive.

Correctly architecting your schema and indexing your data will go the longest of ways in improving your reporting capabilities.

Finally, as Jonathan Fite mentioned, partitioning won't directly improve your querying performance but it can be used to improve your management of the table because you can smartly divide your data into partitions that allow you to manage the less active data while the more active partitions are in use. You'll see improvements in reduced concurrency when you need to do things like archiving your data (through partition swapping) or index maintenance.


If your estimate of 100,000 rows per day is accurate, and you don't anticipate any meaningful growth from that over the next few years in actuality, then you're only looking at 36 million rows a year. I wouldn't even think about partitioning or archiving at that point. Regular indexing on a well architected schema should provide you very quick query runtimes. But I personally like to plan for a magnitude more of data then I actually expect, like above.

1
  • Regarding partitioning, I guess it depends of the RDBMS. One of the main use of partitioning in Oracle DB is to improve querying performance, mainly through partition pruning.
    – JeromeFr
    Commented Feb 16, 2021 at 16:49

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.