September 13th, 2011, 09:38 PM
Simple Minisite Service DB Strategy
If you have a service that allows people to create their own minisites, is it best to have each site's data in a separate database, or in a single database?
I'm in the process of moving to a new server, and thought now would be a really good time to re-evaluate my database strategy.
Right now I have 100 customers running on a single database and it's running great. I really like to be able to make changes to the database structure one time, rather than 100 times, when I need to do so.
The number of rows for each table:
But, I realize the more minisites I get, the slower queries will be. I'm just wondering where that tipping point is. My goal is only 5 or 600 sites on this thing, so that would mean the biggest table would have around 30,000 rows and the smallest would have around 1,200.
Does anyone have any advice? Should I split up the databases now before it grows any more, or is the scale of the service small enough to be able to run everything on one db?
September 13th, 2011, 10:09 PM
There are a lot of disadvantages and advantages to both designs, but since your code is already designed to support multi-tenancy and it's working well for you I recommend leaving it as is. If, in the future, you need to move to a multi-instance model you can do so relatively easily. It's a lot harder to take code that is designed to be multi-instance and change it to be multi-tenant.
As long as your database is properly designed and properly indexed there should be no performance issues with only 30,000 rows. Database systems are capable of handling tables many times that size without performance issues.
Remember that a binary search can search 4 billion records in only 32 steps, and could search 18 quintillion records in only 64 steps. Single database tables are thus extremely scalable if properly indexed.
Last edited by E-Oreo; September 13th, 2011 at 10:17 PM.
September 13th, 2011, 10:21 PM
Thanks! That's good news, puts things into perspective a bit. Really appreciate it.