We are trying build a multi tenant application and are debating which approach we should take: (also my understanding is based on that pgbouncer connection pool doesn’t work across different user/database pair):
1. For each tenant, we create a dedicated database and a dedicated user. This option gives best isolation. However, connection pooling won’t work pgbouncer.
Not sure what you meant by saying connection pooling (pgBouncer) does not work ? This is the general approach for building a multi-tenant application.
2. We put all tenants in a single database, and just use one user. I learned that pgbackup will be probamatic when there are > 30 schemas in a database, so we probably can’t create dedicate schema for each tenant.
We are more inclined to choose 1, but don't know how many concurrent connections Postgres can handle for OLTP workload in a 32GB memory, SSD box. Also we would like hear from someone with more postgres experience about the best practice for building multi-tenant application. Again, i am not sure what you meant by saying pgbackup will not work where there are more than 30 schemas ?
Which version of PostgreSQL are you using ?
How many concurrent connections can be made, will purely depend on number of CPUs (cores) you have available on the database server.
Well, certainly 1 would be the way to go to build an multi-tenant application, but, it strongly depends on your application specific requirements and how are you distributing the data across databases and how the users are accessing data across the databases.
