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)