Re: Schema per user?

I've actually done this before. I had a web app with about 400 users
each with their own schema. It actually worked very well, except for
one thing. There got to be so many tables that a pg_dump would fail
because it would run out of file locks. We got around it by creating a
primary table and then using views in each of the schemas to access
that user's data. It also made it easy to do a query against all users
at once in the primary table.


On 08/05/2009, at 5:45 AM, Anders Steinlein 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.
>
> Regads,
> Anders
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general