[postgis-users] Basic question #3 schemas

[Kevin Neufeld]

It's highly recommended to use schemas.  No, there aren't any concerns 
with speed or indexing that I'm aware of. 

I have about 50 schemas in a large 150GB database at the moment.  Each 
schema is like a subdirectory of tables.  Once you're in the habit to 
schema-qualify your tables or get familiar with using search_paths, it's 
exactly like using the single public schema ... except now your data is 
organized.

Personally, I take it a step further.  I never store anything in the 
public schema - I reserve that for PostGIS and PostgreSQL modules like 
cube or tsearch.  It makes upgrading crazy easy because I can pg_dump 
everything EXCEPT the public schema into a new PostGIS install.  Besides 
having hundreds of tables, I also have many custom plpgsql functions.  
If I stored them in public, they would get mixed up with the 600+ 
PostGIS functions.  I would need to sort them all out when trying to 
pg_dump everything except PostGIS ... now that's would be a pain.

Cheers,
Kevin

Ben Madin wrote:
> And finally,
>
> following on from my previous questions, I have a database at the 
> moment with relatively static gis data and a large amount of content 
> being added regularly. I'm learning about how Schema's work, and it 
> would seem to me that I might see some management benefits from 
> putting my GIS data into a schema on it's own, so I can pg_dump only 
> my general work schema regularly, and the gis schema only after I've 
> made changes (probably only every few months). This would help to 
> avoid the current risk of not backing up tables because I added them 
> to the database, but not to the -t element of pg_dump. It would also 
> help because we backup nightly, and then transfer the database 
> (currently around 54MB) across the internet to an offsite server. I'm 
> guessing with out the GIS component, we'd be talking about < 2MB.
>
> Given the size of the GIS component, this would be attractive... but 
> are there implications for speed? Indexing? any good reasons not to do 
> this?
>
> cheers
>
> Ben
>
>