Re: Schema per user?

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.