Designing for Multi-Tenant Applications

SingleStoreDB is a great solution for multi-tenant analytical applications because of its unique architecture which provides ultra fast ingest, super low latency queries, and high concurrency. This article is for developers looking to create secure, performant, scalable, and cost effective applications on top of SingleStore. It contains design patterns you can use with workspaces and databases to get the most out of SingleStoreDB for your multi-tenant apps.

Multi-Tenant Design with Workspaces

Workspaces are how SingleStoreDB achieves separation of storage and compute, which makes them idea for use in multi-tenant applications.

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 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

Multi-Tenant Design with Databases

Customers have a few options when designing their databases with multi-tenancy in mind. The following sections describe various possible approaches (Plans).

Plan A: 1 Database and 1 Set of Tables

The most scalable, manageable, and cost effective model is 1 database and set of tables for all tenants: Plan A.

Highlights for Plan A:

The best system in terms of scalability, manageability, and cost control.
Keep customer data separate via Row Level Security, and Procedural Extensions Security.
Use Plan A unless you have a good reason not to.

Reasons to not use Plan A:

Your customers have different schemas - Go to Plan B
For compliance reasons, some customers must not be in the same database as other customers - Go to Plan C
For compliance reasons, each customer must have its own database - Go to Plan D

Plan B: 1 Database and N Sets of Tables

Another great approach is to put all customers in the same database, but use separate sets of tables for each customer: Plan B

Highlights for Plan B:

This system is good, but you will need to carefully manage your memory use as you add customers.
This system is significantly more scalable than Plans C and D, so you should use it unless you can't, due to compliance reasons.

Memory Management in Plan B

Memory use is proportional to:

# of tables in set * # of sets of tables * # of unique query plans

Use the following strategies to minimize the above formula:

Use views instead of tables.
Use fewer unique queries (queries that require individual query plans to be compiled).
Decrease the number of tables via merging (wider tables are ok).

Other memory minimization strategies include:

Turn off table autostats
Use fewer partitions

You can view your memory usage in the Cloud Portal.

Security in Plan B

Use RBAC to keep customer data separated.

This system can be extended to any number of users or user groups for a tenant (customer).

Plan C: Separate Workspace Groups

Some customers might want the highest possible level of isolation. You can achieve this with separate workspace groups: Plan C

Highlights for Plan C:

If some customers must be totally walled off, Workspace Groups 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 Plan A / Plan B setup as having every customer in its own workspace group will not scale well in terms of operational complexity or cost.

Plan D: 1 Database per Customer

It’s also possible to put each customer in its own database: Plan D

This is easy to set up and great for prototyping, but will not scale to a significant number of customers due to persistent cache constraints.

Highlights for Plan D:

If every customer must be isolated at the database level for compliance reasons, Plan D may be 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 as you add customers and databases.

Local Disk Use in Plan D

Unlimited Storage provides great benefits, but is distinct from the Local Disk (aka Persistent Cache), which is limited and holds cached data as well as other types of data, such as transaction logs.

Each database, on average, will use 4 GB for transactions logs for each partition, so if you have a lot of databases, this can add up quickly. This means that if you plan to have a lot of customers, you will need a large Local Disk under Plan D, which will mean scaling up your workspace.

Summary

When deciding which approach is best for your particular circumstances, consider which factors are most important to you and your different offerings. In many cases, our customers have used a hybrid approach across the patterns mentioned above. For example, some customers have one use case on Plan A, another on Plan B, and a handful of high value customers completely isolated with Plan C.