The two main approaches to multi-tenancy in SaaS products — row-level security and schema-per-tenant — with an honest comparison of when each makes sense, performance characteristics, migration costs, and the decision framework senior teams actually use.
Multi-tenancy is the architectural decision that's hardest to undo. Make it wrong at seed, spend 3 months migrating at Series A while competitors ship features. Here's how to make it right the first time.
The good news: there's no universally correct answer. The decision depends on your compliance requirements, team size, customer profile, and growth stage. This guide gives you the framework to make the right call for your specific situation — not a generic recommendation that fits nobody.
Multi-tenancy means multiple customers (tenants) share the same infrastructure and application code while their data remains isolated from each other. Your application is one codebase deployed once. Acme Corp's data should never be visible to Beta Inc., even if both are in the same database.
The isolation requirement is what makes multi-tenancy hard. Getting isolation right — completely, without leaks, under all failure modes — is the core engineering challenge. The two main approaches differ in where the isolation boundary is drawn.
Every tenant's data lives in the same tables. Isolation is enforced by a tenant_id column on every table, and Postgres row-level security (RLS) policies that prevent one tenant from reading another's rows.
Every table gets a tenant_id column. A tenants table holds one row per customer. Foreign keys reference tenants.id. When a request comes in, the application sets a session variable (SET LOCAL app.current_tenant = 'abc123') before running any queries. Postgres RLS policies read that session variable and automatically filter every query to only return rows matching the current tenant.
Critically, RLS operates at the database level. The application doesn't explicitly add WHERE tenant_id = ? to every query — Postgres adds it automatically based on the policy. A bug in application code that accidentally omits the filter doesn't cause a data leak; the database enforces it regardless.
With Drizzle ORM and PostgreSQL, the setup involves defining RLS policies via raw SQL migrations, then configuring the connection pool to set tenant context at the start of each transaction. The Drizzle queries themselves remain clean — no tenant filtering sprinkled through application code.
-- Enable RLS on a table
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
-- Policy: tenants can only see their own rows
CREATE POLICY tenant_isolation ON projects
USING (tenant_id = current_setting('app.current_tenant')::uuid);Every subsequent query against projects automatically respects this policy. No application-level filtering required.
Most B2B SaaS products under $5M ARR should use row-level security. If your customers are small-to-medium businesses, compliance requirements are standard (GDPR, basic data security), and you don't have contractual data isolation requirements, RLS gives you speed of development and operational simplicity that schema-per-tenant can't match at this stage.
Each tenant gets their own Postgres schema (or, in extreme cases, their own database). The tables are identical across schemas, but the data is physically separate. Tenant acme has acme.projects, acme.users, acme.invoices. Tenant beta has beta.projects, beta.users, beta.invoices.
On tenant creation, the application runs a provisioning script that creates a new Postgres schema, runs all migrations in that schema, and seeds any default data. At request time, the application sets search_path = tenant_schema so all queries target the correct tenant's tables.
The isolation is complete. There are no shared tables. A bug in application code cannot reach another tenant's data because the other tenant's tables don't exist in the current search path.
DROP SCHEMA acme CASCADE removes all tenant data atomically. No DELETE FROM every_table WHERE tenant_id = ?.Healthcare (HIPAA), financial services, legal tech, or any SaaS where enterprise customers make data isolation a contractual requirement. If your sales cycle includes a security questionnaire that asks "is our data physically isolated from other customers," schema-per-tenant lets you answer yes without caveats.
Also valid: when you have a small number of high-value enterprise accounts (under 200 tenants) and the operational complexity is manageable.
The most practical architecture for SaaS products aiming at both SMB and enterprise:
Start with row-level security for all tenants. When a customer is large enough or has compliance requirements that RLS doesn't satisfy, provision them a dedicated schema (or database) and route their traffic there.
The application needs a tenant registry that maps tenant IDs to their database configuration. Tenant abc123 (SMB) connects to the shared pool with RLS. Tenant ent456 (enterprise healthcare) connects to a dedicated schema. The application code is identical — only the connection parameters differ.
This gives you fast development and low cost for your long tail of smaller customers, while offering true isolation as an enterprise upsell. It's also a natural migration path: you don't need to rebuild anything when a customer upgrades to the isolated tier.
The complexity is in the tenant registry and the connection routing layer. It needs to be reliable, because routing a request to the wrong tenant's connection is a data breach. This is infrastructure you build once and maintain carefully.
For row-level security, the setup involves three things: the RLS policies in the database, a connection pool that supports session-level configuration (Neon, Supabase, or PgBouncer in session mode), and a middleware layer that sets the tenant context at the start of each request.
In Next.js App Router, this typically lives in middleware.ts (for reading the tenant from the request — subdomain, header, or JWT claim) and a database utility that wraps every database operation in a transaction that sets app.current_tenant first.
For schema-per-tenant, the Drizzle connection receives a different search_path per request. Drizzle itself doesn't need to know about schemas — you pass the connection string and the schema search path handles the routing.
In both cases, Drizzle ORM queries remain clean. Tenant isolation is a connection-level concern, not a query-level concern.
If you start with RLS and outgrow it, migrating to schema-per-tenant is more manageable than it sounds, provided you've kept your data layer reasonably clean.
| Phase | Work involved | Typical time |
|---|---|---|
| Schema provisioning tooling | Create scripts to provision schemas from tenant list | 1 week |
| Data migration | Copy rows per tenant to new schemas, validate row counts | 1–2 weeks |
| Application routing | Add tenant registry, connection routing middleware | 1 week |
| Testing and validation | Verify isolation, run regression tests | 1–2 weeks |
| Cutover | Blue-green or per-tenant gradual rollout | 1 week |
Total: 5–7 weeks for a mid-complexity SaaS. Not a 3-month rewrite. The main risk is data migration correctness (missing rows, broken foreign keys) — which you catch with validation scripts before cutover, not after.
The prerequisite is that tenant_id is reliably populated on every table with no missing values. If your data is clean, the migration is mostly mechanical. If you've accumulated tenant_id gaps over time, clean that up first.
The performance differences between RLS and schema-per-tenant matter less than most engineers assume, and more than the marketing materials for each approach suggest.
| Factor | Row-level security | Schema-per-tenant |
|---|---|---|
| Query overhead | RLS policy eval on every query (~0.1ms) | search_path lookup (negligible) |
| Index efficiency | Shared indexes, tenant_id as leading column | Per-tenant indexes, smaller, faster |
| Noisy neighbor | Yes — shared table locks and I/O | No — fully isolated |
| Connection pool | Simpler — one pool | Complex — pool per tenant or proxy |
| Analytics queries | Fast (no joins across schemas) | Slow (UNION across N schemas) |
For most SaaS applications under 100,000 users, neither approach creates a performance problem. The decision should be driven by compliance and operational requirements, not benchmark chasing.
Where RLS genuinely struggles: a single tenant with 50M rows in a shared table, running complex reporting queries during peak hours. That tenant degrades the experience for everyone. Schema-per-tenant eliminates this entirely — but you probably don't have this problem until you're well past $10M ARR.
What actually matters for RLS performance: ensure tenant_id is the leading column in composite indexes on high-volume tables. A query that filters by tenant_id first, then by another column, uses the index efficiently. Without this, every query scans more rows than necessary.
For the broader context of B2B SaaS MVP decisions — what to build in which order, what to defer, and what architecture choices lock you in — see building a B2B SaaS MVP. For the billing side of the architecture, Stripe billing for SaaS covers the implementation patterns that matter.
Hunchbite builds B2B SaaS products on Next.js and PostgreSQL. We've implemented both RLS and schema-per-tenant architectures in production and can help you choose the right approach for your product, compliance requirements, and growth stage.
Call +91 90358 61690 · Book a free call · Contact form
If this guide resonated with your situation, let's talk. We offer a free 30-minute discovery call — no pitch, just honest advice on your specific project.
How to set up Drizzle ORM with PostgreSQL from scratch — schema definition, migrations, query patterns, connection pooling, and the configuration decisions that matter in production Next.js applications.
11 min readguideA technical guide to database indexes: B-tree internals, composite index column ordering, covering indexes, partial indexes, the write cost of over-indexing, EXPLAIN ANALYZE interpretation, and the common indexing mistakes that degrade production performance.
14 min read