Re: Schema per user?

On Thu, May 7, 2009 at 2:56 PM, Erik Jones <ejones(at)engineyard(dot)com> wrote:
>
> 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.

Yeah, we're already looking at segregating out pg_dumps by schemas /
ranges of schemas to keep things manageable.