[GENERAL] How to best archetect Multi-Tenant SaaS application using Postgres

[Silk Parrot]

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.

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.


Thanks
Ryan 

[Venkata Balaji N]

Post by Silk Parrot
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
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.

Post by Silk Parrot
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

[Silk Parrot]

Thanks for the response

  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.
According to https://pgbouncer.github.io/config.html, the connection pooling is based on user/database pair. If each user connects under its own username to its own database, then the connection opened can’t be shared with other users. If I want to make a single server host 1000 tenants at the same time, then there will be at least 1000 connections even I use pgBouncer.

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.
Yup, I also would like to go with 1, suppose a server with 16 core, 32GB memory, SSD box, how many connections could it handle roughly, e.g. O(100) or O(1000) or O(5000)?


-- 
Regards
Ryan

On August 1, 2016 at 10:30:48 PM, Venkata Balaji N (***@gmail.com) wrote:


On Sun, Jul 31, 2016 at 12:07 PM, Silk Parrot <***@gmail.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  

[John R Pierce]

Post by Silk Parrot
Yup, I also would like to go with 1, suppose a server with 16 core,
32GB memory, SSD box, how many connections could it handle roughly,
e.g. O(100) or O(1000) or O(5000)?

thats a fairly small server by today's standards, especially the 32GB
ram part.

with 16 cores, more than 16 concurrent CPU bound queries will degrade
each other. idle connections don't use too much memory and no CPU,
but 1000s sounds like a bad idea.

[Venkata Balaji N]

Post by Venkata Balaji N
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.
Yup, I also would like to go with 1, suppose a server with 16 core, 32GB
memory, SSD box, how many connections could it handle roughly, e.g. O(100)
or O(1000) or O(5000)?

Well, as its been said already, you can typically have 16 concurrent
connections at a time and more if the CPUs are hyper-threading enabled. All
that said, how many requests your database can serve will depend on the
fact that, how long each connection is expected to spend on the database.
This means, there must an appropriate benchmarking process in place. If you
really see a connection/query spending few milli-seconds on the database
with some aggressive connection pooling mechanism, then you can get more
number of requests served in less time. Generally, it is possible that,
1000s of requests can be served and those being concurrent will be far from
real with kind of hardware capacity you have. So, the solution would be to
have appropriate tuning and benchmarking process in place.

Regards,
Venkata B N

Fujitsu, Australia

Post by Venkata Balaji N

Post by Silk Parrot
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
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.

Post by Silk Parrot
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