I have a monolith application backend which serves millions of request a day written in NodeJs, with Sequelize and postgres as the database. Since ours is a tenant based application I am planning to shard my database in a way that I have x thousands of tenants in one shard and x in another shards, etc. I use AWS RDS (postgressql) as the Database Server.
On the infra structure its pretty much straight-forward to create a new shard. Just creating a new RDS database server with same configurations as my primary database would be sufficient.
The main problem I am facing now is how to manage the shards.
For example: I have the following requirement -
- All my queries of tenant_id < 10000 should go to meta_database
- All my queries of tenant_id > 10000 and < 30000 should go to shard_1
- All my queries of tenant_id > 30000 and < 60000 should go too shard_2
I tried with the following tools:
Sequelize - Its seems like it's highly impossible in doing this with Sequelize since it does not support sharding still. I can have multiple Sequelize connections created for all the shards and do the mapping of tenant_id with a particular shard manually in code. But it requires to get the models each time by passing the tenant_id of the tenant, which is not a good and readable approach.
pg_bouncer_rr - I tried with pg_bouncer-rr and droppped it since I found that having a logic in the query routing level to get the tenant_id from the query and check the value using regex is not a good approach and also can cause some unexpected errors too.
Pg_fdw - Foreign Data Wrapper I was able to create a fdw server and was able to route my queries to the foriegn server by following few articles. But the problem is it's still inserting all the records to my primary meta database tables. It seems like I was able to route only the reading through data wrappers and the data will still reside on the co-ordinator database. Also on addition to that I can partition my table and have few partitions on the foreign servers, but still when I have a record is to be inserted it is getting written to the main database table and then its getting reflected in my foreign tables. How can i have my foreign server to handle all my write and read calls completely independent of the meta database (meta database should only do the routing and should not have any data persisted).
pl/proxy - I read few articles on pl/proxy it requires me to write a function for every read and inserts. I guess its more useful for managing table partitions, than managing shards.
I am not sure how to proceed with the tenant based sharding. If anyone have achieved sharding with nodejs, postgres and sequelize, kindly help!
I am even okay in having a proxy to the database that will take care of the query routing based on tenant_id. I tried CITUS for this purpose to use as a proxy but it revoked its support for AWS recently.