On Thu, May 7, 2009 at 2:56 PM, Erik Jones <ejones@xxxxxxxxxxxxxx> wrote: > > 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. Yeah, we're already looking at segregating out pg_dumps by schemas / ranges of schemas to keep things manageable. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
