Re: What is the benefit of schemas? - Mailing list pgsql-general

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
>