Multi-tenant data

Multi-tenant data is how a SaaS product stores many customers’ data while keeping each customer, or tenant, walled off from the rest. Each tenant can get its own database, its own schema in a shared database, or just a tenant_id column on rows in shared tables, and shared tables can be spread across many databases with a catalog saying where each tenant lives. The choice sets how strong the wall is, what a tenant costs, and how painful migrations, restores and deletions become.

Picture it first: a CA firm’s filing room

A chartered accountant’s firm in Pune keeps the books for 3,000 small businesses. It can store their papers in four ways.

• A locked almirah per client. No clerk can pull the wrong ledger, and a leak soaks one client’s files. But that is 3,000 almirahs and keys, and a new government form means updating all of them.
• One almirah, a labelled shelf per client. One key, but every shelf repeats every folder type.
• One big cabinet, every sheet stamped with the client’s GSTIN. Cheapest, and a new form changes one cabinet. But a clerk who forgets to check the stamp hands one client’s ledger to another.
• Several rooms of stamped cabinets and a register at the front desk saying which room holds which client. A client whose papers fill half a room gets a room of its own.

Map it back: almirahs are databases per tenant, shelves are schemas per tenant, the stamp is a tenant_id column, and the rooms plus register are a sharded pool with a tenant catalog. The client with its own room is the hybrid. The forgetful clerk is the bug most of this article is about.

Four ways to lay out tenant data

Tenancy models: silo, pool and bridge asks how much of the stack a tenant gets to itself; this article zooms into the data tier, where that answer bites hardest. (What is a tenant? settles what a tenant is.)

Database per tenant. Each tenant gets its own database, and the app finds its connection details in a catalog. This is silo at the data tier: credentials, backups, keys and region can differ per tenant, and a query physically cannot read another tenant’s rows. You pay with one database per tenant to provision, migrate, monitor and connect to.

Schema per tenant. One database holds a schema per tenant (acme.invoices, zenmart.invoices …), and the app picks one per transaction by setting PostgreSQL’s search_path. You keep one database’s connections and backups, but every table and index exists once per tenant, and every migration runs once per schema.

Shared tables with a tenant_id column. One set of tables; every row records its owner, and every query must filter on it. This is the pool: cheapest per tenant, one migration for everyone, and a cross-tenant report is one query. The wall between tenants is now a WHERE clause, so it needs backstops.

Sharded pool. When one shared database runs out of headroom, run many and keep a tenant catalog mapping each tenant to its shard. All of a tenant’s rows live on one shard, so its joins and transactions never cross the network. Citus, a PostgreSQL extension, builds this in: tables distributed by the tenant column have each tenant’s rows co-located on one node.

Hybrid. Every database has the same schema with a tenant_id column, but some hold one tenant and others thousands: trial tenants share a crowded database, paying tenants a quieter one, the largest get their own. Because the schema is identical everywhere, moving a tenant is a data copy, not a code change. Azure’s SQL patterns describe exactly this, and most large SaaS data tiers end up here.

Keys and constraints for shared tables

If tenants share tables, make the schema itself tenant-aware. Five rules cover most of it.

1. Lead every primary key with tenant_id. PRIMARY KEY (tenant_id, id) keeps a tenant’s rows together, and Citus requires primary and foreign keys to include the distribution column so one node can check them.
2. Use globally unique IDs. Per-database auto-increment IDs collide the day a tenant moves shards; UUIDv7 or Snowflake-style IDs don’t.
3. Put tenant_id into every foreign key. FOREIGN KEY (tenant_id, customer_id) REFERENCES customers (tenant_id, id) lets an invoice point only at a customer of the same tenant, even when a bug passes the wrong ID. See foreign key design.
4. Scope uniqueness per tenant. Two companies can each have a user priya@gmail.com, so write UNIQUE (tenant_id, email). A global unique constraint also leaks: PostgreSQL checks constraints without row-level security, so a “duplicate email” error tells tenant B the address exists elsewhere.
5. Lead secondary indexes with tenant_id. Nearly every query is “for this tenant, where …”, so (tenant_id, status, created_at) makes the tenant filter free. See composite index design.

CREATE TABLE customers (
  tenant_id uuid NOT NULL REFERENCES tenants (id),
  id        uuid NOT NULL,                 -- globally unique (UUIDv7)
  email     text NOT NULL,
  PRIMARY KEY (tenant_id, id),
  UNIQUE (tenant_id, email)                -- unique per tenant, not globally
);

CREATE TABLE invoices (
  tenant_id    uuid        NOT NULL,
  id           uuid        NOT NULL,
  customer_id  uuid        NOT NULL,
  status       text        NOT NULL,
  amount_paise bigint      NOT NULL,
  created_at   timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (tenant_id, id),
  FOREIGN KEY (tenant_id, customer_id) REFERENCES customers (tenant_id, id)
);

CREATE INDEX invoices_by_status ON invoices (tenant_id, status, created_at DESC);

Reference data that belongs to nobody, such as currency codes, stays global; Citus copies such reference tables to every node so joins stay local.

Enforcing isolation in a pool

AWS’s tenant isolation whitepaper is blunt: networks and IAM roles cannot separate tenants sharing one table, and sharing raises the chance of cross-tenant access, so a pool needs more diligence, not less. Use independent layers, so one bug is not one breach:

1. Tenant context from a trusted source: a signed token, never a parameter the client controls (see routing requests to tenants).
2. A tenant-scoped data access layer: repositories or ORM scopes that add tenant_id = ? to every query. It catches most mistakes, but code can go around it.
3. Database enforcement: row-level security, so a query that goes around layer 2 still sees one tenant.
4. Tenant-scoped credentials where the store supports them, such as DynamoDB’s leading-key condition.
5. Per-tenant encryption keys for the data that matters most.

Row-level security in PostgreSQL

Row-level security (RLS) attaches a policy to a table: a Boolean expression PostgreSQL evaluates for each row before the query’s own conditions. Rows where it is not true are invisible to SELECT and untouchable by UPDATE and DELETE, and with RLS enabled, a table with no policy denies everything.

-- once, as the table owner (the migration role)
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoices FORCE ROW LEVEL SECURITY;   -- filter the owner too

CREATE POLICY tenant_isolation ON invoices
  USING (tenant_id = current_setting('app.tenant_id')::uuid);
-- with no WITH CHECK clause, USING also checks inserted and updated rows

-- every request, as the application role
BEGIN;
SELECT set_config('app.tenant_id', $1, true);    -- true: this transaction only
SELECT * FROM invoices WHERE status = 'overdue';  -- forgot tenant_id, still safe
COMMIT;

If nothing set app.tenant_id, the policy raises an error or matches nothing: the failure is closed, not open. And since the policy is just another condition on tenant_id, indexes that lead with it serve it.

Azure’s guidance notes that many teams skip RLS because tenant identity must reach the database with every query. Setting it per transaction in shared code pays that cost once.

Set the tenant per transaction, not per connection

RLS is only as good as the tenant value it reads, and the danger is the connection pool. If request A runs a session-level SET app.tenant_id = 'acme' and returns its connection, request B for zenmart may borrow it. If B forgets to set its own tenant, the policy faithfully enforces the wrong one.

SET LOCAL, or set_config(…, true), lasts only until the transaction ends, so the value cannot outlive the request. It needs a real transaction: outside BEGIN … COMMIT, SET LOCAL only prints a warning. Prefer set_config with a bind parameter, since SET accepts none and string concatenation invites SQL injection. A transaction-mode pooler such as PgBouncer pushes the same way: it lends a server connection one transaction at a time, lists session SET as unsupported in that mode, and runs no reset query between clients.

Tenant-scoped credentials

Where the store’s access control understands keys, use it as another wall. In DynamoDB, an IAM policy can limit a caller to items whose partition key matches given values, through the dynamodb:LeadingKeys condition key with the ForAllValues modifier. A pooled service fetches short-lived credentials scoped to the current tenant, so a bug that builds another tenant’s key gets “access denied”.

{
  "Effect": "Allow",
  "Action": ["dynamodb:GetItem", "dynamodb:Query", "dynamodb:PutItem", "dynamodb:UpdateItem"],
  "Resource": "arn:aws:dynamodb:ap-south-1:111122223333:table/Orders",
  "Condition": {
    "ForAllValues:StringEquals": {
      "dynamodb:LeadingKeys": ["${aws:PrincipalTag/tenant_id}"]
    }
  }
}

PostgreSQL has no row-level IAM, so its equivalent is RLS plus a database role the app cannot escalate from. As the AWS whitepaper notes, every store needs its own mechanism, and where a store has none, the application layer is all you have.

Per-tenant encryption keys

Encryption at rest protects disks, not tenants: in a shared database everyone’s rows sit under one key. For per-tenant keys, use envelope encryption: encrypt a tenant’s data with a data key, and that data key under a tenant-specific key in a key management service. With bring your own key (BYOK), that key lives in the tenant’s own account, so the tenant can revoke your access.

Per-tenant keys also make deletion provable. Crypto-shredding destroys a tenant’s key so every copy of its ciphertext, including backups you cannot edit, becomes unreadable. AWS KMS makes data under a deleted key unrecoverable, after a mandatory 7 to 30 day wait your deletion promise must allow for.

The price: databases encrypt whole files under one key, so per-tenant keys in a pool mean application-level encryption of chosen columns, which the database can no longer index or search. Azure lists tenants who bring their own keys as a common reason to isolate them. More in encryption and key management.

Beyond relational: NoSQL, objects, search and caches

The same choice (separate container, separate namespace, or shared with a tenant key) repeats in every store.

DynamoDB. A pooled table puts the tenant first in the partition key (acme, or acme#ORDER with the entity in the sort key), so every query is per tenant and the leading-key policy applies. A table per tenant is the silo: per-tenant capacity and deletion, but thousands of tables against per-account quotas. See Cassandra and DynamoDB.

Object storage. A prefix per tenant (docs/acme/…) in a shared bucket is cheap, with IAM policies limiting each tenant’s credentials to its prefix. A bucket per tenant buys a per-tenant key and clean deletion, but bucket counts are capped per account. See blob storage.

Search. An index per tenant keeps deletion clean, but thousands of tiny indexes waste per-shard overhead. A shared index with a tenant_id field, routing so a tenant’s documents share a shard, and a filter forced on every query (a filtered alias per tenant) scales better. See Elasticsearch.

Caches. Prefix every key with the tenant (t:acme:invoice:42), never cache a tenant’s response under a URL-only key, and cap memory per tenant. See Redis.

The sharded pool and its catalog

A sharded pool needs one more component: something that knows where each tenant lives.

• Hash the tenant ID. hash(tenant_id) picks the shard, as Citus does by default and as consistent hashing does on a ring. No lookup, but you can’t place a tenant on purpose.
• Look it up in a catalog. A small table maps tenant to shard, cached by the router. One cached read per request buys the freedom to put any tenant anywhere.

For SaaS the catalog usually wins, because placement is a business decision: the whale gets its own shard, a German customer’s shard sits in Frankfurt, the free tier shares a crowded one. Azure’s SQL guidance pairs the catalog with tools that take a tenant offline, move it and update the mapping. Citus blends the two: tables are hash-distributed by tenant, and isolate_tenant_to_new_shard gives one tenant its own shard to move to a dedicated node.

The catalog is now on every request’s path. Keep it small (tenant, shard, region, tier, schema version, state), replicated, cached and free of personal data, so it can be global while tenant data stays in-region. More in database sharding and sharding a relational database.

Operations: where the models really differ

In production, the models differ most in what routine jobs cost.

Connections

PostgreSQL’s max_connections is typically 100 by default, changes only at a restart, and sizes shared memory, so connections are not free. A shared database is easy: 20 app instances with pools of 10 open 200 connections, which a modestly raised limit handles. Now give 1,000 tenants a database each, with every app instance keeping a pool of 10 per tenant database:

1,000 databases × 10 connections × 20 instances = 200,000 connections.

Spread over 10 servers, that is still 20,000 per server, 200 times the default. Pools of one still leave 1,000 × 1 × 20 = 20,000. Per-tenant pools multiply by the number of tenants; real demand follows the tenants busy right now.

So put a pooler in between. PgBouncer can front databases on many hosts, needs about 2 kB per client connection, and in transaction mode lends a server connection for one transaction at a time. Its pools are per database and user pair (20 server connections each by default), so cap them per database and let idle tenants’ pools sit empty. If about 150 tenants are active at peak, needing about 3 connections each, the fleet needs roughly 150 × 3 = 450 server connections. Schema per tenant dodges the problem, being one database. More in connection pooling.

Schema migrations across 1,000 databases

In a pool, a migration runs once, though a big table needs it done online (see online schema and data migrations). With a database per tenant it runs 1,000 times, and some runs fail. Treat it like a deployment:

• Waves. Canary tenants first, watch error rates, then 5%, 25% and the rest. At 50 databases at a time and about 2 minutes each, 1,000 databases take 1,000 ÷ 50 = 20 batches, or 40 minutes, plus soak time.
• Expand, then contract. Ship additive changes first, let the code handle both shapes, backfill, and drop the old shape in a later release.
• Version drift is normal. During a rollout, and whenever a database fails, two schema versions are live. Azure’s advice: record each tenant database’s schema version, and keep the app compatible with at least the previous one.

For very large fleets, Azure suggests flipping the trigger: the application notices a tenant’s old schema and starts its upgrade.

Catalog bloat with thousands of schemas

Schema per tenant looks like a cheap silo until you count objects. PostgreSQL stores every table and every index in a separate file. With 5,000 tenants and 50 tables of 3 indexes each:

250,000 tables (5,000 × 50) plus 750,000 indexes (5,000 × 150) = 1,000,000 relations, and at least as many files.

Each is a catalog row that the planner, autovacuum and backup tools walk, and each migration touches 5,000 copies. Azure calls per-tenant tables in one database an antipattern; schema per tenant is that pattern with a namespace. It suits hundreds of tenants; past a few thousand, prefer shared tables or separate databases.

Backups and single-tenant restore

The question that decides many designs: “a tenant ran a bad import at 10:43; can you put its data back as of 10:42 without touching anyone else?”

With a database per tenant, yes: point-in-time restore that one database and repoint its catalog entry. In a pool, the backup holds everyone. Azure’s guidance describes the path: restore the whole database to a separate server, extract the tenant’s rows from every table in foreign-key order, and merge them into live data, deciding what to do with rows changed after 10:42. A sharded pool shrinks the side restore to one shard, still holding hundreds of tenants. Script and rehearse it before a customer asks; see disaster recovery for RPO and RTO.

Export, deletion and residency

Data-protection laws and enterprise contracts often require you to hand over or erase a customer’s data on request. In a silo, export is a dump and deletion drops a database. In a pool, both walk every table, object prefix, search index, cache and stream by tenant_id, so everything must carry one. Delete in small batches, since one huge DELETE holds locks and leaves dead rows for vacuum, and crypto-shred the backups.

Residency is placement: a sharded pool puts the tenant on a shard in its region, a silo puts its database there, and a single global pool cannot answer it at all.

Cross-tenant analytics

This is the pool’s quiet advantage: “revenue by plan across all tenants” is one query, run on a replica by an audited role that bypasses RLS. With 1,000 databases it is a 1,000-way fan-out, or better, a pipeline streaming changes into a warehouse with tenant_id on every row, as Azure recommends. See change data capture and OLAP warehouses.

Hot tenants and skew

Tenants differ in size. The Citus guide observes that SaaS tenant data tends to follow a Zipf distribution: among 100 tenants the largest holds about 20% of the data, among 10,000 about 2%. At 10 TB in total, that is about 200 GB, which fits on one node. So the trouble is rarely fitting a tenant; it is whom it shares with.

A whale on a shard. When one tenant’s month-end reports, or a Big Billion Days-style sale on its storefront, saturates its shard, its hundreds of neighbours slow down. Per-tenant rate limits buy time (see noisy neighbours and fairness); the fix is a shard of its own. In Citus that is isolate_tenant_to_new_shard plus a shard move; elsewhere it is the online tenant move from the sibling article: bulk copy, stream changes, freeze briefly, flip the catalog.

A whale on a DynamoDB partition. Each partition delivers at most 3,000 read units and 1,000 write units a second, and one write unit is one write of up to 1 KB. Say megamart’s flash sale writes 2,500 orders a second of 2 KB each: 2,500 × 2 = 5,000 write units a second, five times a partition’s ceiling. Adaptive capacity moves hot items onto their own partitions and can split one key’s items by sort key, but not with a local secondary index or an ever-increasing sort key, and never past the ceiling.

The fix is a sub-partitioned key: megamart#0 to megamart#7, chosen by hashing the order ID, gives 5,000 ÷ 8 = 625 write units per key. Reads now fan out to eight queries, so flag it per tenant in the catalog and use it only for whales. A leading-key policy matching exact values must switch to a prefix match (StringLike on megamart#*).

Rebalancing. Shards drift apart as tenants grow and churn. Move whole tenants, never split one across shards, which breaks the “one tenant, one shard” rule that keeps joins local. Table partitioning can still split a big tenant’s history by month within its shard.

Choosing a model

A sensible default for a new B2B product: shared tables with tenant_id and RLS from day one, a tenant catalog even with one database, and the hybrid as the growth path. Start with a database per tenant when tenants are few and valuable, or contracts demand separate storage, keys or restores.

Going deeper: how tenant data leaks

The forgotten filter

Most pool leaks are a query that forgot its tenant: raw SQL in a report, an ORM escape hatch, a join filtered on one side only, an admin endpoint, and above all bulk statements. UPDATE invoices SET status = 'archived' WHERE created_at < … archives every tenant’s invoices.

RLS turns that bug from a leak into a smaller result, if the app role cannot bypass it. Tests catch the rest: seed two tenants in every integration test and assert that, acting as A, B’s rows never come back or change; fail the build if any table with a tenant_id column lacks an enabled, forced policy.

Caches, search and CDNs

Layers in front of the database have no RLS. A dashboard cached under /api/dashboard alone serves acme’s numbers to zenmart; a search without its filter mixes every tenant’s documents; a CDN caching an authenticated response hands it to the next visitor. Build the tenant into cache keys and search filters in one shared helper; see hot keys, stampedes and invalidation.

Jobs and pipelines that lose the tenant

Background jobs have no request token. Put tenant_id in every message and job payload, and have the worker set the tenant per transaction like a request handler. Jobs that truly span tenants (billing, migrations, analytics exports) use a separate, logged BYPASSRLS role. Pipelines must keep tenant_id on every row, or the warehouse is where everyone’s data mixes.

Making the silo cheaper

The case against a database per tenant is a fixed, mostly idle floor per database. Azure SQL’s elastic pools attack it: tenant databases on one server share a compute budget, so quiet tenants cost little and busy ones borrow headroom. Azure says pooled single-tenant databases can be about as cost-efficient as a few shared databases. The hidden cost: one shared database with 20 indexes becomes 20,000 indexes across 1,000 tenant databases, which only automated tuning can manage. Silo the data tier, and budget for automation as seriously as for hardware.

Cheat sheet

How to say it in the interview

“I’d keep tenant data in shared PostgreSQL tables, with tenant_id leading every primary key, foreign key, unique constraint and index, and globally unique IDs. Isolation is layered: tenant context comes from the token, a tenant-scoped repository adds the filter, and row-level security enforces it in the database, with the tenant set per transaction using SET LOCAL so pooled connections can’t leak it. A tenant catalog maps tenants to shards, so as we grow I add shards, place EU tenants in an EU region, and move whales or regulated customers into their own databases with the same schema. The price is harder single-tenant restores and deletes, so I script both up front.”

Why not a database per tenant for everyone? Every tenant pays a database floor, every migration runs N times, and per-tenant pools explode: 1,000 databases × 10 × 20 instances is 200,000 connections. It suits a few large or regulated tenants, which the hybrid gives you.

A developer forgets WHERE tenant_id. What happens? The repository layer should add it. If the query went around that layer, RLS returns only the current tenant’s rows, or fails if no tenant was set. Two-tenant integration tests should catch it first.

How do you restore one tenant in a shared database? Restore the database, or the tenant’s shard, to a side server at the target time; extract the tenant’s rows in foreign-key order; merge them into live data, resolving rows changed since. It takes hours, so rehearse it. In a silo it is one point-in-time restore.

One tenant is 30% of a shard’s load. What do you do? Rate-limit it per tenant now, then move it to a dedicated shard by copy, change stream and catalog flip. On DynamoDB, also split its partition key into buckets so no key exceeds 1,000 write units a second.

How do you delete a tenant completely? Walk every store by tenant_id: tables in batches, object prefixes, search documents, cache keys, queues. For backups, delete the tenant’s encryption key.

Where to go next

• Tenancy models: silo, pool and bridge: the same choice across the whole stack, and tenant moves.
• Routing requests to tenants: resolving tenant context and finding its shard.
• Noisy neighbours and fairness: per-tenant limits on a shared shard.
• Multi-tenancy and isolation: isolation beyond the data tier.
• Encryption and key management: envelope encryption and KMS.
• Database sharding: shard keys, directories and rebalancing.
• Connection pooling: pool sizing and transaction mode.
• PostgreSQL: the database behind most examples here.
• Design a multi-tenant SaaS platform: all of this in one design.

References and further reading

1. PostgreSQL documentation: Row Security Policiespostgresql.orgDefault-deny once enabled, policies evaluated before the query’s own conditions, implicit WITH CHECK, owner and BYPASSRLS exemptions, FORCE, constraint checks bypassing RLS, and row_security = off for backups.
2. PostgreSQL documentation: Connections and Authenticationpostgresql.orgmax_connections defaults to typically 100, can only be set at server start, and sizes shared memory.
3. PgBouncer: features and configurationpgbouncer.orgTransaction pooling, session SET unsupported in that mode, no reset query between clients, default_pool_size of 20 per database and user pair, and max_db_connections.
4. Citus documentation: Multi-tenant applicationsdocs.citusdata.comDistributing tables by tenant, co-location, composite primary and foreign keys, reference tables, DDL propagation, the Zipf-shaped tenant sizes and isolate_tenant_to_new_shard.
5. Azure Architecture Center: Architectural approaches for storage and data in multitenant solutionslearn.microsoft.comShared, sharded and per-tenant databases; single-tenant restore from a shared database; schema versions per tenant; table-per-tenant as an antipattern; RLS complexity; tenants with their own keys.
6. Azure SQL Database: Multitenant SaaS database tenancy patternslearn.microsoft.comDatabase per tenant with elastic pools, the tenant catalog, sharded and hybrid models, tenant-scale ranges, per-tenant point-in-time restore and the 20-to-20,000 index example.
7. AWS SaaS Tenant Isolation Strategies: Pooled storage isolation strategiesdocs.aws.amazon.comWhy pooled data needs more diligence, a leading-key IAM condition for DynamoDB versus row-level security for Aurora PostgreSQL, and services with no granular isolation.
8. Amazon DynamoDB Developer Guide: IAM policy conditions for fine-grained access controldocs.aws.amazon.comThe dynamodb:LeadingKeys condition key on the partition key, and the required ForAllValues modifier.
9. Amazon DynamoDB Developer Guide: Burst and adaptive capacitydocs.aws.amazon.comPer-partition limits of 3,000 reads and 1,000 writes per second, isolating hot items, and when item collections cannot be split.
10. AWS Key Management Service: Deleting AWS KMS keysdocs.aws.amazon.comData encrypted under a deleted key becomes unrecoverable; deletion needs a 7 to 30 day waiting period.