Multi-Tenant Data Models in SaaS

Shard keys route rows, but nothing stops one tenant reading another's

Sharding ended with the celebrity problem: one wildly popular account lands on shard B, 40% of reads hit one machine while the others idle, and no hash function prevents it because the skew lives in popularity rather than key distribution. Routing solved placement, but placement is not permission. A tenant, which is one customer organization sharing your infrastructure with many others, expects its data to behave as if it owned the whole system. Every query must therefore answer which tenant it serves, usually with a tenant identifier column carried on each row. The naive approach is to add that column and trust application code to include it, and it works until the one endpoint or background job that forgets it. A forgotten filter does not return an error but returns the world, which means one customer sees another customer's invoices.

The opposite naive fix is a separate database per tenant, and it fails on cost rather than correctness. A second tempting fix is one shared schema with views per tenant instead of Row Level Security, and it fails on write paths: views filter reads but every insert, background job, and analytics endpoint writes through the base table, so one unfiltered writer still leaks, and 2,000 tenant views each need maintaining through every migration. Ten thousand tenants mean ten thousand backup schedules, connection pools, and migration targets, which is affordable only when each tenant pays like a large enterprise. Think of a restaurant kitchen that cooks under many different brand names from one room: one analogy for the whole idea, where sharing the kitchen is cheap but every plate must still carry the right brand's label, and the rest is mechanics about how strictly the labels are enforced.

Shared tables stay safe only when the database enforces the tenant boundary

Application checks alone fail open, which is why mature teams enforce the boundary inside the database too. Including the tenant identifier in primary keys, which are the unique addresses of rows, and in foreign keys, which are columns pointing at rows in another table, keeps every access path starting at the tenant. Row Level Security, which is a database feature that silently adds the tenant condition to every query and rejects queries with no tenant context, then makes a forgotten filter return nothing instead of everything. Middleware, which is request-handling code that runs before business logic, sets the tenant for each request and refuses to run background jobs that arrive without one.

Leaks look boring, which is exactly why they ship

The real incident is never dramatic. One analytics endpoint forgets the tenant filter, caches the result for twenty minutes, and serves one customer's invoice list to another customer until the cache expires. Or a background job runs without tenant context and emails every customer a usage report containing everyone else's rows. Shared-schema systems fail open by default, meaning any query without the filter returns all tenants rather than none. That is why the database must reject context-free queries even when the application forgets, and why noisy-neighbor stories rhyme the same way: one tenant's end-of-quarter export fills the shared memory and every other tenant's slow-request percentile doubles for the afternoon.

Tenant count and contract strength pick the shape before philosophy does

The arithmetic decides early. Ten thousand tenants on separate databases means ten thousand backup schedules, connection pools, and migration targets, and unless each tenant pays enough to fund that overhead the math never closes. Tens to low thousands of similar tenants belong in shared tables with Row Level Security, because one migration and one pool serve everyone. Thousands with a few giants fit a hybrid where the giants get dedicated homes and everyone else stays shared. Regulated enterprise tenants whose contracts promise per-tenant encryption keys, regional pinning, or point-in-time restore need separate databases, because only physical separation can deliver a restore of one tenant to last Tuesday without touching the rest.

Dials that keep shared tenancy honest under load

Isolation dials and noisy-neighbor dials solve different halves of sharing. Isolation dials make leaks structurally hard: composite keys and indexes leading with the tenant identifier, Row Level Security on every tenant table with deny-by-default, and middleware that refuses to run without context. Noisy-neighbor dials make hogs visible and movable: per-tenant rate limits and statement timeouts, which are hard caps on how long one query may run, separate pools for interactive and batch work, per-tenant usage metrics for rows and queries, and a tenant-to-location map so a loud tenant can move without a rewrite.

Migrations must roll out without waking every tenant at once

Shared schema means one migration touches everyone, which is the point until a bad one does. The safe sequence is expand then contract: add the new column as nullable, backfill existing rows in small tenant-ordered batches with pauses so the shared pool never saturates, then add the constraint once the backfill is verified. For separate schemas, never loop migrations serially across thousands of schemas inside a deploy, because the deploy stretches to hours and stragglers block release. Instead run a schema-migration worker that fans out, tracks a version per tenant, and lets slow tenants catch up asynchronously behind a compatibility window where old and new code both run.

Moving a giant out without downtime follows the dual-write evacuation

When one tenant grows from 1% to 30% of rows, complaints about slow neighbors turn serious. The evacuation runs like a careful move: provision the dedicated home, write new rows to both places behind a flag, backfill history in tenant-scoped batches with checksums, which are small fingerprints that prove the copied batch matches the original, then flip the tenant-to-location map entry and delete the old rows after a verification window. The map lives at the gateway so application code asks where a tenant lives instead of branching on tenant names. Teams that skip the map hardcode the split and relive the same migration for every subsequent giant.

Connection math kills per-tenant databases long before features do

A relational database comfortably runs a few hundred active backends, which are server-side processes serving client connections. Give each of 2,000 tenants its own database with just five pooled connections and the fleet needs 10,000 backend slots, which is roughly thirty times over budget before serving a single query. Separate schemas are lighter but still need per-request namespace switching, per-schema migration tracking, and pool partitioning so one tenant's burst cannot starve the rest. Shared schema keeps one pool, one migration, and one backup, which is why even teams promising dedicated databases usually run shared clusters with logical separation until a tenant pays enough to fund real isolation.

Encryption keys and residency rules force physical separation

Some tenants care less about speed than about whose disk holds their bytes. A healthcare tenant demands its own encryption key that it can revoke, a European tenant requires data pinned to its region, and a financial tenant wants audit logs only it can export. Shared rows can carry a key identifier per tenant for envelope encryption, which is encrypting each row with a per-tenant key that is itself encrypted by a master key, but revocation then means re-encrypting those rows under pressure, and residency means the whole cluster must satisfy the strictest tenant's geography. That is the honest trigger for physical isolation: when the contract names keys, regions, or per-tenant backups, that tenant gets its own home and a matching price.

Cross-tenant analytics runs on a separate path, never on the live cluster

Product inevitably asks for average orders per tenant and finance asks for cross-tenant revenue, both impossible when every production query is tenant-scoped. The pattern is a separate analytics path: stream tenant-tagged change events into a warehouse, which is a store optimized for large scans, where each row still carries its tenant identifier, then grant dashboards either one tenant's slice or aggregated-only views that can never return another customer's raw rows. Never run warehouse-style full-table scans on the production cluster, because one aggregation competes with every tenant's checkout for the same memory and the noisy-neighbor story writes itself during the first quarterly report.

Test tenancy like an attacker trying to read someone else's rows

The test suite needs a tenant-hopping test that creates two tenants, writes distinct rows as each, then runs every endpoint and background job as the other tenant and asserts zero rows leak. A second test runs representative queries with no tenant context at all and expects rejection rather than empty results, because rejection proves deny-by-default while empty results could merely mean an empty table. Seed random identifiers across tenants so collisions and timestamp ties surface early, and run the suite with Row Level Security enabled exactly as production runs it, since testing without it proves nothing about the real boundary.

Start shared, isolate the loud and the regulated

Tenancy is sharing done deliberately, with Row Level Security and tenant-led keys making a forgotten filter return nothing instead of everything. Sharing raises the read-side twin of the same problem: the homepage reunites the orders table with the users table ten thousand times a second, fetching a name it already fetched a millisecond ago, and no tenant label removes that join. Copying the name into each order so the hot read finds everything in one row is the denormalization trade.