Shared tables, schema per tenant or database per tenant for a multi-tenant SaaS

Moving a product from one deployment per customer to a single multi-tenant service is mostly a data question. Every other part of the change (authentication, billing, feature flags, background jobs) follows from where a tenant's rows live and what keeps them apart from everyone else's. At Cocomply I worked on moving a compliance product from single-tenant to multi-tenant SaaS, and this was the first decision on the table, because it is the one that is hardest to reverse once data is in it.

There are three honest options. Put every tenant in the same tables with a tenant column on each row. Give every tenant its own schema inside one database. Or give every tenant its own database. Each is a reasonable design; what changes is who pays, and when. This post sets out what decides it, what each option is good and bad at, and the order in which I would ask the questions.

What decides it

The criteria that matter are not the ones that come up first in a design meeting. Query simplicity and raw performance are roughly equal across the three at ordinary scale, with a well-indexed tenant column doing fine for most products. The real differences are operational: how a migration runs, how a bad tenant is contained, how a restore works, and how many of anything (connections, backups, alerts) the team has to manage.

The isolation row deserves emphasis. In shared tables the boundary between tenants is a filter that every query must apply, and the failure mode is a query that forgets it. That is not a reason to reject the option; it is a reason to make the filter impossible to omit. In practice I would put the tenant in request context and apply it in one place (a repository base class, a query middleware, or row-level security in the database) rather than trusting every developer to remember it in every query.

// Simplified: the tenant comes from context, never from the caller
export abstract class TenantRepository<T extends { tenantId: string }> {
  constructor(private readonly ctx: RequestContext) {}

  protected scoped(where: Partial<T> = {}) {
    return { ...where, tenantId: this.ctx.tenantId };
  }

  findMany(where?: Partial<T>) {
    return this.table.find(this.scoped(where));
  }
}

The migration row is the other one that decides real projects. A migration that takes four seconds on one database takes four seconds on shared tables, but on 300 schemas or 300 databases it is a batch job with its own failure modes: half-applied states, tenants on two different versions at once, and code that has to tolerate both until the batch completes.

The three options

Shared tables

Best for many small tenants on one product with one release train. One set of tables, a tenant column on every row, and an index that starts with it. This is the default for a SaaS that expects hundreds or thousands of tenants and ships one version of the product to all of them.

• Strengths: one migration per release; cross-tenant analytics and admin tooling are ordinary queries; onboarding is a row insert; connection pooling is trivial; cost does not grow with tenant count.
• Costs: the tenant filter is the only wall, so a missing clause leaks data; one tenant's heavy query slows everyone; restoring a single tenant to yesterday means filtered exports and careful re-inserts; per-tenant residency is not available without sharding.

Schema per tenant

Best for a modest number of tenants who need cleaner separation without separate infrastructure. Every tenant gets a schema in one database, with the same tables in each. The application sets the schema per request, so the tenant boundary is a name rather than a filter.

• Strengths: an accidental unscoped query hits an empty or wrong schema rather than everyone's rows; one tenant can be dumped and restored on its own; the database server, backups and monitoring stay singular.
• Costs: migrations run once per schema and the ordering and rollback story is yours to build; a database with thousands of near-identical schemas strains catalogue tables, tooling and connection poolers; cross-tenant reporting means unions or a separate warehouse; noisy neighbours still share the same CPU, memory and pool.

Database per tenant

Best for few, large tenants with contractual, regulatory or residency requirements. Each tenant gets its own database, sometimes its own server or region. The service holds a directory that maps tenant to connection.

• Strengths: the strongest isolation available short of separate deployments; per-tenant restore, encryption keys, region and even version are all natural; a tenant can be moved or offboarded by moving or dropping one database.
• Costs: every operational task multiplies by tenant count (backups, alerts, credentials, upgrades); migrations become fleet orchestration; connection counts grow with tenants unless pooled per database; any shared view of the data needs an export pipeline; the per-tenant floor cost makes small tenants unprofitable.

The questions, in order

The mistake I see most often is starting from the largest customer's demands and building the whole platform to satisfy them. The better order is to rule out what is forced, then choose the cheapest option that fits the tenants you actually expect.

flowchart TD
  S[New tenant model] --> Q1{Contract demands own storage}
  Q1 -- yes --> DB[Database per tenant]
  Q1 -- no --> Q2{Hundreds of tenants expected}
  Q2 -- yes --> SH[Shared tables]:::accent
  Q2 -- no --> Q3{Own restore or region needed}
  Q3 -- yes --> DB
  Q3 -- no --> Q4{Can run N migrations safely}
  Q4 -- yes --> SC[Schema per tenant]
  Q4 -- no --> SH

The first question is whether any customer contract or regulator requires physically separate storage. If so, those tenants need their own database whatever else you do, and the only remaining question is whether everyone else does too. The second is scale: past a few hundred tenants, anything that multiplies per tenant becomes the team's main job, and shared tables win on cost alone. The third is whether tenants need their own restore point or their own region, which shared tables cannot offer. The last is an honest look at the team: schema per tenant is only cheaper than a database per tenant if you are prepared to build and own the migration runner that keeps every schema on the same version.

The answers can be mixed. A shared-table platform with a small number of database-per-tenant customers is a common and sensible shape, as long as the application talks to both through the same tenant-resolution layer and the two paths are tested with the same suite. What I would avoid is running all three at once, because every feature then has three data paths to design and three to break.

Whichever option you choose, the two things worth building on day one are the same: a single place where the tenant is resolved from the request and attached to every query, and a test that runs the whole suite as two tenants and asserts that neither can see the other. The isolation model decides how much those two things have to carry; it does not replace them.

Takeaways

• Resolve the tenant once from request context and apply it in one place, never per query.
• Rule out forced isolation (contracts, residency) first, then pick the cheapest option that fits.
• Default to shared tables past a few hundred tenants; per-tenant anything becomes the team's main job.
• Run the whole test suite as two tenants and assert that neither can see the other.