Designing Multi-Tenant APIs That Scale

"Multi-tenant" just means one app serving many separate customers (tenants) out of the same running system. One Slack instance serves thousands of companies, and none of them see each other's messages.

Most multi-tenant APIs isolate customers the same simple way: one database, one set of tables, and a tenant_id column every query filters on. A query for customer A never touches customer B's rows.

It's the cheapest way to ship the feature. For a while, it's genuinely fine. What doesn't get said out loud is what "fine" is resting on: every query, written by every engineer, forever remembering to add that one WHERE clause.

This post starts there, with that one clause, and fixes it properly.

Then it follows the same question up the stack. Tenants sharing a database also share rate limits and connection pools, and those need fixing too. Last, it covers how a failure should look from the outside. It also covers how to keep all of this out of the business logic that shouldn't have to think about it.

Start with the version that looks fine

@itsnas — TypeScript

Hide line numbersCopy code

// GET /invoices?status=overdue
async function listOverdueInvoices(req: Request) {
  const invoices = await db`
    SELECT id, customer_name, amount, due_date
    FROM invoices
    WHERE tenant_id = ${req.tenantId}
      AND status = 'overdue'
    ORDER BY due_date ASC
  `
 
  return json(200, { invoices })
}

main

Nas (@itsnas)

This works. Every handler that touches invoices looks like this, and code review catches the ones that don't, for a while.

The problem isn't this function. It's the shape of the guarantee: isolation exists because a person remembered to type it, in every file, every time.

This is one endpoint in a small invoices API: GET /invoices, GET /invoices/:id, POST /invoices, all shaped the same way.

Notice req.tenantId isn't something the client sends in the URL or the body. It's resolved server-side, from an API key or JWT claim mapped to a tenant. If a client could set its own tenant id, none of what follows in this post would matter. They could just ask for someone else's data directly.

That's the whole problem with tenant isolation as a convention instead of a rule. It breaks under the same conditions any unenforced convention breaks under: deadline pressure, a file nobody's touched in months, a query copied from an answer that was never written with tenants in mind.

Isolation models, and what each one is actually buying you

Before fixing the query-level problem, it's worth naming the three shapes a multi-tenant backend usually takes, because the fix looks different at each one.

• Shared schema, shared tables (the example above): every tenant's rows live in the same tables, and a tenant_id column tells them apart. This is the cheapest option to run: one set of migrations, one connection pool (a small, reused set of open database connections that requests borrow instead of each opening its own). But the isolation is only logical, not physical. Nothing stops a query from reading across tenants except the query being written correctly, which is exactly the gap the trace above fell into.
• Schema-per-tenant: one #postgres schema per tenant, with the same table names in each, and search_path (which schema a connection reads from) switched per request. This gets closer to physical isolation: a query without a schema qualifier just fails, instead of silently reading another tenant's rows. The cost is running migrations across every schema, and a connection pool that now has to track which schema each connection currently points at.
• Database-per-tenant: full physical isolation. Easiest to reason about, and easiest to tell a customer who requires "our data can never share infrastructure with anyone else's." It's also the first to stop scaling: "one pool per tenant" becomes a thousand pools once you have a thousand tenants. Each open database connection costs real memory, so there's a hard ceiling on how far this goes.

Tenant A

Tenant B

Tenant C

Invoices API

Postgres - shared tables, tenant_id column

1. Tenant A to Invoices API: auth token identifies tenant
2. Tenant B to Invoices API
3. Tenant C to Invoices API
4. Invoices API to Postgres - shared tables, tenant_id column: tenant_id-filtered query

Most teams start shared-schema, because it's the cheapest to build. They hit the leak trace above later, in production or in an audit. Then they have to choose: re-architect towards physical isolation, or push enforcement down into the shared schema instead. The second option is usually right, until a specific customer's compliance requirement forces the first.

Fixing the leak: push isolation into the database

The fix isn't "review harder." It's making the database itself refuse to hand back another tenant's rows, even when a query has no tenant filter at all.

Postgres has a built-in feature for exactly this, called row-level security. You attach a policy to a table once. From then on, every query against that table gets filtered by the policy automatically, whether the query remembered to filter or not:

@itsnas — SQL

Hide line numbersCopy code

ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
 
CREATE POLICY tenant_isolation ON invoices
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

main

Nas (@itsnas)

The application sets app.tenant_id once per request, on the same connection that runs the query. Every statement against invoices on that connection is then filtered by Postgres itself, not by whether someone remembered a WHERE:

@itsnas — TypeScript

Hide line numbersCopy code

async function withTenant<T>(
  tenantId: string,
  fn: (db: Sql) => Promise<T>,
): Promise<T> {
  return db.begin(async tx => {
    await tx`SELECT set_config('app.tenant_id', ${tenantId}, true)`
    return fn(tx)
  })
}
 
// GET /invoices?status=overdue
async function listOverdueInvoices(req: Request) {
  const invoices = await withTenant(
    req.tenantId,
    db => db`
    SELECT id, customer_name, amount, due_date
    FROM invoices
    WHERE status = 'overdue'
    ORDER BY due_date ASC
  `,
  )
 
  return json(200, { invoices })
}

Show all 24 lines

main

Nas (@itsnas)

Re-run the leak trace against this version. The reporting endpoint's SELECT * FROM invoices WHERE status = 'overdue' still has no tenant_id clause in it anywhere.

It still only returns the calling tenant's own rows. The policy is enforced by Postgres underneath the query, not by the text of the query. The forgotten clause stops being a leak, and becomes a non-event.

The true third argument to set_config scopes the setting to the current transaction, and that matters more than it looks. Without it, a pooled connection could leak tenant A's setting into tenant B's request on the same connection. Scoping it to the transaction means the setting dies with the transaction, whether it commits or rolls back.

The leak that survives RLS: forgetting to set the setting at all

One gap remains, and it's the mirror image of the original bug. Say a handler simply never calls withTenant: a new route added without the wrapper, or a background job that runs outside a request entirely. The query then runs with no app.tenant_id set.

Written as in the policy above, that's actually fine. current_setting with no fallback value throws an error, so the broken request fails loudly and immediately, which is exactly what you want.

The dangerous version is the one where someone "fixes" that error by adding a fallback:

The true second argument there suppresses the error instead of surfacing it. Leave it off. Then a job that forgot to scope itself fails loudly in the logs. That's better than quietly returning zero rows and looking like a data problem instead of a wiring one.

The isolation fix doesn't touch the noisy-neighbor problem

Row-level security closes the "which rows come back" question. It says nothing about "how much of the database's capacity does this request get to spend."

One tenant running a report that scans a few million rows holds locks and burns I/O. Every other tenant's requests are now waiting behind it. RLS doesn't slow that query down. It just correctly returns that one tenant's own few million rows.

This is the noisy neighbor problem. It's easy to miss once RLS is in place, because everything "looks" isolated now. Two things actually address it: rate limits and connection pooling. Both get harder in exactly the same way, once the API stops being one process.

Rate limiting has to live above any single instance

A per-tenant rate limit sounds like a counter: track requests per tenant per minute, reject once a tenant crosses its budget.

That's correct for one running copy of the API. It's wrong the moment there are two, which is the normal case behind any load balancer.

The counter needs one shared home, not a separate copy on every instance. Redis is the usual choice: one INCR with an expiry, using the same key no matter which instance handles the request:

@itsnas — TypeScript

Hide line numbersCopy code

async function checkRateLimit(tenantId: string): Promise<boolean> {
  const key = `rate-limit:${tenantId}:${currentMinuteBucket()}`
  const count = await redis.incr(key)
  if (count === 1) await redis.expire(key, 60)
 
  return count <= REQUESTS_PER_MINUTE
}
 
// Runs before the handler, for every route.
async function rateLimitMiddleware(
  req: Request,
  next: () => Promise<Response>,
) {
  const allowed = await checkRateLimit(req.tenantId)
  if (!allowed) {
    return json(
      429,
      { error: 'Rate limit exceeded' },
      {
        'Retry-After': '60',
      },
    )
  }
 
  return next()
}

Show all 26 lines

main

Nas (@itsnas)

Every instance runs the same middleware against the same Redis key. The count from the trace above becomes what it should have been all along: one shared 100, not five separate 100s.

This is the opposite lesson from row-level security, on purpose. RLS needed no new shared state, because Postgres was already the one shared thing every instance talks to. A rate limit has no such natural home. The instances themselves are what's being multiplied, so the limit needs a shared store on purpose, or "per tenant" quietly becomes "per tenant, per instance."

Connection pooling multiplies with every instance you add

The same instance-count problem shows up again, with the connection pool. It's easy to miss, because the number that looked safe on one instance was never the number that actually mattered.

Sizing a pool per instance, without accounting for instance count, is the same mistake as the rate-limit trace: correct locally, wrong in aggregate. Two fixes, and they compose:

• Divide the real budget by instance count. If Postgres can sustain 100 connections and you run 10 instances, each instance's pool is sized to 10, not 20, with headroom left for Postgres's own background workers.
• Put a pooler in front of Postgres. Most commonly PgBouncer, which multiplexes many app-side connections onto far fewer real ones. Each instance can still open whatever pool size it likes; PgBouncer is what actually enforces the ceiling Postgres can sustain.

API instance

API instance

API instance

PgBouncer - connection pooler

Postgres - fixed max_connections

1. API instance to PgBouncer - connection pooler
2. API instance to PgBouncer - connection pooler: many app-side connections
3. API instance to PgBouncer - connection pooler
4. PgBouncer - connection pooler to Postgres - fixed max_connections: few real connections, fixed regardless of instance count

Errors: turning an internal failure into an honest status code

Every scenario this post has walked through eventually surfaces at the edge of the API, as some response the client sees. What it surfaces as matters just as much as whether it's caught at all.

A few of these are easy to get wrong in the same direction: telling the client more than it should be able to infer.

The sharpest case: tenant A requests a resource that belongs to tenant B, say GET /invoices/:id with someone else's invoice ID. RLS already handles the data: the query itself returns zero rows, so tenant B's row never leaks. What's still up to the handler is the status code for those zero rows. And 404 Not Found is the right answer, not 403 Forbidden:

The fix falls out of how RLS already answers the query: don't run a separate existence check at all. Treat "zero rows" as 404, whether the row doesn't exist or just belongs to someone else. The database physically cannot show the handler tenant B's row, so the handler was never in a position to leak it.

This is one of RLS's quieter benefits. Correct authorization falls out of correct isolation, instead of needing its own separate check that could itself have a bug.

The rest of the errors this post has raised map onto ordinary HTTP the same honest way. The important discipline is doing that mapping in one place, error-handling middleware every route passes through, rather than each handler picking its own status codes:

Situation	Status	Why
No tenant resolved from the request (bad or missing auth)	401	Nothing about the request identifies who's asking; this is caught before any handler or query runs
Query returns zero rows, tenant-scoped	404	RLS already made this safe to be honest about, see above
Rate limit exceeded (earlier)	429	Tell the client to slow down and when to retry, via Retry-After
Connection pool exhausted, request timed out waiting	503	Temporary capacity problem, not a bug; Retry-After lets a well-behaved client back off instead of retrying immediately into the same exhausted pool
app.tenant_id never set (a route that forgot withTenant)	500	This is a wiring bug, not a client error, and should page someone, not be silently retried

Keep tenant plumbing out of business logic

Tenant resolution, RLS scoping, rate limiting, pooling, error mapping: everything above is plumbing. It's worth being deliberate about where that plumbing ends and business logic begins.

The original bug in this post, a query that forgot tenant_id, was only possible because business logic had direct, unscoped access to the database. The real fix wasn't just RLS. It was making sure ordinary application code never gets a connection that isn't already scoped.

The shape that keeps this true is a request context that carries the already-scoped database handle, not a raw tenantId string a function could choose to ignore:

@itsnas — TypeScript

Hide line numbersCopy code

interface RequestContext {
  tenantId: string
  db: Sql // already inside withTenant's transaction
}
 
// Domain logic. No tenant_id in sight, no way to query outside scope,
// because `ctx.db` is the only database handle this function ever
// sees, and it was already scoped before this function was called.
async function markInvoicePaid(
  ctx: RequestContext,
  invoiceId: string,
) {
  const [invoice] = await ctx.db`
    SELECT id, status FROM invoices WHERE id = ${invoiceId}
  `
  if (!invoice) throw new NotFoundError('invoice')
  if (invoice.status === 'paid')
    throw new ConflictError('already paid')
 
  await ctx.db`
    UPDATE invoices SET status = 'paid' WHERE id = ${invoiceId}
  `
}
 
// Wiring. Every cross-cutting concern this post covered happens here,
// once, not re-implemented per handler.
async function handlePayInvoice(req: Request) {
  return withTenant(req.tenantId, async db => {
    const ctx: RequestContext = { tenantId: req.tenantId, db }
    await markInvoicePaid(ctx, req.params.invoiceId)
    return json(200, { ok: true })
  })
}

Show all 33 lines

main

Nas (@itsnas)

markInvoicePaid can be unit tested with no auth, no rate limiter, and no HTTP layer in sight. It has no tenant_id parameter to forget to pass, because there's nothing for it to do with one.

Everything this post spent so much space getting right, which tenant, how much they can spend, what status code a failure becomes, lives in one layer that wraps business logic. It's not a detail every new domain function has to remember on its own.

1. 1

   Treating tenant_id filtering as a code-review discipline

   A convention enforced by human attention degrades under deadline pressure and in the file nobody's looked at in months. Push the filter into the database with row-level security so a missing WHERE clause is a non-event instead of a leak.

2. 2

   Running application traffic through a role with BYPASSRLS

   Row-level security is opt-in per role. A superuser or BYPASSRLS connection sails past every policy silently. Keep the migration role and the application role separate.

3. 3

   Scoping the tenant setting to the session instead of the transaction

   A session-scoped setting on a pooled connection can leak one tenant's context into the next request that reuses the connection. Set it with the transaction-local flag on set_config so it can't outlive the transaction that set it.

4. 4

   Letting an unset tenant context fail silently

   A fallback that suppresses the error on a missing app.tenant_id turns a wiring bug into rows that quietly vanish, which looks like a data bug to whoever debugs it next. Let the missing setting raise.

5. 5

   Assuming row-level security also solves noisy neighbors

   RLS governs which rows a query can see, not how much of the database's capacity it's allowed to spend. A shared schema still needs per-tenant rate limits and pool sizing that accounts for instance count, or one tenant's spike degrades everyone.

6. 6

   Rate limiting with an in-memory, per-instance counter

   A limit enforced locally on each instance becomes the real limit times the instance count once the API is horizontally scaled. Back it with a shared store like Redis so the count is the same number no matter which instance answers the request.

7. 7

   Sizing a connection pool per instance without dividing by instance count

   A pool that looked safe with one instance running becomes (pool size x instance count) real connections against Postgres, which has its own fixed max_connections. Divide the real budget across instances, or put a pooler like PgBouncer in front of Postgres to enforce the ceiling directly.

8. 8

   Switching schemas per session under transaction-mode pooling

   A schema-per-tenant design that sets search_path per session sets state that outlives a single transaction. Under a transaction pooler, the next tenant to borrow that connection can inherit the previous one's schema. Either use session pooling for that design, or prefer RLS's transaction-scoped setting, which is safe under transaction pooling by construction.

9. 9

   Returning 403 for another tenant's resource instead of 404

   Distinguishing "exists but isn't yours" from "doesn't exist" lets an attacker enumerate real IDs across every tenant without ever reading a row, a classic broken object level authorization finding. Let the tenant-scoped query be the only source of truth, and treat zero rows as 404 regardless of why there were zero.

10. 10

    Handing business logic a raw tenant_id or an unscoped database handle

    If a domain function can reach the database directly, it can also forget to scope the query, the exact failure this post opened with, one layer up. Pass a request context whose db handle is already tenant-scoped, so there's no unscoped connection left to misuse.

Shared-schema multi-tenancy is the right starting point for most products. It's cheap to build and cheap to operate. The mistake is trusting any single layer to hold that isolation up by convention: a WHERE tenant_id = ... clause, a per-instance counter, a per-instance connection pool.

Push each of them into something that enforces it structurally instead. Row-level security for data. A shared store for limits and connections. An honest status code for failures. And a scoped context for the business logic that should never have to think about any of it.

---