I have a table to which millions of data are inserted every day. As the table size grows more than 10 GB, I have issues with the performance of server in fetching apis and generating reports, which are based on above table. What i am planning to do it is move the old, say 1 week, data to another server and keep the current table size always small. I am running a php application to fetch api and generate reports on the server.
My concerns are:
- Is it the right approach or are there other solutions to my problem?
- What is the best way to do it?