New to Schemas - Good for Multi Company in one DB ?

From: "Paul Newman" <pnewman(at)tripoint(dot)co(dot)uk>
To: <pgsql-general(at)postgresql(dot)org>
Subject: New to Schemas - Good for Multi Company in one DB ?
Date: 2005-03-15 08:14:10
Message-ID: D5F7521105A39145BEA6A6F47AEFFA8816D3AE@sbserver.tripoint.local
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

I'm a convert from Firebird so I consider myself a newbie to Postgresql.
We have a requirement to host 400 - 600 companies data inside a single
database for connection pooling and scalability reasons as well as our
business logic requirements. We have therefore been very busy adding a
company id to each relevant table and adjusting all our queries to be
company specific ... such that if a company says "show me all my
clients" we would use a query such as Select * from client where comp_id
= 'CompA'

But, I've just discovered Postgresql Schemas ....

If I were to create a schema for each company and therefore remove the
comp_id from our tables and sql would this work ? Could we have 600
schemas in the db ? Would performance be hindered ? If this is OK what
is the best way to maintain all the db structures ? In other words if I
have an update script do I need to run it against each schema ?

I would realy appreciate someones help with this asap .....

Thanks

Paul Newman (Development Director - Tripoint Ltd)

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Richard Huxton 2005-03-15 08:23:16 Re: script variables
Previous Message Karsten Hilbert 2005-03-15 07:25:05 Re: Multi Time Zone Select