mufassall

Cheat sheet · Architecture

Multi-tenancy

14 min · Advanced · mufassall.dtwin.uz/en/l/multi-tenancy/

← Open the lesson

The problem

A B2B invoicing service keeps 1 200 customer companies in one database. One forgotten `WHERE tenant_id` leaks invoices, one big customer's export slows everyone, and one migration can break them all at once. The fixes: isolation enforced by the database, a tenant taken only from the token, per-tenant quotas, a silo for the big tenant behind a routing catalog, per-tenant keys and migrations in waves.

What you learn

  • Why a tenant_id filter in application code leaks, and how row-level security with a per-transaction tenant context stops it
  • Where the tenant must come from: the signed token, never a header or URL the client controls
  • Noisy neighbours: per-tenant quotas, 429 with limit headers, heavy work in a queue
  • Pool, silo and bridge, tenant routing, per-tenant keys and migrations rolled out in waves
Final scene
40%60%

Diagram as code

The lesson's final design as text: paste it into Jira, GitLab, GitHub, Notion or your docs, where Mermaid renders by itself. There are D2 and PlantUML (Confluence, IntelliJ) versions too.

Mermaid: flowchart

flowchart LR
  n_ta(["Small tenant"])
  n_tb(["Large tenant"])
  n_api["Invoicing API"]
  n_db[("Shared DB")]
  n_exporter("Export worker")
  n_catalog[("Tenant catalog")]
  n_db900[("Silo DB")]
  n_kms>"Key service"]
  n_migrator("Migration runner")
  n_ta --> n_api
  n_tb --> n_api
  n_api --> n_db
  n_api -->|"async"| n_exporter
  n_exporter --> n_db
  n_api --> n_catalog
  n_api --> n_db900
  n_api --> n_kms
  n_migrator --> n_db
  n_migrator --> n_db900

Mermaid: request sequence

sequenceDiagram
  participant n_api as Invoicing API
  participant n_db as Shared DB
  participant n_tb as Large tenant
  participant n_catalog as Tenant catalog
  participant n_kms as Key service
  participant n_migrator as Migration runner
  Note over n_api,n_db: 1 200 companies, one database
  n_api->>n_db: SELECT id, total FROM invoices
  n_db-->>n_api: 3 rows
  Note over n_api,n_db: One forgotten filter, 37 companies in one CSV
  n_api->>n_db: SELECT id, tenant_id, total FROM invoices
  n_db-->>n_api: 1840 rows
  Note over n_api,n_db: Row-level security: the database filters
  n_api->>n_db: ALTER TABLE invoices ENABLE ROW LEVEL SECURITY#59;
  n_db-->>n_api: 312 rows (t_900 only)
  Note over n_tb,n_api: The tenant came from a header the user controls
  n_tb->>n_api: GET /v1/invoices?status=open HTTP/1.1
  n_api-->>n_tb: HTTP/1.1 200 OK
  Note over n_tb,n_api: Tenant from the signed token only
  n_tb->>n_api: Bearer token, decoded claims
  n_api-->>n_tb: tenant context = t_900 (from the token)
  Note over n_api,n_db: One tenant's export slows everyone
  n_api->>n_db: top tenants by DB time, last 5 min
  Note over n_tb,n_api: Per-tenant quotas and an export queue
  n_tb->>n_api: POST /v1/exports HTTP/1.1
  n_api-->>n_tb: HTTP/1.1 429 Too Many Requests
  Note over n_api,n_catalog: The biggest tenant gets its own database
  n_api->>n_catalog: SELECT cell, db_host, state FROM tenant_routes
  n_catalog-->>n_api: cell #124; db_host #124; state
  Note over n_api,n_kms: One encryption key per tenant
  n_api->>n_kms: POST /v1/keys/decrypt HTTP/1.1
  n_kms-->>n_api: HTTP/1.1 200 OK
  Note over n_migrator,n_db: Migrations in waves, not all at once
  n_migrator->>n_db: migrate 0042_invoice_currency

D2

direction: right

n_ta: "Small tenant" {
  shape: rectangle
}
n_tb: "Large tenant" {
  shape: rectangle
}
n_api: "Invoicing API" {
  shape: rectangle
}
n_db: "Shared DB" {
  shape: cylinder
}
n_exporter: "Export worker" {
  shape: rectangle
  style.multiple: true
}
n_catalog: "Tenant catalog" {
  shape: cylinder
}
n_db900: "Silo DB" {
  shape: cylinder
}
n_kms: "Key service" {
  shape: cloud
}
n_migrator: "Migration runner" {
  shape: rectangle
  style.multiple: true
}

n_ta -> n_api
n_tb -> n_api
n_api -> n_db
n_api -> n_exporter: "async"
n_exporter -> n_db
n_api -> n_catalog
n_api -> n_db900
n_api -> n_kms
n_migrator -> n_db
n_migrator -> n_db900

PlantUML: request sequence

@startuml
participant "Invoicing API" as n_api
database "Shared DB" as n_db
participant "Large tenant" as n_tb
database "Tenant catalog" as n_catalog
boundary "Key service" as n_kms
participant "Migration runner" as n_migrator
note over n_api, n_db : 1 200 companies, one database
n_api -> n_db : SELECT id, total FROM invoices
n_db --> n_api : 3 rows
note over n_api, n_db : One forgotten filter, 37 companies in one CSV
n_api -> n_db : SELECT id, tenant_id, total FROM invoices
n_db --> n_api : 1840 rows
note over n_api, n_db : Row-level security: the database filters
n_api -> n_db : ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
n_db --> n_api : 312 rows (t_900 only)
note over n_tb, n_api : The tenant came from a header the user controls
n_tb -> n_api : GET /v1/invoices?status=open HTTP/1.1
n_api --> n_tb : HTTP/1.1 200 OK
note over n_tb, n_api : Tenant from the signed token only
n_tb -> n_api : Bearer token, decoded claims
n_api --> n_tb : tenant context = t_900 (from the token)
note over n_api, n_db : One tenant's export slows everyone
n_api -> n_db : top tenants by DB time, last 5 min
note over n_tb, n_api : Per-tenant quotas and an export queue
n_tb -> n_api : POST /v1/exports HTTP/1.1
n_api --> n_tb : HTTP/1.1 429 Too Many Requests
note over n_api, n_catalog : The biggest tenant gets its own database
n_api -> n_catalog : SELECT cell, db_host, state FROM tenant_routes
n_catalog --> n_api : cell | db_host | state
note over n_api, n_kms : One encryption key per tenant
n_api -> n_kms : POST /v1/keys/decrypt HTTP/1.1
n_kms --> n_api : HTTP/1.1 200 OK
note over n_migrator, n_db : Migrations in waves, not all at once
n_migrator -> n_db : migrate 0042_invoice_currency
@enduml

PlantUML: components

@startuml
left to right direction
agent "Small tenant" as n_ta
agent "Large tenant" as n_tb
component "Invoicing API" as n_api
database "Shared DB" as n_db
collections "Export worker" as n_exporter
database "Tenant catalog" as n_catalog
database "Silo DB" as n_db900
cloud "Key service" as n_kms
collections "Migration runner" as n_migrator
n_ta --> n_api
n_tb --> n_api
n_api --> n_db
n_api --> n_exporter : async
n_exporter --> n_db
n_api --> n_catalog
n_api --> n_db900
n_api --> n_kms
n_migrator --> n_db
n_migrator --> n_db900
@enduml

Recap

Six rules for many customers in one system.

1) Do not trust every query to remember tenant_id: enforce it in the database with row-level security and SET LOCAL per transaction. 2) The tenant comes only from the signed token; any other source is input to verify, not to trust. 3) Quotas and queues per tenant keep one customer from taking everything. 4) Pool by default, silo for those who need or pay for it, a catalog that routes each tenant. 5) One key per tenant: a leak stays small, leaving is a key deletion. 6) Migrate in waves, with a canary and a version per tenant.

Numbers: at the start and at the end

MetricStartEnd
Rows leaked00
API p99120 ms140 ms
Hit by one change100%1%

Steps

  1. Case 1 200 companies, one database

    An invoicing SaaS sells to companies.

  2. Problem One forgotten filter, 37 companies in one CSV

    A developer adds an "overdue invoices" report for t_900.

  3. Fix Row-level security: the database filters

    A policy on the table adds tenant_id = current tenant to every query, even one that forgets it.

  4. Problem The tenant came from a header the user controls

    The API took the tenant from an X-Tenant-Id header sent by the web app.

  5. Fix Tenant from the signed token only

    At login the identity service writes the tenant into the access token and signs it.

  6. Problem One tenant's export slows everyone

    t_900, the biggest customer, starts an export of seven years of invoices, and then starts it again because it seemed slow.

  7. Fix Per-tenant quotas and an export queue

    The API counts requests and running exports per tenant.

  8. Fix The biggest tenant gets its own database

    t_900 is 30% of all data and pays for an isolated setup.

  9. Fix One encryption key per tenant

    Every tenant gets its own data key, wrapped by a master key in the key service.

  10. Fix Migrations in waves, not all at once

    Last quarter one ALTER TABLE locked the shared table for 40 minutes, for all tenants.

  11. Recap Recap

    Six rules for many customers in one system.

Terms

Row-level security
A database policy that adds a condition such as the current tenant to every query on a table, so rows of other tenants stay invisible even when a query forgets the filter.
Noisy neighbour
One tenant in a shared system using so much CPU, disk or connections that every other tenant slows down.
Pool, silo and bridge
Tenant isolation models: all tenants share resources (pool), a tenant gets its own (silo), or a mix with shared code and separate data (bridge).