Learn

Architecture & pattern guides

Read the idea, build it on a Grid, prove it in a challenge. Short, practical guides — illustrated with RodGrid's own nodes — on system design, design patterns, flow modeling, and how to get the most out of the app.

All articles

Multi-tenancy

Serving many customers from one system: how much to share, keeping every query inside its tenant, stopping one tenant from slowing the rest, carrying the tenant through caches, queues and jobs, and giving a large tenant capacity of its own.

Twenty seconds to open a dispatch screen

On the last afternoon of the month, a little after four, the dispatch screens of a scheduling app started taking twenty seconds to open. At Hollis Plumbing, three technicians and two vans, that meant both vans parked outside a finished job, waiting for the next address. Hollis was not alone. The app serves about 3,000 plumbing and heating contractors from one deployment: one API, one Postgres cluster, one pool of background workers. At 16:02 Brightwater Home Services, a franchise with 900 technicians, had started its month-end report, and every dispatch screen had been slow since. Nobody at Hollis knows Brightwater exists, and nobody should have to.

That is the promise of multi-tenancy: many customers, each a tenant (here, one contracting company and all its staff), share one running system, and each should be able to act as if it were alone. Brightwater slowing Hollis down is a failure of performance isolation. The worse failure would be one of data isolation: Hollis seeing even one of Brightwater's customer addresses. Both shape the first decision, which is how much to share.

Why 3,000 contractors share one set of tables

Most of the 3,000 have one to ten technicians and pay around $49 a month, too little for a database server each, so the app uses the cheapest layout: shared tables with a tenant id. One jobs table holds every contractor's jobs, each row carries a tenant_id, and the indexes its queries use start with it, so a query for one contractor's jobs reads only that contractor's slice of the index. Onboarding a contractor is an insert, a schema change is one migration, and it is a common SaaS layout. The other models buy more isolation at a higher price:

  • Schema per tenant: one database, with a schema per contractor holding its own copy of every table. One tenant's data is easier to export or delete, and a botched change to one schema stays there. But every migration now runs 3,000 times, and at thousands of tenants that per-schema work can outweigh what the separation buys.
  • Database per tenant: each customer's data in a database of its own, with its own backup and restore, which can be placed on a server of its own or in a chosen region. Each tenant costs a database's worth of operations, so here it is kept for the handful of customers who pay for it or are required to have it.

A database each for all 3,000 would mean 3,000 databases to migrate, back up and connect to, for customers paying $49. So the usual design is a ladder: the long tail on shared tables, and a few large customers moved up a rung when their size or their contract asks for it, running the same code. The rung above a database of one's own, a complete copy of the stack for a group of tenants, has its own pattern in Deployment Stamps.

A filter nobody can forget

Shared tables put every contractor's rows one missing clause apart. Last spring a developer added a technician search, and its query read WHERE name ILIKE ? and nothing else. It looked like every other query, so review missed it, and for two days a dispatcher who typed Mike saw every Mike on the platform, phone numbers included.

Isolation by convention fails on an ordinary day. The fix is to enforce the scope at three levels, so that one forgotten filter is not enough to cross between tenants:

  • The tenant comes from the authenticated session, never on the request's word. The gateway reads it from the signed session token and passes it downstream. A tenant_id in a JSON body or a query string is something any user can edit, so an endpoint that trusts it without checking the session's user belongs to that tenant hands every tenant's data to whoever changes a number.
  • Data access goes through a scoped repository: a query builder that adds the tenant condition itself and has no method that leaves it out. A forgotten filter becomes hard to write instead of easy to miss.
  • Row-level security in the database is the backstop. In Postgres, each table gets row security switched on (ALTER TABLE jobs ENABLE ROW LEVEL SECURITY) and a policy that compares tenant_id with a setting the app sets at the start of each transaction (SET LOCAL app.tenant_id), so a query without the filter still returns only the current tenant's rows. Two details decide whether it holds: the app must connect as a role that is not a superuser, lacks the BYPASSRLS attribute and does not own the tables, since the first two always bypass the policies and the owner does unless the table forces them, and the setting must be scoped to the transaction, or a pooled connection carries one request's tenant into the next.

Inside one contractor, a dispatcher assigns jobs while a technician sees only their own; those rules come after this coarser check, and Authorization: roles, relationships & policies explains how to model them.

Forty million rows at month-end

Brightwater's report reads every job event from the last 13 months, about 40 million rows, on the Postgres primary that serves everyone's dispatch screens. For minutes it holds connections and CPU, and Hollis's small query waits behind it. Nothing leaked; the system just had no idea one tenant was using most of it. Data isolation is a correctness problem; this one, the noisy neighbour, is a capacity problem.

The API already had a global cap of 1,000 requests a second. It protects the system, not the tenants: one customer at 900 a second stays within it, the other 2,999 share what is left, and no dashboard says why. The defences that work are per tenant, roughly cheapest first:

  • Per-tenant limits and quotas at the gateway: requests a second, rows per export, jobs in flight. The counting algorithms belong to Rate limiting; deciding that the limit belongs to each tenant rather than to the whole API is the multi-tenancy decision.
  • Fair queues. Report jobs used to wait in one first-in, first-out queue, so Brightwater's sixty location reports sat ahead of Hollis's single one. A queue per tenant, with workers taking from each in turn, keeps one big backlog from starving everyone behind it, as long as the cap on jobs in flight stops its long reports from holding every worker at once.
  • Per-tenant connection budgets, so one tenant's reports can hold a few database connections and never the whole pool, the same compartment idea that Bulkhead applies to any shared resource.
  • Capacity of its own for the tenant, the top rung, covered below.

Each of these needs cost measured per tenant from the first day, with queries, worker time and storage tagged with the tenant that caused them; a limit is guesswork when nobody knows who uses what. Billing customers for that usage is a separate pipeline, described in Usage metering & billing pipelines.

Limits and queues only work if every hop knows the tenant. A report job carries its tenant id in the message, and the worker opens its transaction with that tenant set, as a web request does. Caches are where the scope slips most quietly:

Watch outTechnician ids are numbered per contractor, so Hollis and Brightwater both have a technician 123. A cache keyed tech:123:schedule holds one schedule for both, so each company can be shown whichever the other wrote last; a key that starts with the tenant, t:4471:tech:123:schedule, cannot. A search index queried without a tenant filter is the same mistake: a data leak with a nice UI.
One deployment, a quota and a scope per tenant

The gateway takes the tenant from the session and checks its quota; the API keys everything by tenant and picks the database the tenant lives in. Report workers set the tenant from each job before they query.

When Brightwater outgrows the pool

Limits protect the others, but they make Brightwater, the customer who pays most, the one whose report runs slowest. So it moves up the ladder to a database of its own, on its own server, with the same schema and code, and spreading tenants across databases this way is the tenant-shaped case of Sharding & partitioning. A small directory records where each tenant's data lives, and the API reads it before picking a connection; Regional routing & geo-partitioning works through such a directory, and the copies that must follow a tenant, for the case where the reason is a country rather than a size.

Remove one filter and see what stops it

The Multi-Tenant SaaS Platform Challenge asks for this foundation, from resolving the tenant on every call to dedicated capacity for the largest one. Draw the gateway with its per-tenant Rate Limiter, the shared database beside a dedicated one, and the queue workers. Then run one check on your Grid: take the tenant filter out of one query in your head, and point at the component that still keeps another tenant's rows out. If the only answer is code review, the requirement is not met yet.

Practice this