The introduction of schemas in PostgreSQL v 7.3 seems like an
important improvement, since that is a feature many expensive,
proprietory RDMS have, but I'm wondering how I should be using it.
After I installed 7.3 and then brought my database over, I created an
application-specific schema and defined my tables and other database
objects within that name space, rather than the "public" name space.
But, I'm thinking, if that is all I do, then what is the point?
I realize that with schemas, you can allow individual users to create
tables in their own user-accessible schemas, but I'm not sure yet
what the utility of that is.
So my question is, I guess, what would be some typical or
archetypical ways that the ability to use schemas would be a good
thing, for example?
The only thing I've come up with so far as possiblities is something
like having most of an application's domain-specific tables defined
in an application-specific schema, but then maybe define in the
public schema tables such as for locations (city, state/province,
country, postal code, etc.) or generic personal attributes such as
tables defining gender or courtesy titles (i.e., Mr., Mrs., etc.).
Does it make sense to utilize schemas in such a way as to support say
multiple, separate, mostly un-related applications by having a
separate, application-specific schema for the objects specific to
each particular application, and then share items like I suggested
above in the public schema?
My follow-up question then is to ask whether there is a performance
penalty to having additional schemas, i.e., if I am supporting
multiple applications with one database but multiple schemas within
that database, is database server performance going to suffer as the
number of schemas grows?
Regards,
Berend Tober