One database vs. hundreds?

From: "Kynn Jones" <kynnjo(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: One database vs. hundreds?
Date: 2007-08-28 12:08:36
Message-ID: c2350ba40708280508g138ced4bwb4135043be3d474c@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers

I'm hoping to get some advice on a design question I'm grappling with.
I have a database now that in many respects may be regarded as an
collection of a few hundred much smaller "parallel databases", all
having the same schema. What I mean by this is that, as far as the
intended use of this particular system there are no meaningful queries
whose results would include information from more than one of these
parallel component databases. Furthermore, one could delete all the
records of any one of these parallel components without affecting the
referential integrity of the rest of the database.

Therefore, both for performance and maintenance reasons, the idea of
splitting this database into its components looks very attractive.
This would result in a system with hundreds of small databases (and in
the future possibly reaching into the low thousands). I don't have
experience with such a situation, and I'm wondering if there are
issues I should be concerned about.

Alternatively, maybe there are techniques to achieve the benefits of
this split without actually carrying it out. The two benefits I see
are in the areas of performance and maintenance. As for performance,
I assume (naively, I'm sure) that searches will be faster in the
individual component databases, simply because it's a search among
fewer pieces of information. And for maintenance, I think the split
would make the system more robust during database updates, because
only a small component would be updated at a time, and the rest of the
system would completely insulated from this.

I'd very much appreciate your thoughts on these issues.

I imagine I'm not the first person to confront this kind of design
choice. Does it have a standard name that I could use in a Google
search?

TIA!

kj

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Kristo Kaiv 2007-08-28 12:22:35 INSERT doc discrepancy
Previous Message Dave Page 2007-08-28 11:32:57 Re: PostgreSQL vs Firebird feature comparison finished

Browse pgsql-hackers by date

  From Date Subject
Next Message Kamil Srot 2007-08-28 12:23:00 Re: One database vs. hundreds?
Previous Message Heikki Linnakangas 2007-08-28 11:02:25 Re: Testing the other tsearch dictionaries