Multiple databases or many many tables?

Question

I've done some research on this question (both via google and on here), but haven't found anything I felt matched my situation so am asking.

I have a project that currently has a one account - one environment model, and is looking to expand to one account - many environments. The environments will be identical (at least as far as table structure is concerned) and will require around 100 tables. I'm torn between two possible approaches:

1. Use a single database, with table prefixing to separate each environment and an unprefixed account table
2. Use many databases - a central account database, and a separate one for each environment (the central one will likely have other central, once-only data, such as tables for our forum software)

Are there any significant performance gains/concerns with either approach? The data will (at least for now) all reside on the same physical server. Queries should only ever need to access a single environment (except in very rare circumstances) - and of course the main accounts record.

Answer 1

Interesting question. As a standard answer I would suggest letting one instance run all the accounts. I.e. the prefix solution. This is the approach that is used all over by hosting providers.

It seems to make sense to have one RDBMS running the show. Easier for backups and other system-wide tasks. I would suggest that in terms of performance one instance running is going to be much more efficient than running a separate process for each account.

If you need to enable load balancing the 'prefix' model would also be easier to scale since most modern RDBMSs have addons/features to support this type of functionality, rather than having to set this up multiple times for each instance for each account.

Modern database systems are easily capable of handling thousands of requests per second, they won't have any performance loss by looking up fancy customised (prefixed) table names. As long as you can come up with a simple hierarchy (account-prefix) way of separating accounts you shouldn't have a problem running thousands of tables.

The only potential downside is security. And in most cases even having multiple servers wont fix your security problem.