Multi-tenancy decisions are made in week two and paid for in year three. They are hard to reverse, because reversing them means migrating customer data.
Shared schema vs schema-per-tenant vs database-per-tenant
Shared schema. All tenants in the same tables, separated by a tenant_id column.
Cheapest to run and simplest to operate — one database, one migration, one connection pool. Scales to thousands of tenants comfortably. The risk is concentrated in one place: every query must filter by tenant, and one that does not is a cross-tenant data leak.
Schema-per-tenant. One Postgres schema per tenant, identical tables in each.
Stronger isolation, per-tenant backup and restore, easier to answer "delete everything about us". The cost is migrations: a schema change runs N times, and at a few thousand tenants that becomes an operation with its own failure modes. Connection pooling gets more complex, and Postgres itself starts to strain with very large numbers of schemas.
Database-per-tenant. Complete separation.
The strongest isolation, straightforward compliance story, per-tenant tuning and restore, no noisy-neighbour risk. Also the most expensive and operationally heaviest. Appropriate for enterprise contracts, high-value customers, or regulated data.
The pragmatic answer for most SaaS: start shared-schema, and design so that promoting a large or regulated customer to a dedicated database later is possible. Hybrid is common and entirely respectable.
Row-level security
If you go shared-schema, Postgres RLS is the difference between "we are careful" and "the database enforces it".
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON projects
USING (tenant_id = current_setting('app.tenant_id')::uuid);
With the tenant set per transaction, a query that forgets its WHERE tenant_id = … returns nothing instead of everything.
That is the entire argument for RLS: application-level filtering depends on every developer, every query, forever. RLS depends on the database. The first assumption fails eventually — through a raw SQL query, a new join, a reporting endpoint written in a hurry.
Note the exceptions: BYPASSRLS roles and table owners skip policies. Run the application as a role that cannot bypass, and keep migrations on a separate privileged role.
Plumbing tenant context through the app
However you isolate, tenant context must be set once per request, as early as possible, and be impossible to forget.
Resolve the tenant from the authenticated session — never from a request body or a query parameter the client controls. Then set it for the transaction:
await prisma.$transaction(async (tx) => {
await tx.$executeRaw`SELECT set_config('app.tenant_id', ${tenantId}, true)`;
return work(tx);
});
The true scopes the setting to the transaction, which matters with pooled connections — otherwise the value leaks to whoever gets that connection next. That is a genuinely dangerous bug class and it is easy to introduce.
Wrap it in middleware or a helper so no route can do a database call without a tenant.
Migrations at scale
Shared schema keeps this simple: one migration, applied once.
Schema-per-tenant does not. You need a runner that iterates tenants, applies in batches, tracks which succeeded, and can resume after a failure — because with a few thousand schemas, something will fail partway.
Regardless of model, adopt expand/contract for anything touching live tables:
Add the new column, nullable
Backfill in batches
Write to both old and new
Switch reads
Stop writing the old
Drop it, later
More steps, no downtime, and each step is reversible. Renaming a column in one migration is a very short-lived kind of convenience.
Noisy neighbours
In shared infrastructure, one tenant can degrade everyone. The usual suspects:
A tenant with far more data. Queries fine on 1,000 rows time out on 10 million. Index on (tenant_id, …) with tenant first, and test with a deliberately oversized tenant in staging.
Runaway API usage. Rate limit per tenant, not globally, so one integration cannot consume the budget.
Expensive background jobs. A large tenant's nightly export blocking the queue. Use per-tenant queues or fair scheduling.
Unbounded queries. Always paginate. "Fetch all projects" is fine until a tenant has 50,000.
Monitor per tenant, not just in aggregate. Averages hide the customer who is about to page you.
Choosing for your stage
Pre-product-market-fit: shared schema, RLS, one database. Optimising isolation before you have customers is optimising the wrong thing.
Growing, mid-market: shared schema with RLS, plus per-tenant rate limits and monitoring. Start tracking your largest tenants.
Enterprise deals appear: hybrid. Shared for the long tail, dedicated databases for customers who contractually require it. Your architecture should already allow this — which is the one forward-looking decision worth making early.
The decision to make now: make tenant context explicit and enforced from day one. Whether that is RLS or a repository layer nothing can bypass, build it before the codebase has two hundred queries. Retrofitting isolation is where the genuinely painful migrations come from.