Re: Schema per user?

On May 7, 2009, at 2:06 PM, Scott Marlowe wrote:

> On Thu, May 7, 2009 at 2:12 PM, Anders Steinlein
> <anders@steinlein.no> wrote:
>>
>> On May 7, 2009, at 10:05 PM, Scott Marlowe wrote:
>>
>>> On Thu, May 7, 2009 at 1:45 PM, Anders Steinlein <anders@steinlein.no
>>> >
>>> wrote:
>>>>
>>>> Hi,
>>>>
>>>> I'm pondering a design question for a subscription-based web-app
>>>> we are
>>>> developing. Would it be feasible to create a new schema per user
>>>> account,
>>>> setting the search_path to their own schema during login?
>>>>
>>>> [snip]
>>>
>>> We're looking at something similar here at work, but in the 10k to
>>> 10M
>>> range of schemas.  I'll let you know how our testing goes.
>>>
>>> 1,000 is nothing in terms of schemas.  You should be fine.
>>
>> I'd be *very* interested to hear your experiences once you get some
>> results.
>>
>> Generally though, what made you consider such a solution? Same
>> advantages as
>> I mentioned? One thing I'm a bit usure of how best to solve is
>> where to
>> place the "users" or some such table for authentication and other
>> "shared"
>> info -- simply in the "public" schema, perhaps?
>
> We're looking at a "schema per group" fit for a certain application
> and we have lot of groups (in the 100,000 to 1,000,000 range.)  We're
> also looking at partitioning to multiple db servers if needs be.  It's
> a compelling app, and schemas allow us to have one copy of the master
> user data etc and the app just has to have a different search path and
> viola, we're integrated.

Actually, that does sound really interesting.  I could see using pl/
proxy to handle transparently accessing schemas regardless of what
actual db their on -- and I do think that once you get up to those #s
you're talking you're going to need to partition across multiple boxes.

Erik Jones, Database Administrator
Engine Yard
Support, Scalability, Reliability
866.518.9273 x 260
Location: US/Pacific
IRC: mage2k