Managing a multi-tenant connection pool with separate schema/DB approach

Question

Say that I have a multi-tenant monolithic application, which uses a separate schema (or DB) approach to isolate the tenants' data. A single running instance of the application is used by multiple tenants to access their data. The diagram below sums up the idea:

So far, so good. Now, I have to scale the application up. To do so, I increase the number of running instances, and the load balancer routes each request to one of the instances. By doing so, each instance keeps a connection pool for each tenant's database, since it can serve requests from any tenant. The result is something like this:

The problem is that it grows up exponentially as the number of tenants increases -- higher number of tenants demands more running instances, both of them demands a higher number of connections which demands an increasing number of resources just to keep track of the connection pool. It gets worse if I have a microservices application.

My question is: Is this approach maintainable? What are the possible alternatives and how can one implement them?

Answer 1

Problem with DB per tenant is that you have to add new connection definition to all your app instances, every db instance has its own lifecycle, needs separate configuration for backup, permissions, monitoring, etc. In this regard the schema based approach is easier to implement and scale. At least in a scope of relational DBs. You can also go with discriminator per tenant, so tablespace is shared among tenants but each entry has a unique tenant id as a discriminator column. Depending on your business, you can also mix and match the strategies, e.g.schema per tenant with dedicated db instance for customers with premium plan...