Multitenent architecture

Hi Vasu,

On 04. Jun, 2020, at 17:52, Vasu Madhineni <vasumdba1515@gmail.com> wrote:
We are planning a POC on multitenant architecture in Postgres, Could you please help us with steps for multitenant using schema for each application model.

look up the docs for "create database" and "create schema":

https://www.postgresql.org/docs/current/sql-createdatabase.html
https://www.postgresql.org/docs/current/sql-createschema.html

PostgreSQL is by definition, what Oracle calls "multi tenant".

Cheers,
Paul

On Thu, 2020-06-04 at 23:52 +0800, Vasu Madhineni wrote:

We are planning a POC on multitenant architecture in Postgres, Could you please
help us with steps for multitenant using schema for each application model.

For few tenants, you can keep identical tables in several schemas and
set "search_path" to select a tenant.

With many tenants, you are better off with one table that holds the
data for all clients. You can use Row Level Security to have each
tenant see only his or her data, and it might be a good idea to
use list partitioning on the tenant ID.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com

If the data size is more than 6TB, which approach better?

On Fri, Jun 5, 2020 at 2:57 PM Laurenz Albe <laurenz.albe@cybertec.at>
wrote:

On Jun 5, 2020, at 2:54 AM, Vasu Madhineni <vasumdba1515@gmail.com> wrote:


If the data size is more than 6TB, which approach better?

On Fri, Jun 5, 2020 at 2:57 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Thu, 2020-06-04 at 23:52 +0800, Vasu Madhineni wrote:

We are planning a POC on multitenant architecture in Postgres, Could you please
help us with steps for multitenant using schema for each application model.

For few tenants, you can keep identical tables in several schemas and
set "search_path" to select a tenant.

With many tenants, you are better off with one table that holds the
data for all clients. You can use Row Level Security to have each
tenant see only his or her data, and it might be a good idea to
use list partitioning on the tenant ID.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com

The question is How many separate data owners?

If the data size is more than 6TB, which approach better?

Do you require cross tenants queries? If yes, then schemas are a better solution.

Hi Rob,

Our environment is medical clinical data, so each clinic as a tenant.
Approximately 500+ tenants with 6TB data.

Thank you in advance.

Regards,
Vasu Madhineni

On Fri, Jun 5, 2020 at 6:09 PM Rob Sargent <robjsargent@gmail.com> wrote:

On Jun 5, 2020, at 9:57 PM, Vasu Madhineni <vasumdba1515@gmail.com> wrote:


Hi Rob,

Our environment is medical clinical data, so each clinic as a tenant. Approximately 500+ tenants with 6TB data.

.

How quickly are both those numbers growing? What are your cross clinic query requirements ?
Any single table dominating storage size?

On Sat, Jun 6, 2020 at 3:14 AM Vasu Madhineni <vasumdba1515@gmail.com>
wrote:

Hi Rob,

Our environment is medical clinical data, so each clinic as a tenant.
Approximately 500+ tenants with 6TB data.

Thank you in advance.

There's a good article on the AWS blog on multi tenancy with postgres:

https://aws.amazon.com/blogs/database/multi-tenant-data-isolation-with-postgresql-row-level-security/

This is similar to Laurenz's second suggestion.

-Michel

Hi guys - apologies for jumping into the middle of this, but it touches on
precisely one of the topics I'll have to deal with soon. We're standing up
a pharmacovigilance / drug safety platform managing ICSRs (HL7 "individual
case safety reports" that must be FDA/ICH compliant) and will be dealing
with multi-level privacy and compliance (HIPAA, GDPR, PHI, CCPA, etc.) at
levels such as Patient, Doctor, Pharmacist. This first part deals with
adverse drug events in the "post marketing" realm, i.e., drugs already
approved and in the marketplace. Our model will also expand to support
pre-marketing, i.e., clinical trials and the like.

Far easier to have one coherent data model and tackle compliance as a
multi-tenant / multi-level-secure database issue. We just have to make sure
we do this so it's rock solid and also facilitates the business / use cases
that need to be executed.

I'd love to compare notes with anyone else doing something along those
lines. @Vasu - it sounds like you're smack in the middle of this. Let me
know if you'd be open to connecting and comparing notes on this topic.
(anyone else as well!)

Thanks,

- Jon

On Sat, Jun 6, 2020 at 1:21 PM Michel Pelletier <pelletier.michel@gmail.com>
wrote:

Hi All,

Thanks a lot for information, I will look into it and get back to you.

Regards,
Vasu Madhineni

On Sun, Jun 7, 2020 at 1:21 AM Michel Pelletier <pelletier.michel@gmail.com>
wrote:

On Sat, 2020-06-06 at 11:56 +0800, Vasu Madhineni wrote:

On Fri, Jun 5, 2020 at 2:57 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:

On Thu, 2020-06-04 at 23:52 +0800, Vasu Madhineni wrote:

We are planning a POC on multitenant architecture in Postgres, Could you please
help us with steps for multitenant using schema for each application model.

For few tenants, you can keep identical tables in several schemas and
set "search_path" to select a tenant.

With many tenants, you are better off with one table that holds the
data for all clients. You can use Row Level Security to have each
tenant see only his or her data, and it might be a good idea to
use list partitioning on the tenant ID.

Our environment is medical clinical data, so each clinic as a tenant.
Approximately 500+ tenants with 6TB data.

The important number to base this decision on would be the number of
tables you'd expect in the database. It shouldn't be too many.

If the database grows large, you may be better off sharding the database
together with partitioning it across schemas.
Several smaller databases are easier to back up and make scaling easier.

Of course that requires your application to be part of the solution:
it needs to know which database to use for which tenant.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com

On 4/6/20 6:52 μ.μ., Vasu Madhineni wrote:

Hi All,

We are planning a POC on multitenant architecture in Postgres, Could you please help us with steps for multitenant using schema for each application model.

Hello,
you may benefit from this blog post :
https://severalnines.com/database-blog/multitenancy-options-postgresql

Thank you so much all.

Regards,
Vasu

--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

Hi All,

Our project uses each database for tenant, But how can we restrict
tenant resources?
Ex: Tenent1 has to use 20% resource and Tenent2 has to use 10% resource,
how can we restrict users like this.

Thanks and Regards,

Vasu Madhineni

On Mon, Jun 8, 2020 at 2:50 PM Laurenz Albe <laurenz.albe@cybertec.at>
wrote:

On Tue, Jul 21, 2020 at 7:47 AM Vasu Madhineni <vasumdba1515@gmail.com>
wrote:

Hi All,

Our project uses each database for tenant, But how can we restrict
tenant resources?
Ex: Tenent1 has to use 20% resource and Tenent2 has to use 10% resource,
how can we restrict users like this.

See https://wiki.postgresql.org/wiki/Priorities

-Michel