Multi-Tenant Database Strategies: Row-Level Security vs. Schema-per-Tenant

Every SaaS application starts with the same quietly terrifying decision: how do we partition customer data so that tenant A can never see tenant B? The database schema you choose in week one becomes the constraint you architect around in year three, and retrofitting a new tenancy model into a live product with real customers is about as fun as migrating sharks in open water.

The good news: PostgreSQL gives you a spectrum of options rather than a binary choice. In this article I want to lay out the three realistic strategies — shared schema, schema-per-tenant, and the database-per-tenant variant — and then argue that most teams should land on shared schema with row-level security (RLS) plus a naming convention, unless they have hard per-tenant isolation requirements.

Why tenancy is a schema problem, not a code problem

When people say "our app is multi-tenant," they usually mean the business layer is: every row has a tenant_id, and every query filters on it. That works right up until the moment a developer forgets the filter, a join drops a tenant condition, or a support script runs UPDATE emails SET read = true and touches thirty customers at once.

The core insight is this: tenancy is a data concern, so it should be enforced where the data lives. If your boundary is "we promise to remember to add a WHERE clause," you are not multi-tenant — you're hopeful. RLS moves that promise from application discipline into the database itself.

Strategy one: shared schema with a tenant_id column

This is the default for a reason. Every table gets a tenant_id foreign key, and application queries filter by it. It's simple, it scales, you can build features across tenants trivially, and analytics queries are easy because all data lives in one place.

The classic failure mode is a missing predicate. Let's fix that:

-- RLS: policy the whole table, not just the query
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
 
CREATE POLICY tenant_isolation ON projects
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

The application sets the tenant context once per connection in a transaction:

import { PrismaClient } from "@prisma/client";
 
export const prisma = new PrismaClient();
 
export async function withTenant<T>(
  tenantId: string,
  fn: () => Promise<T>
): Promise<T> {
  return prisma.$transaction(async (tx) => {
    await tx.$executeRaw`SELECT set_config('app.tenant_id', ${tenantId}, true)`;
    return fn();
  });
}

With set_config(..., true) the setting is scoped to the transaction. If your team is using a connection pool (and you should be), the setting can't leak between tenants because it's transaction-local.

Strategy two: schema-per-tenant

Here every customer gets their own PostgreSQL schema — tenant_abc — containing the full set of tables. The application rewrites the search path per request:

ALTER ROLE app_user SET search_path TO tenant_abc, public;

In Prisma you can't cleanly switch schemas per request with migrations the same way you can with raw SQL, which is one of the reasons a lot of ORM-first teams avoid this model. The payoff is brisk: deleting a tenant is dropping a schema, backups are per-tenant, and a runaway customer can't trigger a seq_scan on everyone else's data.

The price is operational. You now run migrations across N schemas instead of one, each schema is a schema in your migration bookkeeping, and cross-tenant reporting requires either a shared analytics schema fed by triggers or a data warehouse. Most teams underestimate schema sprawl more than they overestimate it.

Strategy three: database-per-tenant

The most extreme option: one physical database per tenant. It buys hard isolation, independent performance tuning, and per-tenant backup/restore — genuinely valuable for a regulated vertical like healthcare or fintech.

But your database connection story becomes a hard problem: a thousand tenants means a thousand connection pools, and connection pooling tools like PgBouncer have to be told about every database. If you take this route, do it deliberately: provision databases programmatically, monitor max_connections, and treat "create tenant" as a Terraform-style operation, not a manual one. This is almost never the right default for a bootstrap-stage SaaS.

The comparison most people skip: analytics

The strategy table usually compares cost to build, but your second year is dominated by cost to report.

Notice the asymmetry: the only place schema-per-tenant or database-per-tenant wins on the table above is isolation strength and per-tenant backup. If your compliance obligations don't require byte-level isolation, you're paying a large operational tax for a property your customers can't observe.

The hybrid that works in practice

Real teams converge on this: shared schema with RLS for all transactional data, plus a dedicated analytics schema (or warehouse) built from a nightly read replica. The replica avoids putting read load and report queries on the transactional path, and because the shared schema has a tenant_id everywhere, building the analytics pipeline is a single materialized view per report rather than a shard-by-shard exercise.

CREATE MATERIALIZED VIEW tenant_health_daily AS
SELECT
  tenant_id,
  date_trunc('day', created_at)  AS day,
  count(*)                       AS events,
  count(DISTINCT user_id)        AS active_users
FROM events
WHERE created_at > now() - interval '90 days'
GROUP BY 1, 2;
 
CREATE UNIQUE INDEX ON tenant_health_daily (tenant_id, day);

Run this on a dedicated replica so REFRESH MATERIALIZED VIEW can't stall writes on the primary, and you get cross-tenant reporting without touching the hot path.

A checklist for deciding

• Your security model is "logical isolation is acceptable" → shared schema + RLS.
• You have per-tenant data retention or compliance rules → schema-per-tenant, maybe database-per-tenant.
• Cross-tenant analytics is a first-class product feature → shared schema is dramatically easier.
• Your ORM must support the model day one → shared schema with Prisma + RLS is the most supported path.
• A tenant can legitimately exceed a hosting plan's row-count limits → you'll be migrating to schema-per-tenant later anyway; plan the migration seam (a tenant.plan column) early.

The real takeaway: tenancy isolation belongs in the database, and for the overwhelming majority of teams that means shared schema with row-level security, an analytics replica, and a migration seam ready in case one customer grows past the shared model. Everything else is optimizing a property the market won't pay for until it's too late to change cheaply.