Thread: Many little databases or one big one?
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? Thank you (again!)
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.
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? -- +---------------------------------------------------------------+ | Ron Johnson, Jr. mailto:ron.l.johnson@cox.net | | Jefferson, LA USA http://members.cox.net/ron.l.johnson | | | | The purpose of the military isn't to pay your college tuition | | or give you a little extra income; it's to "kill people and | | break things". Surprisingly, not everyone understands that. | +---------------------------------------------------------------+
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. -- Jim C. Nasby (aka Decibel!) jim@nasby.net Member: Triangle Fraternity, Sports Car Club of America Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"