Hi All,
We are using Amazon RDS PostgreSQL.
We have some multi-tenant solutions which are separating the tenant data in Postgresql mainly in the following manner.
1. Using different schemas
2. Using different tables for different tenants.
I was just wondering what would be the best way to scale in case no. of schemas or no. of tables limit increase and probably cross the limits of PostgreSQL.
I understand that no. of schemas are unlimited in DB as per
https://www.postgresql.org/message-id/24478.1109618520%40sss.pgh.pa.us
and some limits on tables are mentioned here
https://www.postgresql.org/docs/12/limits.html
In case of table separated tenancy some tables might have more data than other tables. In the case of the table itself growing in size I can look for partitioning the data.
In the case of limits being reached in terms of tables and schemas (if any ) I understand that database sharding or horizontal scaling over multiple database instances could be one solution. It will also help in having more database connections/resources for an ever increasing number of tenants . Obviously with the headache of defining a balanced sharding strategy and maintaining it.
Thanks and Best Regards,
Saurav