This is part of The Pile, a partial archive of some open source mailing lists and newsgroups.
To: pgsql-general@postgresql.org From: Jason Hihn <jhihn@paytimepayroll.com> Subject: [GENERAL] Many little databases or one big one? Date: Mon, 05 May 2003 16:04:24 -0400 In all likelihood, I will be admin'ing several hundred databases whose schema is identical. Being the lazy admin that I am, I was thinking that it may be better for me to combine everything into one large database and just make views for each former database (after appending a key to each table, and appropriately naming the view). This would be much more manageable, since db objects (procedures, triggers, table schemas) would update for all at once. My developer team is changing things pretty frequently too. To enforce version consistency through all the databases, this would be the best and easiest way to do that. What are the down sides? I know that I can no longer partition the data into separate directories. A table corruption would effect everyone. More data needs to be stored (addt'l keys). Are there other downsides? If there are too many down sides, is there an easier way I can update the db objects (a tool) for each DB all at once? === To: pgsql-general@postgresql.org From: Bruno Wolff III <bruno@wolff.to> Subject: Re: [GENERAL] Many little databases or one big one? Date: Mon, 5 May 2003 20:31:27 -0500 On Mon, May 05, 2003 at 16:04:24 -0400, Jason Hihn <jhihn@paytimepayroll.com> wrote: > > What are the down sides? I know that I can no longer partition the data into > separate directories. A table corruption would effect everyone. More data > needs to be stored (addt'l keys). Are there other downsides? If there are > too many down sides, is there an easier way I can update the db objects (a > tool) for each DB all at once? A script could fairly easily apply a set of identical changes to all of the databases in one postmaster. === To: pgsql-general@postgresql.org From: Ron Johnson <ron.l.johnson@cox.net> Subject: Re: [GENERAL] Many little databases or one big one? Date: 06 May 2003 06:30:02 -0500 On Mon, 2003-05-05 at 15:04, Jason Hihn wrote: > In all likelihood, I will be admin'ing several hundred databases whose > schema is identical. Being the lazy admin that I am, I was thinking that it > may be better for me to combine everything into one large database and just > make views for each former database (after appending a key to each table, > and appropriately naming the view). This would be much more manageable, > since db objects (procedures, triggers, table schemas) would update for all > at once. My developer team is changing things pretty frequently too. To > enforce version consistency through all the databases, this would be the > best and easiest way to do that. > > What are the down sides? I know that I can no longer partition the data into > separate directories. A table corruption would effect everyone. More data > needs to be stored (addt'l keys). Are there other downsides? If there are > too many down sides, is there an easier way I can update the db objects (a > tool) for each DB all at once? Wouldn't most indexes also have to have the logical_key prepended to it, to help isolate the data? Otherwise, data would "bleed over" between "systems". Also, since the developers would only see views, how would they use the logical_key in their queries so that they are efficient? === To: pgsql-general@postgresql.org From: "Jim C. Nasby" <jim@nasby.net> Subject: Re: [GENERAL] Many little databases or one big one? Date: Wed, 7 May 2003 19:20:15 -0500 On Mon, May 05, 2003 at 04:04:24PM -0400, Jason Hihn wrote: > make views for each former database (after appending a key to each table, > and appropriately naming the view). This would be much more manageable, You need to be careful if performance is an issue. Because PGSQL doesn't support clustered indexes/index organized tables, you can get into a situation where you end up tablescanning instead of using the index to access the particular set of data you want. If you look in the performance archives for April, you'll see several messages about this. ===