Re: How to best archetect Multi-Tenant SaaS application using Postgres

On Sun, Jul 31, 2016 at 12:07 PM, Silk Parrot <silkparrot(at)gmail(dot)com> wrote:

> Hi,
>
> 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.

Regards,
Venkata B N

Fujitsu Australia