Thread: What is the benefit of schemas?

What is the benefit of schemas?

From
"Berend Tober"
Date:
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


Re: What is the benefit of schemas?

From
Bruce Momjian
Date:
I think your ideas are accurate.  You can put each app/user in a
separate schema.  There is no performance penalty.

---------------------------------------------------------------------------

Berend Tober wrote:
> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: What is the benefit of schemas?

From
Arjen van der Meijden
Date:
Another use for schema's might be some application that has a some
different kinds of users.
Where the highest level of powerusers can access all the data, but the
other groups are only allowed to view a subset of data.
In such a case you'll probably define a certain amount of views and deny
access to the tables themselves.

If there are a lot of tables and a lot of different usergroups it is not
so nice to define hundreds of different views with all different names
to support the different groups.
You could assign a default schema for such usergroups (dunno whether
that is possible in postgres), and thus having a much simpler naming
convention for the views while still having the advantage of datahiding
per usergroup.
And in that case you can make very sure noone will ever see a table in
the "master schema" by simply disallowing all access to the entire
schema for non-administrators.

For webhosts it is also a very nice feature, with the use of schema's
they can provide "many different databases" to a client by simply
allowing him to use schema's and thus allowing him to have shielded
"semi-databases" for different applications (a cms, a forum etc), while
the administration has not became more difficult.

Regards,

Arjen

> [mailto:pgsql-general-owner@postgresql.org] Namens Bruce Momjian
> Onderwerp: Re: [GENERAL] What is the benefit of schemas?
>
>
> I think your ideas are accurate.  You can put each app/user
> in a separate schema.  There is no performance penalty.
>
> --------------------------------------------------------------
> -------------
>
> Berend Tober wrote:
> > 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
> >
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> >     (send "unregister YourEmailAddressHere" to
> majordomo@postgresql.org)
> >
>
> --
>   Bruce Momjian                        |  http://candle.pha.pa.us
>   pgman@candle.pha.pa.us               |  (610) 359-1001
>   +  If your life is a hard drive,     |  13 Roberts Road
>   +  Christ can be your backup.        |  Newtown Square,
> Pennsylvania 19073
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>



Re: What is the benefit of schemas?

From
Medi Montaseri
Date:
Another application of Schemas would be for ISPs (or web hosting) folks
who provide
SQL capabilities as well. Now an ISP can allocate a schema per customer.
And if at one
point, we could set quota per schema (for example table sizes, max
concurrent transactions,
WAL limits, etc) then ISPs would be happier.

While I'm still struggling with the concept myself, I can also think of
schema as the concept of
filesystem directory vs a filesystem all by itslef.

ie while a database is a filesystem, an schema is a directory in a
filesystem. So giving users a schema
to themself is like giving them a directory. and the Schema Paths, you
basically have a hierarchy of
schemas to choose from....

I have been thinking about using schema for upgrading within our
product....have'nt figured out how,
but I can have a notion of current schema vs last schema vs next schema
(a queue and a pointer)

Bruce Momjian wrote:

>I think your ideas are accurate.  You can put each app/user in a
>separate schema.  There is no performance penalty.
>
>---------------------------------------------------------------------------
>
>Berend Tober wrote:
>
>
>>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
>>
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 2: you can get off all lists at once with the unregister command
>>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>>
>>
>>
>
>
>




Re: What is the benefit of schemas?

From
Mikael Carneholm
Date:
Pre-7.3 schemas:

\c appfoo appfoouser
select customerID
from appfootable
where appfootable.barcolumn = 'some value';

\c customers customersuser
select *
from customer
where ID = customerID;

Post-7.3 schemas:

\c companydb companydbuser
select c.*
from customers.customer c, appfoo.appfootable a
where a.barcolumn = 'some value' and a.customerID = c.ID;

'nough said? :)

_____________________________________________________________
Här börjar internet!
Skaffa gratis e-mail och gratis internet på http://www.spray.se

Träffa folk från hela Sverige på ett och samma ställe - http://chat.spray.se/



Re: What is the benefit of schemas?

From
Mikael Carneholm
Date:
> ------- Ursprungligt meddelande -------
>
> Från:     Berend Tober  <btober@seaworthysys.com>
> Datum:    Mon, 3 Feb 2003 16:28:43 -0500 (EST)
>
>
> why would you have your customers in a
>separate database from an application table that had to reference the
>customer ID primary key value (CustomerID) from a differnet database?

It's more the other way around: why would you like to have tables for different applications in the same database? Over
time,you will get more and more applications and more and more tables, and (talking pre 7.3), the only way to get
referentialintegrity was to put all tables in the same database. Post 7.3, you no longer have to put tables in the same
databasein order to re-use data from your previous apps. 

I demonstrated the pre 7.3 "no referential integrity while keeping it well organized" issue in my first reply by doing
twoseparate connections to two different databases, as that's how you had to do it previous to 7.3 if you wanted to
keepthings tidy. 

As a former Oracle developer, I can tell you that schemas are a essential part of reusability (in Oracle, at least),
andI'm very happy that Postgres now provides the same functionality. 

A good design (IMHO) is to have a 1-1 relationship between app-db-schema-dbuser, i.e. appFoo connects as userFoo to
dbFooand uses tables in the schemaFoo schema. New applications gets new users, new databases and new schemas, and are
grantedusage to objects in other databases through the use of schemas. Reusability is the keyword! 

_____________________________________________________________
Här börjar internet!
Skaffa gratis e-mail och gratis internet på http://www.spray.se

Träffa folk från hela Sverige på ett och samma ställe - http://chat.spray.se/