How do you handle multiple database connections on an multitenancy applications

Question

In a multitenancy application where you create a new postgres database for each tenant, what's the best way to handle database connections on runtime?

Do you open and close connection on every request or do you leave a connection open once opened, if yes, won't you exceed the number of open connections and how do you clean up your database connections?

• Ewan: googles postgre... wait no built in connection pooling?... wait no use database command?!
• Flater: Just to be clear, this is a single runtime connecting to multiple databases? Not a runtime per tenant (even if on the same machine)?
• Cavdy: Yes @Flater single runtime connecting to multiple tenant database
• Esben Skov Pedersen: The way you describe it, it sounds like you have a single-tenant database(each database can only handle a single tenant)

Answer 1

There is no single best way to handle this. How you manage database connections is highly dependent on application architecture and the infrastructure that supports it.

You do not have much information in your question, but start out with the simplest solution. Offhand it seems like opening and closing the connection on every request is the quickest and easiest to implement. Being that you are still deciding how to handle database connections in a multi-tenant environment, this indicates to me that you are still pretty early on in the design of this system. This is a very broad assumption, though. You have far bigger problems to solve at this point. The overhead of opening and closing a database connection is miniscule compared to the time spent processing queries and handling inserts, updates, and deletes.

Worrying about how best to handle connections at this point is premature optimization. Go for the easiest solution that allows you to tackle the truly hard problems the quickest. Only after measuring a performance problem and profiling your application to pinpoint connection handling as the source of your problem should you consider other options.

• Hans-Martin Mosner: Premature optimization should be avoided in general, but connection pools are a pretty generic building block when doing fine-grained services., and it's probably not a bad idea to include a connection pool if the database framework you use provides them out of the box.
• v1d3rm3: I liked the answer, but i agree with @Hans-MartinMosner