pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Databases Vs. Schemas
8+ messages / 6 participants
[nested] [flat]

* Databases Vs. Schemas
@ 2004-03-22 21:00 Subbiah, Stalin <SSubbiah@netopia.com>
  0 siblings, 0 replies; 8+ messages in thread

From: Subbiah, Stalin @ 2004-03-22 21:00 UTC (permalink / raw)
  To: pgsql-performance; +Cc: 'pgsql-admin@postgresql.org' <pgsql-admin@postgresql.org>

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






^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Databases Vs. Schemas
@ 2004-03-22 21:30 Subbiah, Stalin <SSubbiah@netopia.com>
  2004-03-22 22:04 ` Re: Databases Vs. Schemas Tom Lane <tgl@sss.pgh.pa.us>
  2004-03-23 05:19 ` Re: [PERFORM] Databases Vs. Schemas Josh Berkus <josh@agliodbs.com>
  2004-03-23 13:54 ` Re: Databases Vs. Schemas Adam Ruth <aruth@intercation.com>
  0 siblings, 3 replies; 8+ messages in thread

From: Subbiah, Stalin @ 2004-03-22 21:30 UTC (permalink / raw)
  To: 'pgsql-admin@postgresql.org' <pgsql-admin@postgresql.org>; +Cc: pgsql-performance

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



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Databases Vs. Schemas
  2004-03-22 21:30 Databases Vs. Schemas Subbiah, Stalin <SSubbiah@netopia.com>
@ 2004-03-22 22:04 ` Tom Lane <tgl@sss.pgh.pa.us>
  2 siblings, 0 replies; 8+ messages in thread

From: Tom Lane @ 2004-03-22 22:04 UTC (permalink / raw)
  To: Subbiah, Stalin <SSubbiah@netopia.com>; +Cc: 'pgsql-admin@postgresql.org' <pgsql-admin@postgresql.org>; pgsql-performance

"Subbiah, Stalin" <SSubbiah@netopia.com> writes:
> Is it better to have 1000 databases vs 1000 schemas in a
> database cluster.

You almost certainly want to go for schemas, at least from a performance
point of view.  The overhead of a schema is small (basically one more
row in pg_namespace) whereas the overhead of a database is not trivial.

The main reason you might not want to use schemas is if you want fairly
airtight separation between different services.  Separate databases
would prevent services from looking at each others' catalog entries.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: [PERFORM] Databases Vs. Schemas
  2004-03-22 21:30 Databases Vs. Schemas Subbiah, Stalin <SSubbiah@netopia.com>
@ 2004-03-23 05:19 ` Josh Berkus <josh@agliodbs.com>
  2004-03-23 11:06   ` Re: Databases Vs. Schemas CoL <col@mportal.hu>
  2 siblings, 1 reply; 8+ messages in thread

From: Josh Berkus @ 2004-03-23 05:19 UTC (permalink / raw)
  To: Subbiah, Stalin <SSubbiah@netopia.com>; 'pgsql-admin@postgresql.org' <pgsql-admin@postgresql.org>; +Cc: pgsql-performance

Stalin,

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

No performance difference AFAIK.   The real question is whether you have to 
have queries joining several "databases".   If yes, use Schema; if no, use 
databases.

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Databases Vs. Schemas
  2004-03-22 21:30 Databases Vs. Schemas Subbiah, Stalin <SSubbiah@netopia.com>
  2004-03-23 05:19 ` Re: [PERFORM] Databases Vs. Schemas Josh Berkus <josh@agliodbs.com>
@ 2004-03-23 11:06   ` CoL <col@mportal.hu>
  0 siblings, 0 replies; 8+ messages in thread

From: CoL @ 2004-03-23 11:06 UTC (permalink / raw)
  To: pgsql-performance

hi

Josh Berkus wrote:

> Stalin,
> 
> 
>>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.
> 
> 
> No performance difference AFAIK.   The real question is whether you have to 
> have queries joining several "databases".   If yes, use Schema; if no, use 
> databases.

don't forget the pg_hba.conf :) You need 1000 declaration. Was a thread 
before, title: performance problem - 10.000 databases
Check this:
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&threadm=1068039213.28814.116.camel%40...

C.



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Databases Vs. Schemas
  2004-03-22 21:30 Databases Vs. Schemas Subbiah, Stalin <SSubbiah@netopia.com>
@ 2004-03-23 13:54 ` Adam Ruth <aruth@intercation.com>
  2 siblings, 0 replies; 8+ messages in thread

From: Adam Ruth @ 2004-03-23 13:54 UTC (permalink / raw)
  To: Subbiah, Stalin <SSubbiah@netopia.com>; +Cc: 'pgsql-admin@postgresql.org' <pgsql-admin@postgresql.org>; pgsql-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@postgresql.org so that your
>       message can get through to the mailing list cleanly
>




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Databases Vs. Schemas
@ 2004-03-23 18:16 Subbiah, Stalin <SSubbiah@netopia.com>
  2004-03-24 03:31 ` Re: Databases Vs. Schemas Adam Ruth <adamruth@mac.com>
  0 siblings, 1 reply; 8+ messages in thread

From: Subbiah, Stalin @ 2004-03-23 18:16 UTC (permalink / raw)
  To: Adam Ruth <aruth@intercation.com>; Subbiah, Stalin <SSubbiah@netopia.com>; +Cc: 'pgsql-admin@postgresql.org' <pgsql-admin@postgresql.org>; pgsql-performance

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

Is it because you had smaller value set for max. allowable number of open
files descriptor. what was ulimit -a set to ?

>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@postgresql.org so that your
>       message can get through to the mailing list cleanly
>



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Databases Vs. Schemas
  2004-03-23 18:16 Re: Databases Vs. Schemas Subbiah, Stalin <SSubbiah@netopia.com>
@ 2004-03-24 03:31 ` Adam Ruth <adamruth@mac.com>
  0 siblings, 0 replies; 8+ messages in thread

From: Adam Ruth @ 2004-03-24 03:31 UTC (permalink / raw)
  To: Subbiah, Stalin <SSubbiah@netopia.com>; +Cc: 'pgsql-admin@postgresql.org' <pgsql-admin@postgresql.org>; pgsql-performance


On Mar 23, 2004, at 11:16 AM, Subbiah, Stalin wrote:

>> 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.
>
> Is it because you had smaller value set for max. allowable number of 
> open
> files descriptor. what was ulimit -a set to ?

It was actually running on OS X and it was a shared memory issue.  We 
would have had to recompile the Darwin kernel to get a bigger SHMMAX, 
but this solution seemed better since we would possibly be installing 
on servers where we wouldn't have that much leeway.  I think that the 
view idea works better for a number of other reasons.  For one, I can 
do a query on the base table and see all of the rows for all of the 
schemas at once, that has proven quite useful.

>
>> 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@postgresql.org so that 
>> your
>>       message can get through to the mailing list cleanly
>>
>




^ permalink  raw  reply  [nested|flat] 8+ messages in thread


end of thread, other threads:[~2004-03-24 03:31 UTC | newest]

Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-03-22 21:00 Databases Vs. Schemas Subbiah, Stalin <SSubbiah@netopia.com>
2004-03-22 21:30 Databases Vs. Schemas Subbiah, Stalin <SSubbiah@netopia.com>
2004-03-22 22:04 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-03-23 05:19 ` Josh Berkus <josh@agliodbs.com>
2004-03-23 11:06   ` CoL <col@mportal.hu>
2004-03-23 13:54 ` Adam Ruth <aruth@intercation.com>
2004-03-23 18:16 Re: Databases Vs. Schemas Subbiah, Stalin <SSubbiah@netopia.com>
2004-03-24 03:31 ` Adam Ruth <adamruth@mac.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox