we have a Ruby on Rails app with a MySQL DB (on AWS RDS). Some of our tables have a large number of records ~100 million and this number is rapidly increasing every month. Most of the records in these tables are inactive and are only accessed occasionally for some reports that users can access.
We are now facing several issues:
- query time on these tables has increased significantly even with index optimisation
- restoring from a backup in case of an emergency would take a very long time due to the large size of the tables
Our requirements are:
- data needs to be persisted indefinitely somewhere
- (fast) read access to data
- no further write access necessary after a certain period has passed since the creation of a record
- low cost
We were therefore planning on doing the following:
- after a record has gone "inactive" (this is largely determined by a period of time that has passed since creation), we would create a record in DynamoDB mirroring the data in our sql db.
- after several days have passed we then delete the sql record from the mysql db
- the record can then still be read through dynamodb
Our question was if this sort of approach is conventional and if there are potentially better ways of achieving what we want. Any input would be much appreciated.