Re: Multi tenancy : schema vs databases

On Thu, Sep 29, 2016 at 12:18 PM, Rakesh Kumar <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.
>
> The points to be considered are:
>
> 1 - which is more light weight from resources point of view.
>

Multiple databases used to be more resource intensive, but that was mostly
fixed in 9.3. Now I don't believe there is all that much difference while
running (databases are much more heavy to create in the first place).

As others have said, different databases makes connection pooling less
efficient, which could be very important to you, or could be irrelevant.

> 2 - which is easier for backup/restore
>

That depends on how you want to backup restore. If you use log archiving,
it will make no difference. If you use sql dumps, then do you want to make
it easier or harder to backup and restore all clients together, or all of
them separately? Are you restoring because your whole system failed, or
because one client did something wrong and needs just their data rolled
back?

> 3 - Which is better from security p.o.v
>

Any implementation can be done wrong in a way that causes security
problems. Do your clients authenticate directly to the database, or to the
app server?

Cheers,

Jeff