Multi-tenant SaaS data isolation: shared tables, schemas or databases

Every business-to-business SaaS product stores data for many customers, usually called tenants. The most damaging bug such a product can ship is one customer seeing another customer's data. It is also one of the easiest bugs to write: a single query that forgets WHERE tenant_id = ? is enough.

How you isolate tenants is an architecture decision that is expensive to change later. It affects cost, migrations, backups, performance isolation, compliance conversations and how quickly you can onboard a new customer. This article explains the three common models, gives a decision table, and then shows how to enforce isolation in the shared-table model with PostgreSQL row-level security and Node.js, so that a forgotten filter returns nothing instead of someone else's rows.

The three models: silo, pool and bridge

AWS's SaaS guidance names three patterns, and the terms are now widely used:

• Silo: each tenant gets dedicated resources, for example its own database or even its own full stack, while still sharing one onboarding, identity and operations experience.
• Pool: tenants share resources, such as one database where every row carries a tenant identifier. This is the classic idea of multi-tenancy, built for economies of scale.
• Bridge: a mix, where some parts of the system are siloed and others pooled, for example a pooled application database with a dedicated analytics store for regulated customers.

For relational databases, these map to three concrete designs.

How to choose

Start from your customers, not from the database:

• Many small tenants on self-serve plans: shared tables. Database-per-tenant for thousands of free-trial accounts is mostly an operations bill.
• A few large customers with contractual isolation, data-residency or "bring your own key" requirements: database per tenant for those customers, which makes your system a bridge.
• Customers who regularly ask for a full export or a point-in-time restore of only their data: per-tenant schemas or databases make that far easier.
• Heavy, uneven workloads where one tenant can slow everyone down: plan for moving large tenants to dedicated resources later, even if you start pooled.

Many products start pooled and add a silo tier for enterprise customers. That only works smoothly if every table carries a tenant identifier from day one and every code path resolves the tenant explicitly, so the same code can point at a dedicated database later.

Enforcing isolation in shared tables with PostgreSQL row-level security

In the pooled model, the usual defence is discipline: every query must include a tenant filter. Discipline fails eventually. PostgreSQL's row-level security (RLS) adds a second layer inside the database, so a query that forgets the filter sees no rows instead of all of them. The examples below follow the PostgreSQL 18 documentation.

Step 1: Add the tenant column and enable RLS

CREATE TABLE projects (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id   uuid NOT NULL,
  name        text NOT NULL,
  created_at  timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX projects_tenant_id_idx ON projects (tenant_id);
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE projects FORCE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON projects
  USING (tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::uuid)
  WITH CHECK (tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::uuid);

Key behaviours, from the PostgreSQL manual:

• Once RLS is enabled, a table with no matching policy uses default deny: no rows are visible or can be modified.
• The USING expression controls which existing rows are visible; WITH CHECK controls which rows may be inserted or updated. Without WITH CHECK, the USING expression is used for both.
• Superusers and roles with the BYPASSRLS attribute always bypass row security. Table owners also bypass it unless you use FORCE ROW LEVEL SECURITY, which is why the example sets it.
• current_setting(name, true) returns NULL instead of raising an error when the setting does not exist. The NULLIF(..., '') wrapper also handles the case where the setting exists but is empty, which can happen on a pooled connection after an earlier transaction has ended; without it the cast to uuid would fail.

Step 2: Connect as a role that cannot bypass policies

CREATE ROLE app_user LOGIN PASSWORD 'set-from-your-secret-store'
  NOSUPERUSER NOBYPASSRLS;
GRANT SELECT, INSERT, UPDATE, DELETE ON projects TO app_user;

Run migrations with a separate owner role, and run the application as app_user. If the application connects as the table owner or a superuser, the policies above protect nothing.

Step 3: Set the tenant per transaction in Node.js

PostgreSQL's set_config(setting_name, new_value, is_local) sets a configuration parameter; when is_local is true, the value applies only to the current transaction. That property matters with connection pools: a value scoped to a transaction cannot leak into the next request that borrows the same connection. With the pg package (version 8.23 at the time of writing):

import pg from 'pg';
const pool = new pg.Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10,
});
export async function withTenant(tenantId, work) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    await client.query("SELECT set_config('app.tenant_id', $1, true)", [tenantId]);
    const result = await work(client);
    await client.query('COMMIT');
    return result;
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  } finally {
    client.release();
  }
}
// Usage inside a request handler, after authentication has
// resolved the tenant from the session (never from user input alone):
const projects = await withTenant(req.session.tenantId, async (db) => {
  const { rows } = await db.query(
    'SELECT id, name, created_at FROM projects ORDER BY created_at DESC LIMIT 50'
  );
  return rows;
});

Notice that the query has no WHERE tenant_id clause; the policy supplies it. Many teams still add the filter explicitly in application code, both for readability and so the query planner sees the condition directly. RLS then acts as the safety net rather than the only control. Because the setting is transaction-scoped, this pattern also works behind a transaction-pooling proxy, as long as every tenant-scoped query runs inside withTenant.

Step 4: Test the isolation, not just the features

Write integration tests against a real PostgreSQL instance that:

1. Create two tenants with data in every tenant-scoped table.
2. Query each table as tenant A and assert no tenant B rows come back.
3. Try to insert a row with tenant B's ID while scoped to tenant A and assert it fails the WITH CHECK policy.
4. Query without setting a tenant and assert zero rows.
5. Check, in CI, that every table with a tenant_id column has RLS enabled and forced, by querying the pg_class catalogue columns relrowsecurity and relforcerowsecurity.

If you use MySQL

MySQL does not offer an equivalent of PostgreSQL's CREATE POLICY, so pooled isolation in MySQL lives in the application. The practical substitutes are a data-access layer that refuses to build a query on a tenant-scoped table without a tenant condition, composite indexes that lead with tenant_id, foreign keys that include the tenant column so rows cannot reference another tenant's records, and the same cross-tenant integration tests described above. Database-per-tenant is also more common on MySQL for customers with strict isolation needs.

Things that leak across tenants besides SQL

• Caches: include the tenant ID in every cache key.
• Object storage: prefix keys by tenant and sign download URLs per request.
• Search indexes: filter by tenant on every query, or use one index per large tenant.
• Background jobs: carry the tenant ID in the job payload and set the database context when the job runs.
• Logs and error reports: they often contain customer data; restrict who can read them.
• Rate limits: apply per tenant as well as per user, so one tenant cannot exhaust shared capacity.

Summary

Pick the isolation model from your customer mix: pooled tables for many small tenants, dedicated databases for the few who need them, and a bridge of both as you grow. In the pooled model, do not rely on every developer remembering a WHERE clause. Put a tenant identifier on every row, connect with a role that cannot bypass policies, scope the tenant per transaction, and prove isolation with tests that try to break it.