Best Practice for Handling Table with Large Amount of Transaction Data 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?
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.