Everything very useful but I'm not able to close the circle yet with all these informations.
Just to have some real numbers, I pick up the original example I made and I try to split my question in subtopics:
We are speaking about multi-tenant web application (Angular on the front end, Spring Boot server side)
DBMS: AWS RDS Aurora db.r4.large (2vcore, 15,25GB RAM and fast disk access ) - that according to this can handle 1000 connections.
Tenants: 100 (customers)
Users: let's say each tenant has 3 working users
Multi tenant approach: DB per tenant on the same RDS instance
Average query time: 30ms
Average queries per second: 100(tenants) x 3(users per tenant) x5(average queries for each user) = 1500
Let's forget about load balancing for the moment.
Defining maximum number of connections to the db to maximize TPS
The first question is: how to define the max number of connections to use?
The first thing maybe is not very clear to me is: how to define the maximum number of connections to the DB said that in the actual scenario I can reach the limit of 1000 connections. But from what I got I should use a much less value to maximize the transactions per second.
So, what is the "right" value to use and the maximum number of transactions for seconds possible with that value?
Pool per tenant or shared pool?
Defined the max number of connections, in this scenario is better having a shared pool or a pool per tenant?
Because the pool size should be low, I guess having a pool per tenant is an hard approach to follow. Just having 2-5 connections per tenant, I would have 200/500 connections and accoring to the video you posted here I would say it's not a good approach.
On the other hand, having a shared pool implies it should be able to manage the load generated from users handling also spikes that occasionally could happen. So, should the shared pool size be adaptive (maybe on the number or tenants)?
So here the question is: is better a pool per tenant or a shared pool? Which is the best approach to size it?
Final considerations to scale up
As final consideration, to scale the application up, I was thinking to have many RDS instances in which I store a subset of tenants. In this way (if the response to the previous question is to use a shared pool) I would have a shared pool for each RDS instance.
Is this a good approach to permit to handle thousands of tenants?
Thanks in advance for your useful hints.