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

Paul Newman · *nix forums beginner Joined: 15 Mar 2005 Posts: 10

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)

Scott Marlowe · *nix forums Guru Joined: 03 Mar 2005 Posts: 554

On Tue, 2005-03-15 at 02:14, Paul Newman wrote:

Google