Re: Multitenent architecture - Mailing list pgsql-general

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

 

Regards,

Vasu Madhineni

On Fri, Jun 5, 2020 at 6:09 PM Rob Sargent <robjsargent@gmail.com> 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?