create a connection pool for many DBs on the same DB server (Spring Boot)

Question

I'm looking for a way to create a connection pool for many DBs on the same DB server (PostgreSQL Aurora). This means that I need the ability of changing the target DB of a connection at run time. Currently I'm using HikariCP for connection pooling, in a stack of Spring Boot and JHispter.

Background:

• we need to deploy a multi-tenancy micro-service system with a single DB server (to be specific, a single AWS Aurora PostgreSQL instance)
• our solution of multi-tenancy is that each tenant has a DB, in that DB we have many schema for each service. All the DBs are in the same AWS Aurora instance.

Our problem:

• with this deployment, we have a connection pool for each (tenant x micro-service instance). This leads to a huge number of connections.
  • Ie: with the pool size of 50 connections/pool. We need: 500 tenants x 20 micro-service instances x 50 connections/pool = 500000 connections.
• The maximum connections allowed on any Aurora DB is 16000, and actually by default the "max_connections" parameter is typically set to something lower.

So now I'm looking for a way to make our pooling scope larger, so that many tenants can share the same pool. Since we use only 1 Aurora server instance, I think it's possible to create a connection pool that can be shared between many tenants. Is there any way to have a connection pool that can switch the DB at run time?

Peroxide

What data-access technology are you using? Plain JDBC, JPA with Hibernate, .... – Cornejo

We use JPA with Hibernate. @M.Deinum – Peroxide

Hibernate has out-of-the-box multi tenancy support, which can do things on connection checkout (like change db, etc.). See docs.jboss.org/hibernate/orm/5.4/userguide/html_single/… on how to configure. – Cornejo

@M.Deinum thanks for you link. But our situation is a bit different from the approaches written there. In specific, we are very close to the "Separate database" approach mentioned there. But all of our DBs is on the same DB server, and we still need "a shared pool between tenants" because of the max connections limitation. According to the article, in their "separate database" approach they create a pool for every tenant, that's the part that doesn't match my case. – Peroxide

You need the schema approach afaik. – Cornejo

@M.Deinum Thanks for your advice. It's really pain to change the solution since we are in the mass development phase already... – Peroxide

And why would you need to change? It is configuration, so I don't see what you would need to change, apart from configuration (and maybe a way to store the tenant information in a thread local)? Everything else is (or can be made) transparent. – Cornejo

@M.Deinum The limitation of max connections doesn't meet the number of our tenants. But on the other hand, the structure of tenants-services (DBs-schema) is a must to fulfill our business requirements. I'm thinking about this: instead of a single Aurora server, we will divide into multiple servers. But that would increase the cost unnecessarily (while we actually doesn't need that much of processing power). – Peroxide

Answer 1

Unless Aurora has done some customization on this, you cannot change the database of a connection once it is established in PostgreSQL. You can still use a pooler, but it will effectively be a separate pool for each database. This is pretty fundamental, there is nothing you can do about it.

Conceivable