Re: Partitioning Vs. Split Databases - performance?

Depending on what kind of company you have, you may (or may not) want to
consider how you will upgrade the schema over time. Might it be the case
that some customers will need a different schema than others? If so, a
simple data partitioning by customer id probably won't be the best option.

On Thu, 21 Dec 2006, 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
>
> 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.
>
> 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?
>
> Company is growing rapidly, so growth room is important...
>
> -Ben
> --
> "The best way to predict the future is to invent it."
> - XEROX PARC slogan, circa 1978
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>