Database Scalability

[Saurav Sarkar <saurav.sarkar1@xxxxxxxxx>]

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