Re: Schema per user?

From: Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
To: Erik Jones <ejones(at)engineyard(dot)com>
Cc: Anders Steinlein <anders(at)steinlein(dot)no>, pgsql-general(at)postgresql(dot)org
Subject: Re: Schema per user?
Date: 2009-05-07 21:02:58
Message-ID: dcc563d10905071402i6b37b89fw24c1a60923224560@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Scott Marlowe 2009-05-07 21:06:11 Re: Schema per user?
Previous Message Tom Lane 2009-05-07 20:59:15 Re: Schema per user?