Multi-tenancy#
Serving several customers or organisations, called tenants, from one platform while keeping each tenant's data separate and preventing one tenant's load from affecting the others.
Before reading
Why it matters#
Platforms often hold data that belongs to, or must be shown to, different external parties: sellers on a marketplace, clients of a software product, business units with legal separation. A mistake in isolation shows one tenant another tenant's data, which is a serious breach. A mistake in resource sharing lets one tenant's heavy query slow everyone else down.
How it works#
There are three main isolation models, along a scale from shared to separate.
Shared tables: all tenants' rows live in the same tables, each row carrying a tenant key such as seller_id. Isolation depends on a row filter on the tenant key, applied on every path to the data. It is the cheapest and simplest to operate: one schema, one pipeline, one set of tables. It is also the easiest to get wrong, because a missing filter exposes everything.
Schema or table per tenant: each tenant gets its own schema or set of tables, built by the same pipelines. Access is granted per schema, which is easier to verify than a filter, and a tenant's data can be exported or deleted as a unit. Costs grow with the number of tenants: more tables, more metadata, more maintenance jobs, more small files.
Stack per tenant: each tenant gets separate storage, catalog and compute, sometimes in a separate account or region. Isolation is strongest and easiest to prove to auditors, and tenants can have different versions, regions or encryption keys. It is also the most expensive and the hardest to operate and upgrade.
flowchart TB
subgraph A[Shared tables]
direction LR
A1[(sales: all sellers,<br/>seller_id on each row)] --> A2[Row filter<br/>on seller_id]
end
subgraph B[Schema per tenant]
direction LR
B1[(seller_17.sales)] ~~~ B2[(seller_18.sales)]
end
subgraph C[Stack per tenant]
direction LR
C1[Storage, catalog,<br/>compute for seller 17] ~~~ C2[Storage, catalog,<br/>compute for seller 18]
end
A ~~~ B ~~~ CMany platforms mix models: shared tables for most tenants, a separate stack for a few large or regulated ones.
The tenant key must be present and trustworthy everywhere. It is set at ingestion from the source, never from user input; it is part of every key and join, so that a join cannot match rows of different tenants; and it is checked in tests, such as asserting that no row of a tenant's output has another tenant's key.
The tenant's identity must come from authentication, not from the request. A seller's token carries their seller ID as a claim, and the enforcement point uses that claim for the filter. A parameter such as ?seller_id=17 in an API call must never decide which tenant's data is returned.
Noisy neighbours are tenants whose workload consumes shared resources. Engines offer resource groups or queues that cap concurrency, memory and CPU per tenant or per workload. Large tenants can get dedicated compute while sharing storage. Cost attribution per tenant, through query tags or separate compute, makes heavy usage visible.
Key terms#
- Tenant A customer or organisation whose data must be kept separate.
- Tenant key The column identifying which tenant a row belongs to.
- Isolation model How tenants are separated: shared tables, schema per tenant, or stack per tenant.
- Noisy neighbour A tenant whose load degrades performance for others.
- Resource group An engine setting that limits resources for a set of queries.
Example#
The shop's marketplace sellers get a sales dashboard showing only their own sales. The shop uses shared tables with seller_id on every row of fct_seller_sales.
A seller's token includes the claim seller_id: s-17. The data API reads that claim and queries:
SELECT sales_date, sum(revenue) AS revenue
FROM shop.fct_seller_sales
WHERE seller_id = 's-17' -- from the token, never from the request
GROUP BY sales_date;
As a second line of defence, the API passes the seller's identity to the engine through token exchange, and the engine applies the row filter seller_id = the token's seller_id claim. A nightly test checks that every seller's exported report contains only their own seller_id. One very large seller runs heavy queries every morning; a resource group limits seller queries to a share of the cluster so that internal reporting stays fast.
Pitfalls#
- Tenant ID from the request. A changed parameter shows another tenant's data.
- One missing filter. In shared tables, every path needs the filter; enforce it in the engine, not only in application code.
- Joins without the tenant key. Rows from different tenants with the same product or order ID get combined.
- Per-tenant tables for thousands of tenants. Metadata and maintenance costs grow with every tenant.
- No per-tenant cost visibility. Heavy tenants go unnoticed until everyone is slow.