Multi-Tenant SaaS on Postgres: RLS vs Schema-per-Tenant
The multi-tenancy choice is the single architectural decision that follows your SaaS forever. We've shipped both Row Level Security (RLS) and schema-per-tenant approaches and helped customers migrate between them. Here's what we've learned, with numbers.
Key takeaways
- RLS (shared schema, tenant_id column on every table) is simpler operationally and scales better at small/mid customer counts.
- Schema-per-tenant isolates data better, simplifies backups and migrations per customer, and wins for highly regulated or large-customer scenarios.
- A third option, database-per-tenant, is overkill for most and underrated for the cases it does fit.
- Don't switch mid-flight unless you have to. The migration costs are real.
Why this matters
Get this wrong and you'll either spend years patching the architecture or fundamentally migrate the database, both expensive. Get it right and the rest of your engineering is freed up to focus on product.
The three architectures, in one paragraph each
RLS (Row-Level Security)
One database, one schema. Every table has a tenant_id column. Postgres RLS policies enforce that every query only returns rows where tenant_id = current_setting('app.tenant_id'). Your app sets the tenant ID on every connection.
Schema-per-tenant
One database, multiple schemas, tenant_alpha, tenant_beta, tenant_gamma. Each tenant's tables live in their own schema. App routes queries to the right schema by setting search_path per connection.
Database-per-tenant
One database per tenant. Each customer gets their own Postgres instance (or logical database within a cluster). Highest isolation, highest operational cost.
Operational trade-offs
| Concern | RLS | Schema-per-tenant | DB-per-tenant |
|---|---|---|---|
| Migration ease | Easy (one schema) | Hard (run per tenant) | Hard (run per tenant) |
| Backup granularity | Coarse | Per tenant | Per tenant |
| Per-tenant data export | Hard | Easy | Trivial |
| Noisy-neighbor risk | High | Medium | None |
| Operational complexity | Low | Medium | High |
| Cost at 1000 tenants | Low | Medium | High |
| Compliance / "data isolation" story | Weakest | Strong | Strongest |
Real benchmarks (our internal load test)
We ran an identical workload on both, 100 tenants, 10M rows total, 50 concurrent users. The query was a typical SELECT with a tenant filter + a join.
| Metric | RLS | Schema-per-tenant |
|---|---|---|
| P50 query latency | 12ms | 11ms |
| P99 query latency | 84ms | 47ms |
| Index size | 2.4GB | 1.9GB |
| Backup time (full) | 18min | 18min |
| Backup time (single tenant) | N/A | 14sec |
| Migration time (alter column) | 22sec | 11min (loop) |
The big wins for RLS: simpler migrations. The big wins for schema-per-tenant: per-tenant operations (backup, export, restore).
When RLS is right
- You have hundreds to low-thousands of tenants
- Tenants are similar in shape and size (no enterprise outlier crushing the cluster)
- Compliance does not require physical isolation
- You're early-stage and operational simplicity is a multiplier
When schema-per-tenant is right
- You have tens to hundreds of tenants, growing slowly
- Some tenants are very large (single tenant > 10% of total data)
- Per-tenant restores and exports are a frequent operation
- Compliance frameworks want named-schema isolation
When database-per-tenant is right
- Enterprise customers explicitly demand it (sometimes a contract clause)
- Regulatory requirements force physical isolation (rare, usually schema is enough)
- A single tenant's data volume justifies its own cluster
Common pitfalls
The most common is "we'll start with RLS and switch later." Switching is a project, typically 3-6 months end-to-end. Plan for the architecture you'll need at 100x scale, not 10x.
The second is RLS without proper testing of the policies. A single policy oversight = a tenant-data leak. Build automated tests that try to access cross-tenant data and confirm they fail.
What we recommend
For most B2B SaaS in early or mid stage: start with RLS, write rigorous policy tests, plan for schema-per-tenant only if a specific customer or compliance need forces it. For mid-to-late-stage with clear enterprise tier requirements: schema-per-tenant from day one if you can. For consumer SaaS: RLS, almost always.
FAQs
Can we mix? Yes, RLS for the free/standard tier, schema-per-tenant for the enterprise tier. It's complexity, but it's a sensible scaling pattern.
What about Postgres extensions like pg_tenant? Useful but immature; we use plain RLS for production-critical paths.
Does RLS impact performance? Mildly, typically 5-10% overhead on indexed queries. Negligible if your indexes include tenant_id.
How do we test RLS policies? Integration tests that explicitly try cross-tenant access and assert they fail.
Talk to Techpuvi about complex web applications. We've shipped both architectures and migrated between them.