Multi-tenant architecture: shared schema vs database per tenant

#postgres #database #saas #architecture

Every SaaS product has to decide where one customer's data stops and the next one's starts. There are three common answers. On a good day they look much the same. They differ in what happens on a bad one.

The three models

Shared schema: One database, one set of tables, and a tenant_id column on every row. Cheapest to run and simplest to migrate. Isolation depends on every query filtering by tenant_id.
Schema per tenant: One database server with a separate schema for each tenant. Tables are separate, connections are usually shared, and every migration runs once per schema.
Database per tenant: A separate database for each tenant, sometimes with a separate application process as well. The strongest isolation, and the most to operate.

Most teams start with a shared schema, and most should. The question is when it stops being enough.

Where a shared schema breaks

The classic leak isn't in the main screens. Those get tested. It's in the CSV export someone added on a Friday, the background job that loops over invoices, or the admin report that joins three tables and filters only one of them. One missing tenant_id filter and a customer sees someone else's data.

Postgres can enforce the filter for you with row-level security, so a forgotten WHERE clause returns nothing instead of 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')::uuid);

-- per request, inside the transaction:
SET LOCAL app.tenant_id = '6f1c...';

Two details catch people out. Without FORCE, the table's owner skips the policy, and apps often connect as the owner. Superusers skip it regardless. And SET LOCAL only lasts for the transaction, which is what you want with a connection pool: a plain SET would leak one request's tenant into the next request that borrows the connection.

The other shared-schema problem is the noisy neighbour. One tenant's slow report or bulk import competes with everyone else for the same tables, indexes and connections.

What database per tenant buys you

Isolation by connection. A pool opened against one database can't read another, so cross-tenant access stops being something you can write by accident.
Clean exits. Deleting a customer is DROP DATABASE, not a delete script across forty tables. That matters when a customer, or a data protection law, asks for erasure.
Per-tenant backup and restore. You can roll one customer back to yesterday without touching anyone else.
Bounded noisy neighbours. Give each tenant a small pool and a heavy tenant mostly slows itself down.
Room for different schemas, if different kinds of customer genuinely need different tables. Use this sparingly, because every variation is a code path to test.

There are two ways to run it. One application can keep a pool per tenant and pick the right one per request:

const pools = new Map();

function poolFor(tenant) {
  if (!pools.has(tenant.id)) {
    pools.set(tenant.id, mysql.createPool({
      database: tenant.dbName,
      connectionLimit: 5,
    }));
  }
  return pools.get(tenant.id);
}

Or each tenant gets its own process, started with its database name and holding exactly one pool. A process per tenant also isolates memory leaks and crashes, at the price of idle memory for every customer who isn't using the product right now.

What it costs

Migrations run N times. Skip one tenant and its schema drifts. Drift fails quietly: the one feature that needs the missing column returns a 500, and nobody connects the error to a migration that never ran.
Connections multiply. Fifty tenants with a pool of ten is five hundred possible connections. MySQL and MariaDB default max_connections to 151.
Cross-tenant questions get hard. "How many invoices did all customers send last month" becomes one query per database, or a separate analytics store fed from all of them.
Login needs a directory. With no global users table, something has to map an email address or a subdomain to the right tenant before authentication can even start.
Provisioning has many steps and has to survive failing halfway. Record each step as it completes, and save the database name the moment it exists, so a retry resumes instead of leaving an orphaned database behind.

Drift is the one to plan for on day one. Keep a migrations table in every tenant database, compare each tenant against the repository before a deploy, and refuse to deploy code that needs a column some tenants don't have yet. Reads that tolerate a missing optional column turn a broken module into a missing feature.

How to choose

How many tenants? Tens or hundreds of business customers can each have a database. Tens of thousands of free accounts can't, at least not cheaply.
What does one leaked row cost? For health, legal or financial records, the answer may be the company.
Do customers ask for their own backups, their own region, or proof of deletion? Those are much easier with a database each.
Do tenants really need different schemas, or just different settings?

My default is a shared schema with row-level security, plus a test that runs every query path as two tenants and fails if either sees the other. Move to a database per tenant when the answers above point there, not before. Schema per tenant sits in between, and it inherits the migration cost of the strict option without all of its isolation, so I rarely pick it.

Originally published at anukumar.site.

DEV Community