On Fri, Aug 21, 2009 at 12:50 AM, stoneg64@xxxxxxxxxx<stoneg64@xxxxxxxxxx> wrote: > Hey all, > > My company is designing a database in which we intend to store data for > several customers. We are trying to decide if, > > A: we want to store all customer data in one set of tables with customer_id > fields separating the data or, > B: storing each customers data in a separate schema. > > I'd like to get some opinions on the pros and cons of these methods > concerning maintainability, scalability, and performance. The schema approach works really well. In cases where you can do this, I greatly prefer it over the build in table partitioning features. Some tips: *) You only have to define functions once. As long as you don't schema qualify tables in function definitions, you can have your pl/sql and pl/pgsql functions 'float' over the schema. Just be prepared to regenerate the plans if you change the search path. but, *) Views must be added for each schema *) Queries joining against multiple companies are a pain. In cases where you must do this, make views in the public schema with lots of 'UNION ALL'. *) Make helper functions in dynamic pl/pgsql so you can do things like apply ddl to multiple schemas and rig a 'grant all in schema' if necessary. *) If you are managing huge numbers of schemas, you can use tablespaces to divide up the database into different filesystems. merlin -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
