Multi-Tenant SaaS Data Isolation and Scaling in PostgreSQL

Scope of this article

This is the database-layer companion to our broader multi-tenant SaaS architecture guide, which covers tenant onboarding, identity, billing, feature flags and the overall application design. We will not repeat that here. This deep dive assumes you have chosen PostgreSQL and want to know exactly how to isolate tenants inside it, what breaks at scale, and what we do about it. It is one of our engineering deep dives.

Three isolation models compared

Schema-per-tenant looks like a comfortable middle ground and usually is not: it carries most of the operational cost of database-per-tenant (fan-out migrations, per-schema drift, huge catalogs slowing tooling) without delivering physical isolation. Our common end state is a hybrid: a pooled cluster using RLS for the long tail, plus dedicated databases for the handful of tenants whose size, compliance needs or contracts justify them, with a tenant directory that routes each request to the right place.

Shared schema with Row-Level Security

Every tenant-owned table gets a non-null tenant_id, a policy, and FORCE ROW LEVEL SECURITY so the table owner is not silently exempt:

-- roles: migrations run as app_owner; the application connects as app_user
CREATE ROLE app_user LOGIN NOBYPASSRLS;

CREATE TABLE invoices (
  tenant_id   uuid        NOT NULL,
  id          uuid        NOT NULL,
  customer_id uuid        NOT NULL,
  total_minor bigint      NOT NULL,
  status      text        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)
);

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

CREATE POLICY tenant_isolation ON invoices
  USING      (tenant_id = (SELECT current_setting('app.tenant_id')::uuid))
  WITH CHECK (tenant_id = (SELECT current_setting('app.tenant_id')::uuid));

GRANT SELECT, INSERT, UPDATE, DELETE ON invoices TO app_user;

And per request, inside the transaction that runs the queries:

BEGIN;
SET LOCAL app.tenant_id = '6f1c2a3e-9d7b-4f0a-8c21-3b5e7d9a1f40';
-- or, parameterised from application code:
-- SELECT set_config('app.tenant_id', $1, true);

SELECT id, total_minor, status FROM invoices WHERE status = 'overdue';
COMMIT;

Why each detail matters:

• USING and WITH CHECK: the first filters what you can read, update and delete; the second stops you inserting or moving a row into another tenant.
• Fail closed. If the setting was never defined in the session, current_setting raises an error. If it was set locally in an earlier transaction on the same connection, it reverts to an empty string, and the ::uuid cast raises an error. Either way a missing tenant context produces a loud failure, not a cross-tenant result. We deliberately do not use the missing_ok form here.
• (SELECT current_setting(...)) lets the planner evaluate the setting once per statement as an initPlan instead of per row, which matters on large scans.
• Composite foreign keys including tenant_id. Referential integrity checks bypass RLS, so a plain customer_id foreign key would happily accept a customer from another tenant.
• Views run with the view owner's permissions unless created with security_invoker = true (PostgreSQL 15+). A view owned by the table owner can quietly bypass your policies.
• SECURITY DEFINER functions likewise run as their owner; we avoid them for tenant data or audit them explicitly.
• The application still scopes queries by tenant. RLS is the safety net that catches the bug; it is not a reason to write unscoped queries.

Background jobs and admin tools set the tenant context exactly like web requests. Genuine cross-tenant work (billing aggregation, platform analytics) uses a separate, audited role, ideally against a read replica or warehouse rather than the primary.

Connection pooling pitfalls with RLS

PostgreSQL connections are expensive, so almost every SaaS at scale runs a pooler (PgBouncer, a managed proxy, or an application-side pool). In transaction pooling mode a client holds a server connection only for the duration of a transaction. That interacts with RLS in ways that cause real incidents:

• Session-level SET leaks. Covered in the diagram above. Always SET LOCAL or set_config(..., true), inside an explicit transaction.
• Autocommit ORMs. If the ORM runs SET LOCAL in one implicit transaction and the query in another, the query runs without context (and, with our fail-closed policy, errors). Wrap each unit of work in an explicit transaction and set the tenant as its first statement; most ORMs support a per-transaction hook for this.
• Prepared statements. Older PgBouncer versions did not support protocol-level prepared statements in transaction mode. PgBouncer 1.21+ can track them (max_prepared_statements); otherwise disable server-side prepares in the driver.
• Session features do not work in transaction mode: LISTEN/NOTIFY, session advisory locks, temporary tables that outlive a transaction, WITH HOLD cursors. Route those workloads through a session-mode pool or a direct connection.
• Pool sizing per role. Keep separate pools for the web tier, background workers and migrations so a job storm cannot starve interactive traffic.

Indexing and keys by tenant

• Lead with tenant_id in primary keys and most secondary indexes: (tenant_id, created_at DESC), (tenant_id, status). Nearly every query filters by tenant, and it keeps each tenant's rows clustered in index order.
• Scope uniqueness: UNIQUE (tenant_id, email), not UNIQUE (email), unless the value is genuinely global.
• Use globally unique IDs (UUIDv7 generated in the application, or uuidv7() on PostgreSQL 18) rather than per-table sequences. Time-ordered UUIDs index well, and they make moving or restoring a tenant into another database collision-free.
• Watch planner skew. When one tenant owns a large share of a table, a plan that is ideal for small tenants can be terrible for the large one. Extended statistics, partial indexes for the largest tenants, or partitioning (below) help; so does checking EXPLAIN with the big tenant's ID, not a test tenant's.

Noisy neighbours

In a pooled database one tenant's export job can degrade everyone. Our layered defences:

1. Timeouts per role: ALTER ROLE app_user SET statement_timeout = '5s' for interactive traffic, a longer one for the worker role, plus idle_in_transaction_session_timeout.
2. Per-tenant rate limits and concurrency caps at the API and job-queue level, so a single tenant cannot hold most of the pool.
3. Attribution: tag queries with tenant and endpoint (via application_name or a SQL comment) so pg_stat_statements and logs show who is expensive.
4. Offload heavy reads (reports, exports, search) to read replicas or an analytics store.
5. Graduate the tenant to its own partition or database when the pattern is structural rather than occasional.

Partitioning

We do not partition on day one. We partition specific tables when they become large enough that vacuum, index maintenance or query plans suffer. Two patterns fit multi-tenancy:

• Hash partitioning by tenant_id spreads tenants evenly across a fixed number of partitions (for example 16 or 32), keeping each partition's indexes and vacuum work smaller.
• List partitioning for large tenants: one dedicated partition per very large tenant plus a default or hashed partition for everyone else. This also makes the tenant easier to detach and move later.

Caveats: primary keys and unique constraints on a partitioned table must include the partition key (another reason to lead keys with tenant_id); thousands of partitions add planning overhead; and RLS policies on the parent apply to queries through the parent, but a query that targets a partition directly uses that partition's own policies, so we revoke direct access to partitions from the application role. Time-based sub-partitioning (tenant then month) is worth it for append-heavy event or audit tables where old data is dropped by detaching partitions.

Moving a large tenant out

Sooner or later a tenant needs its own database: size, a data-residency requirement, or an enterprise contract. Because every row carries tenant_id and IDs are global, logical replication with row filters (PostgreSQL 15+) makes this an online operation:

-- on the shared (source) cluster
CREATE PUBLICATION move_tenant_acme
  FOR TABLE customers, invoices, invoice_lines
  WHERE (tenant_id = '6f1c2a3e-9d7b-4f0a-8c21-3b5e7d9a1f40');

-- on the dedicated (target) cluster, after applying the same schema
CREATE SUBSCRIPTION move_tenant_acme
  CONNECTION 'host=shared-primary dbname=app user=replicator'
  PUBLICATION move_tenant_acme;

The sequence we follow:

1. Apply the schema to the target; confirm every tenant table has a replica identity that includes tenant_id (a primary key of (tenant_id, id) satisfies the row-filter requirement for updates and deletes).
2. Create the publication and subscription; wait for initial copy and for replication lag to approach zero.
3. Verify with per-table row counts and checksums for that tenant on both sides.
4. Put the tenant in a brief maintenance or read-only mode, wait for lag to reach zero, and flip the tenant directory entry to the new database.
5. Resume writes, monitor, keep the source rows for a rollback window, then delete them in batches.

Logical replication does not carry schema changes or sequence values, so freeze migrations during the move. How the dedicated database fits the wider infrastructure is covered in our AWS reference architecture for high-traffic SaaS.

Backups and per-tenant restore

Physical backups and point-in-time recovery work at cluster level. That is the right primary mechanism, but customers ask for "restore our account to yesterday 14:00", not "roll back the whole platform". Our runbook:

• Restore a PITR copy to a separate, isolated instance at the requested time.
• Extract the tenant's rows with tenant-filtered exports (COPY (SELECT … WHERE tenant_id = …) TO …) in dependency order.
• Merge into production through a reviewed script: replace, or restore only deleted records, depending on what the customer actually needs. Global IDs keep this collision-free.
• Rehearse it quarterly. An untested per-tenant restore is a hope, not a capability.

Also plan for the reverse: tenant offboarding and erasure requests. Deleting a tenant from the live database is straightforward with tenant_id everywhere; data in backups ages out under a documented retention policy, which should be stated in your contracts and privacy notices. Our security and compliance page describes how we approach these controls in delivery.

Migrations at scale

• Expand, migrate, contract. Add the new column or table, deploy code that writes both, backfill, switch reads, and only then drop the old structure in a later release. Every step is backward compatible with the running code.
• Short locks only. Set lock_timeout (a few seconds) on migration sessions so a blocked ALTER TABLE fails and retries instead of queueing every query behind it. Use CREATE INDEX CONCURRENTLY; add constraints as NOT VALID, then VALIDATE CONSTRAINT separately.
• Batch backfills by tenant and primary-key range, with pauses, so replication lag and vacuum keep up.
• RLS is part of the schema. A new table without a policy is a vulnerability; a CI check fails the build if any table with a tenant_id column lacks RLS enabled and forced.
• Fleet migrations for dedicated databases run through an orchestrator that tracks per-database version, applies in waves (internal, small, large tenants), and halts on the first failure. Application code must tolerate the fleet being on two schema versions at once.

-- CI guard: tenant tables without forced RLS
SELECT c.relname
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public' AND c.relkind IN ('r','p')
  AND EXISTS (SELECT 1 FROM pg_attribute a
              WHERE a.attrelid = c.oid AND a.attname = 'tenant_id' AND NOT a.attisdropped)
  AND NOT (c.relrowsecurity AND c.relforcerowsecurity);

Proving isolation

We treat tenant isolation as a tested property, not an assumption. The test suite creates two tenants with overlapping data, then for every repository method and API endpoint asserts that tenant A's context can never read, update, delete or reference tenant B's rows, including through search, exports, background jobs and file storage keys. Combined with the CI guard above, this catches the classic regressions: a new table without a policy, a raw query that bypasses the repository layer, a job that forgets to set context. The same pattern extends to derived stores; retrieval indexes for AI features, for example, need tenant filters too, as described in our production RAG architecture deep dive.

Use our SaaS architecture checklist to review your own design against these points, or see how we build full products on our SaaS application development page.

Frequently asked questions

Is Row-Level Security enough on its own to isolate tenants?

It is a strong second line, not the only line. We still scope queries by tenant in application code, connect as a non-owner role without BYPASSRLS, force RLS on every tenant table, use composite foreign keys that include tenant_id, and run automated cross-tenant tests in CI.

Does Row-Level Security slow PostgreSQL down?

A simple equality policy on tenant_id costs very little when indexes lead with tenant_id and the setting is read once per statement. Problems usually come from complex policies with joins or function calls per row, which we avoid.

Can we use RLS with PgBouncer?

Yes, in transaction pooling mode, provided the tenant is set with SET LOCAL or set_config with is_local true inside the same transaction as the queries. A plain session-level SET leaks the tenant context to whichever client gets that server connection next.

When should we move to schema-per-tenant or database-per-tenant?

Schema-per-tenant rarely wins for us because it multiplies migration and catalog overhead. Database-per-tenant makes sense for a small number of large or regulated tenants that need dedicated resources, their own region, or contractual physical separation. Many SaaS products end up hybrid: pooled for most tenants, dedicated for a few.

How do we restore one tenant without rolling back everyone?

Restore a point-in-time copy of the cluster to a separate instance, extract that tenant's rows with tenant-filtered exports, and merge them back through a controlled script. Globally unique IDs such as UUIDs make this far safer than per-table sequences.