Re: Any Plans for cross database queries on the same server?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 01/30/07 13:33, Brandon Aiken wrote:
> I always assumed the general argument is if you need to query different
> databases on the same server with the same application, they ought not
> to be separate databases because they're clearly related data.

Just because they are related, doesn't mean that it's always wise to
lump it all in the same database. Mainly for scalability and
performance reasons.

Our system looks like this set of databases:
REF database has all customer "low-volatility" data.
TRANS1 - Transaction data for accounts 0 - 999999.
TRANS2 - Transaction data for accounts 1000000 - 1999999.
TRANS3 - Transaction data for accounts 2000000 - 2999999.
etc.

This allows us to backup all the databases at the same time, and
horizontally scale as systems reach capacity

Currently, "joins" between REF & the TRANSx databases are handled by
the app server, but being able to attach to both databases and being
able to do distributed joins would really simplify our apps.

Of course, reducing the complexity of our apps means increasing the
complexity down to the RDBMS... And thus the balance of the
Universe is maintained.

> It's kinda like "why isn't there a way to do an exactly one to exactly
> one relationship between tables?". Well, because if one A always means
> one B and one B always means one A, shouldn't they ought to be in the
> same table already?

Vertical partitioning. If it's a large table, and certain columns
are "hot", while others not accessed so much, then partitioning the
table would speed up access to the hot column.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFFv6G1S9HxQb37XmcRAumFAKCghFl/ryKtLQ+nlyP+jMRF3NJj1ACgruEU
wok9v3BkB6EFlJ01i/nYDLI=
=gzzP
-----END PGP SIGNATURE-----