What is the benefit of schemas?

From: "Berend Tober" <btober(at)computer(dot)org>
To: pgsql-general(at)postgresql(dot)org
Subject: What is the benefit of schemas?
Date: 2003-01-31 14:39:41
Message-ID: 3E3A445D.3073.1106B8@localhost
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Stephan Szabo 2003-01-31 14:53:22 Re: Basic SQL join question
Previous Message Curtis Faith 2003-01-31 14:32:11 Re: One large v. many small