Skip site navigation (1) Skip section navigation (2)

Re: Databases Vs. Schemas

From: Adam Ruth <aruth(at)intercation(dot)com>
To: "Subbiah, Stalin" <SSubbiah(at)netopia(dot)com>
Cc: "'pgsql-admin(at)postgresql(dot)org'" <pgsql-admin(at)postgresql(dot)org>,"'pgsql-performance(at)postgresql(dot)org'" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Databases Vs. Schemas
Date: 2004-03-23 13:54:02
Message-ID: 8B012B25-7CD1-11D8-85EE-000A959D1424@intercation.com (view raw or flat)
Thread:
Lists: pgsql-adminpgsql-performance
We have a  similarly sized database and we went with schemas.  We did 
something different, though, we created one schema that contained all 
of the tables (we used the public schema) and then created the hundreds 
of schemas with views that access only the related rows for a 
particular schema.  Something like this:

create table public.file (siteid int, id int, [fields]);
create schema sc1;
create view sc1.file as select * from public.file where siteid = 1;
create schema sc2;
create view sc2.file as select * from public file where siteid = 2;

And we also created rules to allow update, delete, and insert on those 
views so that they looked like tables.  The reason we did this is 
because we ran into issues with too many open files during pg_dump when 
we had thousands of tables instead of about 1 hundred tables and 
thousands of views.

We, however, did have a need to periodically select data from 2 schemas 
at a time, and it was simpler logic than if we needed 2 database 
connections.

Adam Ruth

On Mar 22, 2004, at 2:30 PM, Subbiah, Stalin wrote:

> --sorry to repost, just subscribed to the list. hopefully it gets to 
> the
> list this time --
>
> Hi All,
>
> We are evaluating the options for having multiple databases vs. 
> schemas on a
> single database cluster for a custom grown app that we developed. Each 
> app
> installs same set of tables for each service. And the service could 
> easily
> be in thousands. so Is it better to have 1000 databases vs 1000 
> schemas in a
> database cluster. What are the performance overhead of having multiple
> databases vs. schemas (if any). I'm leaning towards having schemas 
> rather
> than databases but i would like to get others opinion on this. 
> Appreciate
> your reply.
>
> Thanks,
> Stalin
>
> ---------------------------(end of 
> broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
>       message can get through to the mailing list cleanly
>


In response to

pgsql-performance by date

Next:From: Phil EndecottDate: 2004-03-23 14:12:01
Subject: Optimisation of INTERSECT expressions
Previous:From: CoLDate: 2004-03-23 11:06:42
Subject: Re: Databases Vs. Schemas

pgsql-admin by date

Next:From: Oleg BartunovDate: 2004-03-23 15:19:52
Subject: Re: Cache loolup failed problems with tsearch following
Previous:From: Mike BellDate: 2004-03-23 13:47:52
Subject: Cache loolup failed problems with tsearch following pg_restore

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group