Multi-tenant SaaS in PostgreSQL: schema vs row-level

How you separate one customer's data from another's is the one decision in a SaaS backend that's genuinely hard to change later. Pick wrong and you find out eighteen months in, either because a migration now has to run across 3,000 schemas, or because one missing WHERE tenant_id = … showed a customer someone else's invoices.

PostgreSQL gives you three real options: a database per tenant, a schema per tenant, or one shared schema where every row carries a tenant_id. This post compares them honestly, explains why I default to the shared schema with PostgreSQL row-level security (RLS) for B2B SaaS, and walks through a setup for NestJS that I'd trust in production, including the connection pooling detail that can quietly leak data between tenants.

Part of my guide to building a scalable SaaS backend with NestJS and PostgreSQL.

TL;DR#

• Shared schema with a tenant_id column and RLS is the right default for most B2B SaaS: one set of tables, one migration run, cheap per tenant, and the database itself enforces isolation.
• Schema per tenant suits tens or a few hundred large tenants that need per-tenant restores or customisation. Past a few thousand schemas, migrations, backups and catalog size become real work.
• Database per tenant is for contracts that demand it: data residency, dedicated encryption keys, or enterprise customers paying for isolation.
• RLS only protects you if the app's database role is not the table owner, is not a superuser and has no BYPASSRLS. Add FORCE ROW LEVEL SECURITY anyway.
• Set the tenant with set_config('app.tenant_id', …, true) inside a transaction. A session-level SET on a pooled connection can carry one tenant's ID into another tenant's request.

The three models#

AWS's SaaS guidance calls these silo, bridge and pool. The names don't matter much; the trade-offs do.

There's no row in that table where one model wins everything, which is why this keeps coming up. The right answer depends on two numbers more than anything else: how many tenants you'll have, and how different from each other they are.

Schema per tenant: where it shines and where it hurts#

The idea is appealing. Each customer gets their own schema (tenant_acme.invoices, tenant_globex.invoices), the application sets search_path at the start of a request, and every query runs unchanged against the right tables. Forgetting a WHERE clause can't leak data, because the other tenants' tables aren't on the path.

Where it shines:

• Per-tenant operations are trivial. pg_dump --schema=tenant_acme exports one customer. Restoring one tenant to last Tuesday doesn't touch anyone else. Offboarding is DROP SCHEMA tenant_acme CASCADE.
• Per-tenant customisation. An enterprise customer can have an extra column or index without affecting the others.
• Per-tenant statistics. Each tenant's tables have their own planner statistics, so a tenant with 20 million rows and one with 200 get plans that suit them.

Where it hurts, and when:

• Migrations run once per schema. With 50 tenants that's a loop. With 3,000 it's a deployment that takes an hour, and the hard question is what happens when schema 1,742 fails. Some tenants are on the new version, some aren't, and your app has to work with both. You end up building migration orchestration that tracks a version per schema.
• The catalog grows with every tenant. Every table, index and sequence is a row in the system catalogs, multiplied by the tenant count. A 60-table app with 5,000 tenants is 300,000 tables plus their indexes. Each backend process caches the catalog entries it touches, so memory per connection climbs, pg_dump slows to a crawl, and tools that list tables struggle.
• Autovacuum has more work. It has to visit hundreds of thousands of small tables, and its workers can fall behind on the few large ones that matter.
• ORMs fight it. TypeORM and Prisma are designed around one schema known at startup. Making them switch per request usually means setting search_path by hand on each connection, which brings back the pooling problem below.
• Cross-tenant questions get expensive. "How many invoices did all tenants send last month?" becomes a query generated across thousands of schemas, or a separate analytics pipeline.

None of this is a hard limit. PostgreSQL doesn't stop you at any number of schemas. But past the low thousands, the operational cost tends to grow faster than the product, and I've seen more teams migrate away from schema per tenant than towards it.

Shared schema with tenant_id: designing it properly#

The shared model is simple: every tenant-owned table has a tenant_id, and every query filters on it. The design details are what make it safe and fast.

CREATE TABLE tenants (
  id         uuid PRIMARY KEY DEFAULT uuidv7(),
  name       text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE customers (
  tenant_id uuid NOT NULL REFERENCES tenants (id),
  id        uuid NOT NULL DEFAULT uuidv7(),
  email     text NOT NULL,
  PRIMARY KEY (tenant_id, id),
  UNIQUE (tenant_id, email)                   -- unique per tenant, not globally
);

CREATE TABLE invoices (
  tenant_id   uuid NOT NULL REFERENCES tenants (id),
  id          uuid NOT NULL DEFAULT uuidv7(),
  customer_id uuid NOT NULL,
  total_cents bigint NOT NULL,
  created_at  timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (tenant_id, id),
  FOREIGN KEY (tenant_id, customer_id) REFERENCES customers (tenant_id, id)
);

CREATE INDEX invoices_tenant_created_idx ON invoices (tenant_id, created_at DESC);

Four decisions in there are worth copying:

1. tenant_id on every tenant-owned table, including child tables like invoice lines. It's tempting to rely on the parent's tenant through a join. Don't: RLS policies and indexes are simplest when every table can answer "whose row is this?" on its own.
2. Composite foreign keys. FOREIGN KEY (tenant_id, customer_id) makes it impossible for an invoice in tenant A to point at a customer in tenant B, even through an application bug. With a plain customer_id foreign key, that bug is one wrong ID away.
3. Uniqueness scoped to the tenant. Two companies can both have a customer called billing@example.com. A global unique constraint would also leak information: tenant B's insert fails because tenant A already has that email.
4. tenant_id first in indexes. Almost every query filters by tenant, so it leads. The EXPLAIN ANALYZE post shows what happens to a list query without that.

uuidv7() is built in since PostgreSQL 18. Version 7 UUIDs are time-ordered, so new rows land at the end of the index instead of at random pages, which keeps inserts fast on big tables. On older versions, generate them in the app.

Row-level security: let the database enforce it#

A shared schema without RLS depends on every query, in every service, written by every developer for the next five years, remembering WHERE tenant_id = $1. It only takes one admin endpoint or one hand-written report to forget it.

Row-level security moves that rule into the database. Once a table has RLS enabled, PostgreSQL adds the policy's condition to every query on it, whoever wrote the query.

-- Roles: the owner runs migrations, the app only reads and writes data
CREATE ROLE app_owner LOGIN PASSWORD '…';
CREATE ROLE app_user  LOGIN PASSWORD '…' NOSUPERUSER NOBYPASSRLS;

-- Run as app_owner, which creates (and so owns) the tables
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;

-- Per tenant-owned table
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)
  WITH CHECK (tenant_id = current_setting('app.tenant_id', true)::uuid);

What each piece does:

• USING filters the rows a query can see, update or delete. Rows from other tenants simply don't exist for this query; an UPDATE on one affects zero rows.
• WITH CHECK validates rows being written. Inserting a row with another tenant's ID fails with new row violates row-level security policy. If you leave it out, PostgreSQL uses the USING expression for both, but I write it out so nobody has to remember that.
• current_setting('app.tenant_id', true): the true means "return NULL if it isn't set" instead of raising an error. tenant_id = NULL is never true, so a request that forgot to set a tenant sees nothing at all. It fails closed.
• FORCE ROW LEVEL SECURITY applies the policy to the table owner too.

The part that catches people: the PostgreSQL docs are explicit that superusers and roles with BYPASSRLS always bypass row security, and table owners do too unless the table is forced. If your app connects as the same user that ran the migrations and owns the tables, which is the default in most tutorials and in TypeOrmModule.forRoot() examples, RLS is switched on and doing nothing. Two roles, one for migrations and one for the app, are not optional.

On RDS, the master user isn't a true superuser but has rds_superuser, and it owns whatever it creates. Don't run the app as it.

Remember to add the policy to new tables. I add a CI check that fails the build when a table with a tenant_id column doesn't have RLS enabled and forced:

SELECT c.relname
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_attribute a ON a.attrelid = c.oid AND a.attname = 'tenant_id' AND NOT a.attisdropped
WHERE n.nspname = 'public'
  AND c.relkind IN ('r', 'p')
  AND NOT (c.relrowsecurity AND c.relforcerowsecurity);

Zero rows means every tenant table is protected.

Setting the tenant per request in NestJS#

The database side is done. Now the application has to tell PostgreSQL, for each request, which tenant it's acting for. The tenant must come from something the server trusts, such as the verified JWT or the session, never from a header or a body field the client controls.

Here's the shape I use with TypeORM and nestjs-cls, which keeps request-scoped values in Node's AsyncLocalStorage without making every provider request-scoped:

// app.module.ts
ClsModule.forRoot({
  global: true,
  middleware: {
    mount: true,
    setup: (cls, req) => {
      // set by your auth middleware after verifying the token
      cls.set('tenantId', req.auth?.tenantId);
    },
  },
}),

// database/tenant-db.service.ts
@Injectable()
export class TenantDb {
  constructor(
    private readonly dataSource: DataSource,
    private readonly cls: ClsService,
  ) {}

  /** Runs `work` in a transaction where RLS sees the current request's tenant. */
  run<T>(work: (manager: EntityManager) => Promise<T>): Promise<T> {
    const tenantId = this.cls.get<string>('tenantId');
    if (!tenantId) throw new ForbiddenException('No tenant in this request');

    return this.dataSource.transaction(async (manager) => {
      await manager.query(`SELECT set_config('app.tenant_id', $1, true)`, [tenantId]);
      return work(manager);
    });
  }
}

// invoices/invoices.repository.ts
@Injectable()
export class InvoicesRepository {
  constructor(private readonly db: TenantDb) {}

  latest(limit = 25) {
    // No tenant filter in the query: RLS adds it
    return this.db.run((m) =>
      m.getRepository(Invoice).find({ order: { createdAt: 'DESC' }, take: limit }),
    );
  }
}

I still add tenantId to queries where it helps the planner, for example on joins. RLS is the safety net, not an excuse to make the planner guess. If you already use @nestjs-cls/transactional for transactions, set the tenant in the same place you start them, so every transaction gets it.

The connection pooling trap#

This is the part I'd want someone to tell me before shipping.

There are two ways to set a runtime variable in PostgreSQL. SET app.tenant_id = '…' (or set_config(…, false)) sets it for the whole session, which is the database connection. SET LOCAL (or set_config(…, true)) sets it for the current transaction only, and PostgreSQL discards it at COMMIT or ROLLBACK.

Your app doesn't open a connection per request. TypeORM keeps a pool, and connections are reused by request after request. If you set the tenant at session level, the connection goes back to the pool still carrying tenant A's ID. The next request to borrow it, perhaps a background job that forgot to set its own tenant, runs as tenant A. RLS is working perfectly and is enforcing the wrong tenant.

With PgBouncer in transaction mode, it's worse: consecutive transactions from one client can land on different server connections, so a session-level setting might not even be there for your next query. PgBouncer's docs say that in transaction pooling, clients must not use session-based features, and that server_reset_query isn't run in that mode. Don't count on a reset query to clean up after you.

The rule that makes all of this safe: set the tenant with set_config(…, true) as the first statement of a transaction, and run every tenant query inside one. The TenantDb.run() helper above exists to make that the only easy path. The same applies to schema per tenant: use SET LOCAL search_path in a transaction, never SET search_path.

RLS performance#

The policy's condition is added to every query, so it needs an index to use, which is why tenant_id leads your indexes. current_setting() is a stable function, so PostgreSQL can use it in an index condition, where it's evaluated once at the start of the scan, just like a query parameter. In practice, on tables indexed this way, I've seen no meaningful overhead from the policy itself.

Two things to watch:

• Look at plans as app_user, not as a superuser. A superuser bypasses RLS, so EXPLAIN in your admin psql session shows a plan without the policy filter. Run SET ROLE app_user; and set the tenant first, or you're optimising a query the app never runs.
• Non-leakproof functions. PostgreSQL runs the policy check before conditions in your query that use functions not marked LEAKPROOF, to make sure they can't reveal hidden rows. Most built-in comparison operators are leakproof. If a plan with RLS looks worse than without, a function in your WHERE clause is often the reason.

Admin tools, background jobs and cross-tenant work#

Not everything runs inside one tenant:

• Background jobs carry the tenant in the job payload and call the same TenantDb.run(). A job without a tenant fails, which is what you want.
• Webhooks from a payment provider arrive with no tenant at all. Look the tenant up from the provider's own ID (the Stripe customer, for example) before opening the tenant transaction, and de-duplicate events as in Stripe webhooks that never double-charge.
• Internal admin and reporting that legitimately reads across tenants should use a separate role, such as app_admin with BYPASSRLS, on a separate connection pool, used only by a separate module (or a separate service). Keep its credentials out of the main app's config, so a bug in the customer-facing code can't reach for it.
• Migrations run as app_owner, never as the app.
• Exporting or deleting a tenant (for a GDPR request or a cancelled contract) is a COPY (SELECT … WHERE tenant_id = …) or a DELETE per table. With composite foreign keys you can use ON DELETE CASCADE from tenants and delete one row, but do it in batches for large tenants.

When one tenant outgrows the rest#

The shared model's weakness is noisy neighbours: one tenant's 40 million rows and heavy reports share CPU and cache with everyone else. You have options before you change models:

• Keep indexes leading with tenant_id, so a big tenant's data doesn't slow a small tenant's queries.
• Move a whale to its own database. The schema is identical, so a large tenant can be moved to a dedicated instance, with a routing table that says which database each tenant lives on. This hybrid is common: pooled for most, siloed for a few who pay for it.
• Partition by tenant (PARTITION BY HASH (tenant_id)) once tables reach hundreds of millions of rows and vacuum or index maintenance becomes a problem.
• Shard by tenant with Citus, which distributes tables by tenant_id across nodes. The composite keys above are exactly what it needs, which is another reason to design them in from day one.

My recommendation#

For a new B2B SaaS on PostgreSQL:

• Default to a shared schema with tenant_id and RLS. It's the cheapest to run, the easiest to migrate and report on, and with RLS forced and the right roles, isolation doesn't depend on every developer remembering a WHERE clause.
• Choose schema per tenant if you'll have a modest number of large tenants (say, under a few hundred) who need per-tenant restores, customisation or a clear isolation story for their security reviews.
• Choose database per tenant when a contract or regulation requires it, and price it into those contracts.
• Plan for a hybrid. Most successful SaaS products end up pooled for the long tail and siloed for a few big customers. Composite keys and a tenant routing table make that move possible later.

The checklist#

• Every tenant-owned table has a NOT NULL tenant_id
• Primary and foreign keys include tenant_id; unique constraints are scoped per tenant
• Indexes lead with tenant_id
• RLS is enabled and forced on every tenant table, checked in CI
• Policies use current_setting('app.tenant_id', true) so a missing tenant sees nothing
• The app connects as a role that doesn't own the tables, isn't a superuser and has no BYPASSRLS
• The tenant comes from the verified token or session, never from client input
• The tenant is set with set_config(…, true) inside a transaction, never at session level
• Plans are checked as the app role, with a tenant set
• Cross-tenant admin work uses a separate role, pool and module
• An integration test proves tenant A can't read, update or insert tenant B's rows

Need a hand?#

Designing multi-tenant data models, and moving existing apps to RLS without downtime, is work I do for SaaS teams. See my full-stack and backend engineering services, or email mhuzaifaa.dev@gmail.com with where your tenancy model is today.

FAQ#

What is the best multi-tenant architecture for PostgreSQL?#

For most B2B SaaS products, a shared schema where every table has a tenant_id column, protected by row-level security. It's the cheapest per tenant, needs one migration run, and scales to any number of tenants. Schema per tenant suits a smaller number of large tenants that need per-tenant restores or customisation, and database per tenant suits strict compliance requirements.

Is schema per tenant a good idea in PostgreSQL?#

It works well for tens or a few hundred tenants, and gives easy per-tenant backups, restores and deletion. With thousands of tenants, migrations have to run once per schema, the system catalogs grow with every table, and backups and connection memory suffer. Many teams that start with schema per tenant later move to a shared schema.

How does row-level security work for multi-tenancy?#

You enable row-level security on each tenant table and create a policy that compares the row's tenant_id with a setting the application provides, such as current_setting('app.tenant_id', true). PostgreSQL then adds that condition to every query on the table, so a query can only see and write the current tenant's rows, even if it forgets to filter.

Does row-level security slow down PostgreSQL?#

Not noticeably when tables have indexes that start with tenant_id. The policy adds a condition to each query, and because current_setting() is a stable function, the planner can use it in an index scan like a parameter. Check plans while connected as the application role, because superusers bypass RLS and see different plans.

Is RLS safe with PgBouncer or a connection pool?#

Yes, if you set the tenant per transaction with SET LOCAL or set_config('app.tenant_id', id, true) and run tenant queries inside that transaction. A session-level SET stays on the pooled connection after your request, and the next request that borrows it can run as the wrong tenant. PgBouncer's transaction mode doesn't support session-level state at all.

Why is my row-level security policy not working?#

Usually because the application connects as a superuser, a role with BYPASSRLS, or the role that owns the tables, all of which bypass RLS by default. Connect the app as a separate role that doesn't own the tables and run ALTER TABLE … FORCE ROW LEVEL SECURITY so the owner is subject to policies too.

Related reading#

• Building a scalable SaaS backend with NestJS and PostgreSQL
• How I made legacy queries 30% faster with EXPLAIN ANALYZE
• NestJS project structure for large SaaS apps
• Running database migrations safely in CI/CD
• AWS's guidance on multi-tenant PostgreSQL with RLS