Multi-tenant or single-tenant: choosing a SaaS architecture you can still afford at 200 customers

The question is not which model is better. It is which failure you can survive. Shared-database multi-tenancy risks one customer seeing another's data. Database-per-tenant risks a migration that takes a weekend and a bill that scales with your customer count rather than with your revenue. Apoliums picks between them on four inputs: the regulatory position, the size distribution of customers, the expected schema churn, and who is going to run the migrations.

What are the three real options?

There are three, and the middle one is chosen far less often than it is discussed. Shared schema with a tenant column, schema per tenant, and database per tenant. They differ on exactly two axes that decide the cost of everything afterwards: where isolation is enforced, and how many migrations one schema change costs you.

Shared schema with a tenant column. Every table carries tenant_id. One database, one schema, one migration. This is the default for most B2B SaaS and it is the right default. The entire risk sits in one place: a query that forgets the filter.

Schema per tenant. One database, one Postgres schema per customer, identical table definitions in each. Isolation is structural, but migrations now run N times and connection pooling gets awkward because search_path is per-session.

Database per tenant. Full isolation, per-tenant backup and restore, per-tenant residency. Also N databases to migrate, N sets of connections, and a fixed cost per customer that does not care whether that customer pays you fifty dollars or fifty thousand.

When is a shared schema the wrong answer?

A shared schema is wrong when a single customer's requirements would force the whole platform to meet them. Three triggers push one tenant out of the shared database: data residency written into a contract, a restore obligation covering that customer's data alone, and a size distribution so uneven that one tenant shapes every query plan. In detail:

• Data residency by contract. A customer whose data must stay in a named jurisdiction cannot live in a shared database in another one. This is a hard boundary, not a preference.
• Restore granularity. If a customer can demand a point-in-time restore of their data without affecting anyone else, a shared database makes that a data-surgery exercise rather than a restore.
• Wildly uneven size. One customer at ten million rows and forty at ten thousand means every index, every query plan and every long-running report is shaped by the outlier. The small customers pay for the large one in latency.

Everything else — "enterprise wants isolation", "it feels safer" — is usually satisfiable with strong enforcement in a shared schema, and a shared schema is what you can still operate at two hundred customers with a team of four.

How do you make a shared schema actually safe?

By making the tenant filter impossible to omit, not by remembering to write it. Two mechanisms do that, and they are not alternatives — production systems want both. Row-level security enforces the filter inside Postgres, below anything the application forgets, and a tenant-scoped data access layer refuses to run a query that has no tenant context attached.

Row-level security in the database. Postgres row-level security enforces the filter below the application, so a forgotten WHERE clause returns nothing rather than everything.

ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoices FORCE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON invoices
  USING (tenant_id = current_setting('app.tenant_id', true)::uuid);

Two details decide whether this works. FORCE ROW LEVEL SECURITY is required, or the table owner bypasses the policy — and your migration user is frequently the table owner. And the application must connect as a role that is not superuser and not the owner, because both bypass RLS entirely. An RLS policy that has never been tested from the application's actual role is decoration.

A tenant-scoped data access layer in the application. Every query goes through a repository that takes the tenant context from a request-scoped store and refuses to run without it. Fail closed:

export function tenantDb(ctx: RequestContext) {
  if (!ctx.tenantId) {
    throw new Error("tenant context missing");
  }
  return db.$extends({
    query: {
      $allModels: {
        async $allOperations({ args, query }) {
          args.where = { ...args.where, tenantId: ctx.tenantId };
          return query(args);
        },
      },
    },
  });
}

The rule that makes this hold: no route handler may import the raw client. Enforce it with a lint rule, because a code review will eventually miss it.

What breaks first in a shared schema at scale?

Not correctness. Noise. One tenant's batch import saturates the connection pool, or their unbounded report scans a table everyone else is reading, and every other customer sees a slow product for twenty minutes. The answer is per-tenant limits on expensive work, not a different tenancy model.

The fixes are boring and they work:

• Per-tenant concurrency limits on expensive operations. Exports, imports and report generation get a small per-tenant semaphore. A tenant can queue as much work as they like; they cannot run it all at once.
• statement_timeout set per role. Background and reporting roles get a much shorter ceiling than the interactive one. A runaway query dies instead of holding locks.
• Every index carries tenant_id first. A composite index on (tenant_id, created_at) serves both the filter and the sort. An index on created_at alone will be scanned across all tenants and then filtered, which is the wrong order of operations at any real data volume.
• Cache keys are prefixed with the tenant. This is the most common source of cross-tenant leakage in systems that got the database right, because the cache layer is usually added later by someone who is thinking about latency rather than isolation.

What does the migration cost actually look like?

A shared schema has one migration; database-per-tenant has N, and N only grows. This is the argument that decides most projects, and it is worth stating plainly. One migration either succeeds or rolls back and the pipeline knows within seconds. N migrations need partial-failure handling and application code that works against two schema versions at once.

A shared schema has one migration. It runs once, it either succeeds or it is rolled back, and the deploy pipeline knows the answer in seconds. The risk is that a bad migration affects everyone at once, which is why expand-migrate-contract matters: add the new column, backfill it, dual-write, switch reads, then drop the old one — four deploys instead of one, with every intermediate state runnable.

Database-per-tenant has N migrations, and N is only a small number today. At two hundred customers a migration is an orchestrated job with partial-failure handling: some tenants on the new schema, some on the old, and application code that must work against both for as long as the rollout takes. That is not a hypothetical cost — it is a permanent tax on every schema change you make for the life of the product.

Schema-per-tenant has the same N problem with none of the residency or restore benefits. It is worth choosing only when you need per-tenant table-level customisation, which is rarer than it sounds.

Can you start shared and move an individual tenant out later?

Yes, and this is the pattern Apoliums recommends most often: a shared schema by default, with a documented path to lift a single tenant into a dedicated database when a contract requires it.

Three things have to be true from day one for that path to exist:

1. The connection is resolved per request, not at boot. A tenant resolver maps tenant to connection string. On a shared deployment it returns the same connection for everyone; the indirection is what lets one tenant point somewhere else later without a rewrite.
2. Every primary key is a UUID. Sequential integers collide the moment data moves between databases, and remapping foreign keys during a tenant export is the kind of migration nobody wants to write under contract pressure.
3. No query joins across tenants. Cross-tenant aggregate reporting is the one thing that makes a tenant genuinely unextractable. Serve internal analytics from a warehouse fed by an event stream, not from joins across the operational tables.

Build those three and the decision stays reversible. Skip them and "we can always split it later" is a claim with no code behind it.

The short version

Start with a shared schema, tenant-scoped at both the database and the application layer, with UUID keys and a per-request connection resolver. Move a tenant to its own database when a contract, a residency requirement or a restore obligation forces it — not when a sales conversation raises it. The architecture that survives two hundred customers is the one where a schema change is still a single deploy.