Thread: What is the benefit of schemas?
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
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
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 >
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) >> >> >> > > >
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/
> ------- 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/