Re: Schema per user?

On Thu, May 7, 2009 at 2:12 PM, Anders Steinlein <anders(at)steinlein(dot)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(at)steinlein(dot)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.