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

As other posters have indicated, there's a convenience factor and an advantage to compartmentalizing data. In our case we don'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 table that stores their spatial attributes and some data about the properties themselves. The table has several million rows currently -- selections based on a bounding box are very fast, but if we try to get a list of all properties on all streets with names like "Elm%" in a given county, the select is painfully slow as the index (county / street in this simplified case) lacks specificity -- any given county yields say a half million rows as candidates by county, with hundreds of 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,and each schema has the same tables (so the schema called "f10675" has a "name_search" table that has the same name as 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 and the 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(at)gmail(dot)com]
Sent: Wed 11/24/2004 9:12 PM
To: pgsql-general(at)postgresql(dot)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