Postgresql: Max Connections per server / database

postgresql

I'm building a new, multitenant application using microservices and Postgres RDS in AWS. Each tenant will have their own database within the RDS instance (additional RDS instances/servers will be added when needed). There will be three microservices that will need access to each tenant database. One microservice will serve UI activity and the other two will handle background transactions, not bound to the UI.

I'm currently using C3p0 for connection pooling for each microservice attached to each tenant database (using a map to manage the tenant connection pools). When a call is made, the tenant id will be passed as part of the payload, determine which connection pool to use then invoke the sql statement. For reference, each tenant will have 20-2000gb of database data.

My question is around postgress max connections. Is the max connections for each database or the database server? I know Postgres can handle thousands of databases but how do you maintain connections to all of these databases?

Suppose I have 100 tenant databases on one database server. Each microservice has a connection pool set to have 5 connections. That would be 15 connections per tenant, or 1500 connections to the database instance/server. Now, I have to have redundancy on the microservices, so I'll have at least 2 of each microservice running, which would double the total database connections to 3000. Add more microservice instances for scalability and my connection count to the database server again increases. To keep it simple for this conversation, I'd scale all the microservices together as a group when there is additional load, so 3 sets of microservices requires 4500 connections and so on.

Will this even work? Am I thinking about connection pooling and Postgres connections correctly? The plan is to use separate databases per tenant for various reasons, unless that is, there is a better way to deploy that makes backup/restores simple, provides data isolation at the SQL level, etc.

I've read through numerous articles with performance issues with one database and a schema per tenant so I think that option is ruled out. The other alternative would be one database with shared tables for all tenants and using the tenant id as part of the primary key, but given the need to keep data isolated, i'm pulling that off the table for consideration.

Best Answer

max_connections from postgresql.conf is for the entire server, but CONNECTION LIMIT from CREATE|ALTER DATABASE command is for that specific database, so you have your choice.

You might barely get away with 4500 connections, but only if the vast majority of them don't do anything the vast majority of the time. In which, case, why have them? Why does each microservice need a connection pool of 5?

I've read through numerous articles with performance issues with one database and a schema per tenant so I think that option is ruled out.

There are lots of bad articles out there, or ones based on 15 year old experiences. A lot has changed in 15 years. Which articles are they? And did they actually say that a database per tenant was better than a schema per tenant, or just that a schema per tenant was bad with no comparison to the alternative?