On Fri, Aug 21, 2009 at 12:50 AM,
stoneg64@excite.com<stoneg64@excite.com> 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
