

So You Wanna Be a SaaS Provider? - derekc
http://gigaom.com/2010/05/23/so-you-wanna-be-a-saas-provider/?utm_source=feedburner&utm_medium=feed&utm_campaign=Feed:+OmMalik+(GigaOM)

======
ora600
The article is too fluffy for my tastes, but it states correctly that multi-
tenancy is a difficult problem where you have too choose between several non-
perfect solutions.

From the DB perspective, the major design decision seems to be: Does each
customer get his own schema, or do customers share a schema?

Problems with giving each customer his own schema:

* No DB vendor will ever admit to a limit on the number of schemas you can put on one database. However, more schemas mean more objects, and every RDBMS has a table with a list of all objects in the database. A list that is sometimes scanned by routine maintenance/monitoring procedures. As the database grows you will need to avoid "show databases" or "select count( _) from user_tables" like the plague.

_ You will become an expert writing code that goes over hundreds of identical
schemas to aggregate something. This is not fun, and normally does horrible
things to the location in shared memory where the database keeps cursors
(compiled queries). Identical queries on different schemas always compile and
cache separately.

Problems with shared schemas:

* Lots of code handling permissions and identity and who can see what * If you decide to do something to just a subset of the customers (migrate to a different server or upgrade version), you will need code to handle this. There is nothing ready made to support you. * Schemas grow very large, and performance of smaller customers can be impacted by their larger peers. * You don't get diskspace back when customers leave. * Sharding is not as trivial as it would be with separate servers.

Generally speaking, I prefer shared schemas. However, the most realistic
scenario is that you will have both - large customers will get their own
schemas and small ones will share. I've seen this happen 5 different times -
you start with one architecture (no matter which) and add the other and write
code to migrate customers.

The best solution I've came up with is to design for multiple shared schemas
and build the product to manage this. This will allow you to stay with one
schema as long as you are small, add more as you grow, and if you are lucky
enough to have large customers - creating another shared schema that happens
to host just one customer is almost no overhead. The important part here is to
build a really good sharding and migration tool.

I wish I had one like that to sell you :)

