Thread: why use SCHEMA? any real-world examples?

I am in the middle of a project to convert non-schema databases to a
schema-based system.  The main reason I am doing it is because I need to
do a join on tables between databases, which can only be done with an
contrib module which does not have all the "features" one might want
(such as use of indexes, etc).

For example:

SELECT a.id, b.name FROM schema1.industry_id a, schema_shared.industries
b WHERE a.industry_id = b.industry_id;
SELECT a.id, b.name FROM schema2.industry_id a, schema_shared.industries
b WHERE a.industry_id = b.industry_id;
SELECT a.id, b.name FROM schema3.industry_id a, schema_shared.industries
b WHERE a.industry_id = b.industry_id;
SELECT a.id, b.name FROM schema4.industry_id a, schema_shared.industries
b WHERE a.industry_id = b.industry_id;
.. etc...

Obviously this prevents replicating "schema_shared" into every database
whenever it gets updated...

I am sure there are many other uses - they seem very flexible to me so
far, but that's what I'm using it for...

- Greg


>I just noticed PostgreSQL's schemas for my first time.
>(http://www.postgresql.org/docs/current/static/ddl-schemas.html)
>
>I Googled around, but couldn't find any articles describing WHY or
>WHEN to use schemas in database design.
>
>Since the manual says HOW, could anyone here who has used schemas take
>a minute to describe to a newbie like me why you did?   What benefits
>did they offer you?   Any drawbacks?
>
>Thanks for your time.
>
>- Miles

As other posters have indicated, there's a convenience factor and an advantage to compartmentalizing data. In our case
wedon't care so much about user rights (a very useful aspect in and of itself), but more for performance issues. 

We have some applications that use a lot of detailed data about properties in different counties. We have a central
tablethat stores their spatial attributes and some data about the properties themselves. The table has several million
rowscurrently -- selections based on a bounding box are very fast, but if we try to get a list of all properties on all
streetswith names like "Elm%" in a given county, the select is painfully slow as the index (county / street in this
simplifiedcase) lacks specificity -- any given county yields say a half million rows as candidates by county, with
hundredsof possible street entries, so sequential scans are used. 

Hence, I broke out some of the property data that needed to be searched by county, with each county in its own
schema,andeach schema has the same tables (so the schema called "f10675" has a "name_search" table that has the same
nameas the "f01223" schema, but its own contents. 

The search tables all refer to the original data by a unique identifier that is common between the schema/search tables
andthe main store. The search in these schema based tables is much faster because the specificity of the index is much
greater,yielding only dozens or hundreds of candidates out of hundreds of thousands of rows.  

The extra space taken by redundant data storage is more than compensated for by speed in retrieval.

HTH clarify possibilties,

Greg WIlliamson
DBA
GlobeXplorer LLC

-----Original Message-----
From:    Miles Keaton [mailto:mileskeaton@gmail.com]
Sent:    Wed 11/24/2004 9:12 PM
To:    pgsql-general@postgresql.org
Cc:
Subject:    [GENERAL] why use SCHEMA? any real-world examples?
I just noticed PostgreSQL's schemas for my first time.
(http://www.postgresql.org/docs/current/static/ddl-schemas.html)

I Googled around, but couldn't find any articles describing WHY or
WHEN to use schemas in database design.

Since the manual says HOW, could anyone here who has used schemas take
a minute to describe to a newbie like me why you did?   What benefits
did they offer you?   Any drawbacks?

Thanks for your time.

- Miles

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Hi,

Citing Miles Keaton <mileskeaton@gmail.com>:
> I just noticed PostgreSQL's schemas for my first time.
> (http://www.postgresql.org/docs/current/static/ddl-schemas.html)
>
> I Googled around, but couldn't find any articles describing WHY or
> WHEN to use schemas in database design.

When your data are too similar to be split into two databases but
at the same time too different to fit into common tables, a schema
comes in handy to keep your db tidy.

> Since the manual says HOW, could anyone here who has used schemas take
> a minute to describe to a newbie like me why you did?

We had agricultural experiments running here at our institute. We had
both field experiments outside and pot experiments in the greenhouse.
The data collected was mostly the same for both sets of experiments
(plant nutrient content, growth parameters like shoot length...), but
the number of samples taken per measured parameter was quite different
(e.g. for the pot experiments, nutrient data was available from each
and every plant, whereas we took samples in the field only from a subset
of plants). So both sets of experiments did not fit into one clean
normalized, relational model. On the other hand, it was quite desirable
to have all the data in one db, to be able to run queries across both
datasets at the same time from one connection. Schemas provided a nice
way to keep this all clean and simple.

> What benefits
> did they offer you?

Clean, logical organization of one projects data in one db.

> Any drawbacks?

If you have tables with the same name in several schemas, only
the ones, which are in the first schema in the search path are
shown on \dt from psql. Not a major problem, but keep this in mind
when designing the db.

Regards,
Daniel