Kynn Jones wrote:
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?
Hi Kynn.
I don't have experience in this type of application, but we use pgsql
partitioning for other reasons
and it has some of the features you want (data separation, query
performance, ...).
It can be worth reading:
http://www.postgresql.org/docs/8.2/interactive/ddl-partitioning.html
Regards,
--
Kamil
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
