Multi-Tenant Database Architectures: Shared Database vs Schema Isolation in Enterprise SaaS
Engineering multi-tenant SaaS data boundaries: implementing PostgreSQL Row-Level Security (RLS), Express tenant context middleware, and zero-leak query policies.
By Uttam Thapa · · SaaS
⚡ Executive Summary (TL;DR)
Every multi-tenant SaaS has to guarantee that tenant A can never read tenant B's rows, and the usual guarantee — "we always remember the
WHERE clause" — is a promise about human attention. This compares database-per-tenant, schema-per-tenant and a shared table,
then shows how PostgreSQL Row-Level Security moves the guarantee into the database engine, where forgetting is no longer
possible.
Figure 1: Many merchants, one database. The isolation boundary is a policy, not a server.
Three Models, Three Different Bills
The tenancy decision is made once and paid for continuously. It sets your migration process, your provisioning speed, your per-tenant cost floor, and how badly
a single mistake can go.
|
Database per tenant |
Schema per tenant |
Shared table + RLS |
| Isolation |
Physical |
Namespace |
Kernel-enforced policy |
| Migrations |
N runs, partial-failure states |
N runs, same problem |
One run |
| Provisioning |
Create, migrate, connect |
Create schema, migrate |
One INSERT |
| Scaling limit |
Operational sprawl |
Connection pool and catalog bloat |
Table size and index selectivity |
| Per-tenant restore |
Trivial |
Workable |
Hard — needs its own tooling |
Schema-per-tenant is the option that looks like a compromise and behaves like the worst of both. Each schema multiplies the catalog, and connection pools do not
share cleanly across them — so you inherit the migration cost of separation without its isolation benefit. It suits tens of tenants, not thousands.
Row-Level Security: Moving the Guarantee Into the Engine
With a shared table, the isolation rule normally lives in application code. RLS relocates it into PostgreSQL itself, where every statement is filtered before it
returns a row — including statements typed by hand during an incident.
-- 1. Turn the policy engine on for this table.
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
-- 2. Define what a tenant may see. current_setting reads a per-session variable.
CREATE POLICY tenant_isolation_policy ON orders
FOR ALL
USING (tenant_id = current_setting('app.current_tenant_id', true));
-- 3. Force it to apply to the table owner too — otherwise the role your
-- application connects as may bypass every policy you just wrote.
ALTER TABLE orders FORCE ROW LEVEL SECURITY;
Step three is the one that is skipped most often. By default the table owner is exempt from RLS, so a policy can be enabled, tested against a restricted role,
and be entirely inert for the role the application actually uses.
Setting the Tenant on the Connection
The policy reads a session variable, so something has to set it — once per request, before any query runs, and inside the same transaction.
// Set the tenant for this transaction only. set_config's third argument
// scopes it to the transaction, so a pooled connection cannot leak the value
// into whichever request borrows it next.
await tx.$executeRaw`SELECT set_config('app.current_tenant_id', ${tenantId}, true)`;
const orders = await tx.order.findMany(); // RLS applies the filter
🚨 Connection pooling is where RLS goes wrong
A pooled connection outlives the request that used it. Set the tenant with session scope rather than transaction scope and the next request to borrow that
connection inherits the previous tenant's identity — a cross-tenant leak caused by the very mechanism meant to prevent one. Always pass
true as the third argument to set_config, and always set it inside the transaction that uses it.
Defence in Depth, Not Instead Of
RLS is a safety net, and the value of a net is that you do not aim for it. Keep scoping queries in the application: pass the resolved tenant into every
repository function so an omission is a type error, and let RLS catch the paths that type checking cannot reach — raw SQL, migrations, admin scripts, and the
psql session someone opens at 2am.
1. Request boundary
Resolve the tenant once from the session or subdomain. Never read it from a request body.
2. Data access layer
Every repository takes the tenant as a required argument, so omitting it fails to compile.
3. Database policy
RLS filters anything the first two layers never saw, including ad-hoc queries.
What RLS Costs
The policy predicate is appended to every query, so it must be indexable — which in practice means tenant_id should lead your composite indexes.
Get that right and the overhead is small; get it wrong and every query degrades to a scan filtered by policy, which is the worst of both worlds.
The other real cost is testability. Write an automated test that connects as the application role, sets tenant A, and asserts that tenant B's rows are
invisible. Without it, nothing tells you the day someone adds a table and forgets to enable the policy on it.
✅ Key takeaways
- ✓Shared table plus RLS suits many small tenants. One migration, instant provisioning, cost that tracks usage.
- ✓Schema-per-tenant is the awkward middle. Separation's migration cost without its isolation.
- ✓Remember
FORCE ROW LEVEL SECURITY. Table owners bypass policies by default.
- ✓Scope the tenant to the transaction. Session scope plus pooling is a cross-tenant leak.
- ✓Lead your indexes with
tenant_id. The policy predicate runs on every query and must be indexable.
- ✓Test the boundary automatically. A new table with no policy is otherwise invisible until it matters.
For how this looks in a live product, see building SellerZonee, and
scaling PostgreSQL to 10M rows for the indexing that keeps a shared table fast.
Frequently asked questions
Shared database or schema per tenant?
Shared database with a tenant column scales cheaply to many small tenants and keeps migrations to one run. Schema per tenant gives stronger isolation and per-tenant operations at the cost of migration complexity that grows with tenant count.
Does PostgreSQL row-level security replace application-level checks?
It complements them. RLS is a safety net that also protects raw queries run by hand during an incident, but the application should still scope every query so a policy misconfiguration is not the only thing standing between tenants.
How do you migrate from a shared database to isolated schemas?
Only if a specific tenant's requirements demand it, and by moving that tenant rather than the whole platform. Design the data access layer so tenant resolution is one function, and moving a single tenant later stays tractable.
Home · Projects · Blog · Services · Résumé · Contact