I am working on a SaaS application using AWS MySQL.
The application is for multiple organizations. We use the same database and tables to store multiple org data. We store
org_id in most of the tables and use this in select, update and delete queries as
where org_id = ? .
client_id phone_number phone_type
The data is growing rapidly and we need to shard the data. We have customers from all over the world.
If I use a different sharding id for different table, then a join might happen across the nodes. For example, for user, sharding can be on
shard_id and for client, sharding can be on
client_id and for project, sharding can be on
project_id. Here, the problem would be joining project and user. The
project_id might be on different nodes and join will happen across nodes.
What is the best approach here? I am thinking of sharding based on
org_id as I store
org_id in most of the tables.
I see two problems here:
- A few child tables don't store
org_idas the parent table is storing. Do I need to store the
org_idin all the tables?
- Some orgs might have more data and load which might lead to a hot spot and more storage on a particular node. Is it possible scale a particular node alone with AWS RDS?
Please suggest the best approach.
Note Each org might have up to 500,000 records in any table with around 10 columns. We could reach more than 5,000 organizations in 6 months. 500,000 multiplied by 5,000 orgs will reach 2.5 billion records.