Re: Schema per user?

On May 7, 2009, at 1: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? There is
>> no shared
>> data (except where we keep a record of users), as each account is
>> entirely
>> self-contained. I would usually just put username into relevant
>> tables and
>> querying based on this, but I figured separate schemas *might* have
>> a few
>> advantages: Ease scaling by placing users across different
>> tablespaces
>> and/or databases, easier backup/restore of specific user's data and
>> no
>> vacuum when we delete accounts (just drop schema vs delete).
>>
>> Has anyone done something like this, or is it simply A Bad Idea?
>> How many
>> schemas can a database contain, until one hits a (hard or soft)
>> limit? Keep
>> in mind that this is not a "regular" web-app with thousands of
>> users, but
>> more in the range of 500-1000 for the foreseeable future.
>
> 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.

One thing you'll notice a big change in is dump times from pg_dump
when compared to what you'd see from a db with the same size data set
but a smaller schema.

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