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

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)
>>
>>
>>
>
>
>