On May 7, 2009, at 1:05 PM, Scott Marlowe wrote:
On Thu, May 7, 2009 at 1:45 PM, Anders Steinlein
<anders@xxxxxxxxxxxx> 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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
