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?