Re: Multi-tenancy in Postgres

Thank you so far for your perspectives on this.

I especially agree some of the things raised by Radoslaw and Rob.

While it may not be common to come across a scenario where this type of
approach fits, I would like to point out that this type of solution is built
into some commercial DBMS solutions already (SQL
Azure<http://blogs.msdn.com/b/cbiyikoglu/archive/2011/03/23/moving-to-multi-tenant-model-made-easy-with-sql-azure-federations.aspx>and
Progress <http://web.progress.com/en/openedge/openedge.html>). We also see
application-level solutions for this today (Tungsten's replicator and Grails'
Multi-Tenant<http://multi-tenant.github.com/grails-multi-tenant-core/guide/1.%20Overview.html>plugin
and many others). I do think some of what's being done at the
application layer today should be handled by the DB - as Rob says, any
leg-up would be appreciated). If it was only for the likes of SalesForce
then there wouldn't be in-db and application-level solutions being created
to solve this.

I made the original post because I too am searching for ways to achieve this
with more assistance from the DB (currently looking at Postgres SCHEMAs for
per-customer separation and table inheritance to achieve something akin to a
'shared table' that can be extended to suit each customers customisations).
Sure, I could have one database per-customer but that creates overhead
(which the paper goes into about). Improving the overhead per-instance
would help address some of this and is very welcome but even then, if I have
one application and thousands of databases in a cluster then I'd have to
alter each schema when I update the application - and deal with the error
conditions that could arise. Hosting companies may want better isolation
for each customer, but SaaS applications operate on a different level where
they control the database and the queries that execute on them so the same
level of isolation may not be needed.

Also, if we look to the NoSQL world, one of the often-touted benefits there
is for schema-less systems. These aren't always chosen solely because
people don't need a schema-less system, but because they need to, as an
example, store different types of data per-customer. The paper in my
original mail goes some way to exploring ways to provide a solution whilst
retaining the benefits that a RDBMS provides. It may not be 'the answer'
but I certainly feel it warrants some interest.

Thank you,

Emrul

On Wed, Jun 29, 2011 at 9:37 AM, Radosław Smogura
<rsmogura(at)softperience(dot)eu>wrote:

> On Tue, 28 Jun 2011 17:04:54 -0600, Rob Sargent wrote:
>
>> On 06/28/2011 04:52 PM, Greg Smith wrote:
>>
>>> On 06/28/2011 05:45 PM, Rob Sargent wrote:
>>>
>>>> I think Greg might be forgetting that some of us don't always get to
>>>> choose what we work on. I was in a shop that decided to go with
>>>> multi-tenancy for reason both technical and um, er envious.
>>>>
>>>
>>> There are certainly successful deployments of multi-tenant PostgreSQL
>>> out there, ones that make sense. What I was trying to communicate is
>>> that the particular variation proposed by this academic paper doesn't
>>> seem the right direction for PostgreSQL development to head in to me.
>>> This project is stubborn about resolving the problems people actually
>>> have, and the ones the paper tries to solve are not the ones I've seen
>>> in my own experiments in multi-tenant deployments.
>>>
>>> Yes, your point is well taken here, and that wasn't even hinted at in my
>> previous (top! oops) post. My point was that hacks in the field (i.e.
>> me) will have to do multi-tenancy on postgres and though this
>> implementation may not become the answer, any leg up would be appreciated.
>>
>
> I think this may be quite interesting solution. Actually I created such
> approach, for many reasons, but it's hard-coded, I mean in any place when
> query is executed I add this "tenancy" id, I called it differently, and it
> works perfectly.
>
> But such feature will not grow quite fast until PostgreSQL "ecosystem" will
> not grow, for example I see problems with Java + Hibernate + Caching, when
> "tenancy" id will be hidden, actually You may query for two "different"
> objects with same id, if we will allow dynamic tanacy switch (should be
> done, as You will loose connection pool benefits).
>
> Regards,
> Radek
>
>
> --
> 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<http://www.postgresql.org/mailpref/pgsql-general>
>