X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 2120AD1D12F; Wed, 24 Mar 2004 03:31:49 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 71725-06; Tue, 23 Mar 2004 23:31:51 -0400 (AST) Received: from localhost.localdomain (unknown [166.70.154.50]) by svr1.postgresql.org (Postfix) with ESMTP id D561ED1BABD; Tue, 23 Mar 2004 23:31:46 -0400 (AST) Received: from [127.0.0.1] (strongbad [127.0.0.1]) by localhost.localdomain (8.12.8/8.12.8) with ESMTP id i2O3VnBo026601; Tue, 23 Mar 2004 20:31:49 -0700 In-Reply-To: References: Mime-Version: 1.0 (Apple Message framework v613) Content-Type: text/plain; charset=US-ASCII; format=flowed Message-Id: Content-Transfer-Encoding: 7bit Cc: "'pgsql-admin@postgresql.org'" , "'pgsql-performance@postgresql.org'" From: Adam Ruth Subject: Re: Databases Vs. Schemas Date: Tue, 23 Mar 2004 20:31:46 -0700 To: "Subbiah, Stalin" X-Mailer: Apple Mail (2.613) X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/330 X-Sequence-Number: 12899 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 >> >