Designing for Multi-Tenant Applications

Tenant_id Column Approach

The most scalable and cost effective model is one database and set of tables for all tenants, with a tenant_id column in each table used to identify data for different tenants. You could use customer_id or a similar column name to identify each tenant's data; we'll use tenant_id in the rest of this topic.

Highlights for tenant_id column approach:

• The best system in terms of scalability, manageability, and cost control.

• Keep customer data separate via addition of filters on tenant_id into SQL statements generated by the application.

• Use the tenant_id column approach unless you have a good reason not to.

Reasons to not use tenant_id column approach:

• Your customers have different schemas - For this, use tables-per-tenant or DB-per-tenant approaches.

• For compliance reasons, some customers must not be in the same database as other customers - Go to DB-per-tenant, or a hybrid of tables-per-tenant and DB-per-tenant.

• For compliance reasons, each customer must have its own database - Go to DB-per-tenant.

Here's an example schema that uses a tenant_id column to distinguish data among tenants "acme" (1) and "ajax" (2):

/* this table has one row per tenant to define tenant names and IDs */
CREATE TABLE tenant(id int, name varchar(80));
INSERT INTO tenant VALUES (1,"acme"), (2,"ajax");

/* data for each tenant in these tables tied to the owning tenant by 
   tenant_id */
CREATE TABLE sales(tenant_id int, product_id int, amount numeric(18,2));
CREATE TABLE product(tenant_id int, product_id int, product_name varchar(80), 
   list_price numeric(18,2));

Now, to retrieve all sales including the name of the item sold for tenant "ajax", you would write a SQL query like:

SELECT s.product_id, p.product_name, s.amount
FROM sales s, product p
WHERE s.tenant_id = 2 AND p.tenant_id = 2
AND s.product_id = p.product_id;

To make the SQL more dynamic so you don't have to substitute the tenants in with application code all the time, you could use a session variable:

SET @tenant_id = (SELECT id FROM tenant WHERE name = "ajax");

SELECT s.product_id, s.amount
FROM sales s, product p
WHERE s.tenant_id = @tenant_id AND p.tenant_id = @tenant_id
AND s.product_id = p.product_id;

Tables-Per-Tenant Approach

Another great approach is to put all customers in the same database, but use separate sets of tables for each customer. We'll call this tables-per-tenant for short.

Highlights for tables-per-tenant approach:

• This system is good at reducing total resources used (disk, threads), but you will need to carefully manage your memory use as you add customers. Even small tables take a certain amount of memory, which can become a limiting factor for tables-per-tenant.

• This system is significantly more scalable than the DB-per-tenant approach so you should use it unless you strongly prefer DB-per-tenant for isolation of tenant data, for example, or you have few tenants or a big enough budget to provision enough disk for each tenant database needed in DB-per-tenant.

Figure 1. The tables-per-tenant approach, with a set of tables for each tenant, all in one database.

DB-Per-Tenant Approach

It’s also possible to put each customer in its own database: DB-per-tenant. This is easy to set up and great for prototyping, but for many budgets it will not scale to a significant number of tenants due to the resources used for databases and tables.

Highlights for DB-per-tenant approach:

• If every customer must be isolated at the database level for compliance reasons, the DB-per-tenant approach is your best option.

• This option will work well for small numbers of customers, but because each database has significant local disk space overhead, you will need to scale up your Workspace Group (or cluster for self-hosted) as you add customers and databases.

SET GLOBAL log_file_size_partitions = 8388608;
SET GLOBAL snapshot_trigger_size = 67108864;
CREATE DATABASE db_tenant1 PARTITIONS = 4;

Isolating Sets of Customers with Workspace Groups (Hybrid)

Some groups of tenants might need to be physically isolated from other tenants. You can achieve this with separate workspace groups:

• If some sets of customers must be totally walled off, Workspace Groups (or separate clusters in a self-hosted environment) provide the greatest isolation. For example, if all your customers in AWS Europe needed to be separated, you could have an AWS Europe Workspace Group.

• You would need to use this in conjunction with a tenant_id-column or tables-per-tenant approach as having every customer in its own workspace group will not scale well in terms of operational complexity or cost.

Example

Let's assume that one of SingleStore's customers has 10,000 customers with free, standard, premium and dedicated offerings. Workspaces can be used to create different service expectations between these offerings. Each group of tenants can be placed on a separate workspace, where the number of users and the size of the compute workspace could be adjusted to provide higher or lower quality of service per tenant.

Example offerings:

• Free - S-2 Workspace - 9,000 tenants with low performance requirement and infrequent usage.

• Standard - S-4 Workspace - 900 tenants, medium performance requirement with infrequent usage.

• Premium - S-16 Workspace - 99 tenants with high performance requirements and frequent usage.

• Dedicated - S-4 Workspace - 1 tenant whose data and compute capacity need to be dedicated.

Here, a tenant_id-column approach would be used throughout, so 9,000 tenants in the "Free" group could fit on one S2 workspace. The same approach would be used for the higher-level groups so the application code would only have to be written once and not be different for different tiers.