Re: Multi tenancy : schema vs databases

you could also use a hybrid approach :
- have a systematic tenant_id field in your tables, allowing for 1 db / 1
schema multi-tenants
- give your application the ability to set the schema path for a tenant,
so it will locate the tenant schema if it has a decidated schema
- maybe go to the extreme to be able to specialize the db per tenant
- ..

this would allow you to easily re-organize your tenants to find the best
compromise depending on their status (small tenants, huge tenant, security
freaks tenants, ..).

if going with schema based tenants, make sure you have administrative tasks
to check the diffs between the schemas because if 1000s schemas diverge it
will be bring technical debt down the line.

On Fri, Sep 30, 2016 at 11:47 AM, Achilleas Mantzios <
achill(at)matrix(dot)gatewaynet(dot)com> wrote:

> Via schemata if the tenants represent sub entities of the same
> organization.
> This gives the top level mgmt the ability to have a consolidated view of
> the whole organization.
>
> On 30/09/2016 12:06, Rakesh Kumar wrote:
>
>>
>> ________________________________________
>> From: Venkata B Nagothi <nag1010(at)gmail(dot)com>
>> Sent: Friday, September 30, 2016 02:48
>> To: Rakesh Kumar
>> Cc: pgsql-general(at)postgresql(dot)org
>> Subject: Re: [GENERAL] Multi tenancy : schema vs databases
>>
>> On Fri, Sep 30, 2016 at 10:16 AM, Rakesh Kumar <
>> rakeshkumar464(at)outlook(dot)com<mailto:rakeshkumar464(at)outlook(dot)com>> wrote:
>>
>>
>> ________________________________________
>> From: Venkata B Nagothi <nag1010(at)gmail(dot)com<mailto:nag1010(at)gmail(dot)com>>
>> Sent: Thursday, September 29, 2016 17:25
>> To: Rakesh Kumar
>> Cc: pgsql-general(at)postgresql(dot)org<mailto:pgsql-general(at)postgresql(dot)org>
>> Subject: Re: [GENERAL] Multi tenancy : schema vs databases
>>
>> On Fri, Sep 30, 2016 at 5:18 AM, Rakesh Kumar <rakeshkumar464(at)outlook(dot)com
>> <mailto:rakeshkumar464(at)outlook(dot)com><mailto:rakeshkumar464(at)outlook(dot)com
>> <mailto:rakeshkumar464(at)outlook(dot)com>>> wrote:
>>
>> Hi
>>
>> I would like to know which technique is better for supporting
>> multi-tenancy=
>> applications, going upto hundreds or even thousands of tenants.
>>
>> 1 - One database with difference schemas (one schema per tenant)
>> or
>> 2 - One database per tenant.
>>
>> Did you mean one database with-in a postgresql cluster ?
>>
>> Yes. Say something like this within a PG cluster
>>
>> db4978
>> db6234
>> ...
>> 100s of such databases.
>>
>> That would make things worst if you are going for one database per
>> tenant. As said by John just now, it would end up in an very complex and
>> bad design contributing to very poor performance and high maintenance
>> overhead.
>> A schema per tenant would be a good idea and its hard to say without
>> knowing the data isolation levels you require for each tenant.
>> ========
>>
>> We require complete data isolation. Absolutely nothing should be shared
>> between two tenants.
>>
>> WHy would multiple dbs be any worse than multiple schemas in performance?
>>
>>
>>
>>
>>
>
> --
> Achilleas Mantzios
> IT DEV Lead
> IT DEPT
> Dynacom Tankers Mgmt
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>