Which Multi-tenant approach is recommended

Question

As per Multi-Tenant Data Architecture post, there are 3 ways to implement multi-tenancy

1. Separate Databases

2. Shared Database, Separate Schemas

3. Shared Database, Shared Schema

I have following details:

1. User should be able to backup and restore their data.

2. No of tenants : 3 (approx)

3. Each tenant might belong to different domain(url).

4. There are some common tables for all the tenants.

5. No of tables in each tenant: 10 (initial)

I would like to know which approach is more suitable for me?

Answer 1

I think option 2 is the best one, but you still have an issue with requirement 1. Backup and restore is not available per schema. you will need to handle this using Import data or any custom tool. common tables will have separate schema.

In option 1, you need to handle requirement 4, common tables will be replicated between all databases.

Answer 2

The most important condition among all the 5 conditions is condition 4 - which says that some tables are common among all the tenant. If some tables are common then Separate Database (i.e. Option 1) is ruled out.

You could have gone ahead with option 2, shared database and separate schema but the number of tenants are quite less (3 in your case). In such a scenario adding the overhead of maintaining separate schemas is an overhead which should be avoided. Hence, we can skip Option 2 as well until we evaluate option 3.

Option 3 - Shared database with shared schema is will be most efficient option for you. It avoids the overhead of maintaining separate schemas and allows common tables among tenants. In shared schemas generally a tenant identifier is used across tables. Hibernate already has support for such tenant identifiers (just in case you will use Java-J2EE for implementation). The only problem may be performance as putting the data of all three tenants together in the same table(s) will lead to lower database access\search performance which you will have to counter with de-normalization and indexing.

I would recommend going forward with option 3.

Answer 3

For us, we have developed an ERP in HR which is used by about a forty of client and a thousand's of users; The approach used for the multi-tenancy is the third.

In addition, one of the technical separation tables used was the heritance.

Answer 4

I had the same issue in my system. We had an ad-network system that became pretty large overtime, so I was considering migration to multi-tenant architecture per publisher.

Option 3 wasn't relevant as some publishers had special requirements (additional columns/procedures and we are not supporting partitioning). Option 2 wasn't relevant since we had concurrency and load issues between customers. In order to reach high performance, with plans to scale-out up to 1000 publishers, with 20 widgets and high concurrency, Our only solution was Option 1 - Separate Databases.

We already migrated to this mode, and we have 10 databases running, with a shared database for configuration and ads. From performance, it is great. From high availability, it is also very good as each publisher traffic doesn't effect all the other. Adding new publisher is an easy step for us, as we have our template environment. The only issue we have is Maintenance.

Lately I've been reading about PartitionDB, It looks very simple to manage as you can have a gate database to perform all maintenance works including upgrades and top level queries. It supports common shared database (same we have already), and I am trying now to understand how to use the stand alone database as well.