Happy new year everyone. I’m hoping for some general guidance in the following situation…
I have an application that has been running for about 10 years. The datastore is in mysql, (now on AWS Aurora).
Some of the tables that are in one-to-many relations are starting to have a larger number of rows:
Records (~1.4million rows) | V (1 to many) | V SubRecords (~10million rows) | V (1 to many) | V SubSubRecords (~22million rows)
There is not a lot of actual data being stored in these rows (ie subSubRecords is about 5gb in total), and the queries I run are very straightforwards, using indexed keys with no joins. For example…
SELECT ... FROM Records WHERE id = ?; SELECT ... FROM SubRecords WHERE recordId = ?; SELECT ... FROM SubSubRecords WHERE subRecordId = ?;
So far, everything continues to be highly performant.
However, I’m starting to worry about how this design will hold up over time. While it took 10 years to get to 22 million rows in SubSubRecords, the db is growing a lot faster now. I wouldn’t be surprised to see that table climb to 100million rows over the next 5 years, which feels like a lot. And I’m not sure at what point it will become a problem.
I realize this is a rather broad question, and is situation-dependent. But what what types of solutions are generally recommended in these cases?
Setup partitions? (The tables use foreign keys to enforce integrity
and my understanding is that this is incompatible with partitions.)
Convert the data in subRecords and subSubRecords to a json payload
and store that directly in a json column in the main records table?
(Same amount of data, but less rows, if it matters.)
Move to an entirely different db? (Mongo? I know nothing about it
but have heard is better at scaling in certain situations.)
Ignore it until it becomes an issue? 😀
Any suggestions / pearls of wisdom from those who have wrestled with
similar problems is welcome. Thanks (in advance) for your help!