Ben,
On Thu, 2006-12-21 at 20:10, 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")
We have here a very similar situation. We started out with one schema
containing all customers, based on a customerid.
> 1) Copy out the data specific to a customer and load into a separate database
> for that customer, or
We went with 1, combined with the original solution we had... i.e. we
keep the same schema for customers, and if the number of customers/size
of them grows too much, we move them out to another machine. If they
need to be isolated from the rest, we move them to another DB possibly
on the same machine... but the original schema is still there to allow
us having multiple small customers on the same DB. Separate DBs on the
same box for each customer would be a maintenance nightmare because we
have a (sizable) cluster of application boxes connecting, and all of
them must be configured with all connection data to all data bases... so
we must keep the nr. of data bases to the minimum. Not to mention that
for each DB we have a connection pool, and the nr. of connections would
grow too much if we would have one connection pool per customer.
Grouping customers according to their business status allows us to
upgrade the schema separately for each DB, so we have some choice in
when to upgrade different customers to the next version of the
application... although usually we make the DB changes in such a way
that multiple versions of the software can work with the upgraded
schema, and we can upgrade customers from the same DB independently, but
this IS a concern when you have the same schema for all customers (we do
have occasional pressure from some customers to upgrade sooner than we
would like).
This does not exclude the possibility of partitioning the biggest tables
in your schema per customerid or whatever else you can use to partition
them (our biggest table does not have customerid as a column, but it
still could be partitioned by something else).
Cheers,
Csaba.