Re: Partitioning Vs. Split Databases - performance?

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

On 12/21/06 13:17, Joshua D. Drake wrote:
> On Thu, 2006-12-21 at 11:10 -0800, Benjamin Smith wrote:
>> I'm breaking up a database into several sets of data with similar layout. (we
>> currently have multiple customers using a single database and tableset, we're
>> splitting it out to give us more "wiggle room")
>>
>> It seems that there are basically two ways to proceed:
>>
>> 1) Copy out the data specific to a customer and load into a separate database
>> for that customer, or

This gives you linear growth potential, since if your current box
gets over-utilized, buy a 2nd box and move some of the databases to it.

>> 2) Copy out the data specific to a customer and load into separate tables
>> (with slightly different names, EG table "dates" becomes "cust1_dates") and
>> use data partitioning to help with performance as needed.

Definitely *not* scalable. And *very* messy. Yech.

> 3) Put each customer in their own schema/namespace which resides within
> its own table space.
>
> Then you can move customers wherever you need in terms of IO.

Splitting like mentioned in these three tactics means that you've
now got 2x as many tables. Add more customers and you've got that
many more tables. Perfect candidate for "schema drift".

If each table has cust_id in it, then you could:

4) retain 1 database and partition each table on cust_id.

http://www.postgresql.org/docs/8.1/interactive/ddl-partitioning.html

>> Given the same physical hardware, which one is likely to perform better? Does
>> it make any difference? Does using separate databases use more RAM than a
>> single database with a bunch of different tables?

Config files are global, so I doubt it.

>> Company is growing rapidly, so growth room is important...

Then go for Option 1.

- --
Ron Johnson, Jr.
Jefferson LA USA

Is "common sense" really valid?
For example, it is "common sense" to white-power racists that
whites are superior to blacks, and that those with brown skins
are mud people.
However, that "common sense" is obviously wrong.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFFiuTPS9HxQb37XmcRAoF/AJ0ZUcj9C3Bwn7II0hfFzFrZjzA2wQCg6pNS
Tbmm4Rr8uluu/hjZ5gqrT9s=
=lkm+
-----END PGP SIGNATURE-----