When we built our first multi-tenant SaaS product in 2017, we chose the wrong isolation model. Three years and $800K in re-architecture costs later, we had learned the hard way that this decision reverberates across your entire stack. Since then, we have helped 18 SaaS companies design or rebuild their tenancy models. Here is everything we know.
The Three Fundamental Models
Every multi-tenancy implementation is a point on a spectrum between two extremes: fully isolated (one database per tenant) and fully shared (all tenants in the same tables, distinguished only by a tenant_id column). In the middle sits the hybrid approach: shared application infrastructure with per-tenant database schemas.
Each model makes a different trade-off between isolation, cost, operational complexity, and scalability. There is no universally correct answer — the right choice depends on your regulatory environment, your pricing tier structure, your team's operational maturity, and your projected growth trajectory.
Fully isolated (Database-per-Tenant): Maximum data isolation, simplest compliance story for regulated industries (HIPAA, SOC 2, FedRAMP). Onboarding a new tenant requires provisioning infrastructure. Operational overhead scales linearly with tenant count. Only viable at scale if you have robust Terraform-driven tenant provisioning. Cost: high.
Shared schema (Row-Level Security): All tenants share tables with a non-nullable tenant_id foreign key. PostgreSQL's Row-Level Security (RLS) policies enforce isolation at the database level. Lowest infrastructure cost, fastest onboarding, but the compliance narrative is harder to sell to enterprise buyers. A misconfigured RLS policy is a catastrophic data breach. Cost: low.
Hybrid (Schema-per-Tenant): A single PostgreSQL cluster hosts one schema per tenant. Migrating schemas with Prisma Migrate or Flyway requires fan-out orchestration. Better isolation than shared tables, lower cost than database-per-tenant. Our default recommendation for most B2B SaaS products.
Note
For products targeting enterprises with strict compliance requirements (HIPAA, SOC 2 Type II, ISO 27001), always design for database-per-tenant from the start, even if you do not enable it for all customers on day one.
Implementing Row-Level Security Correctly in PostgreSQL
Row-Level Security is powerful but requires surgical precision. A common mistake is enabling RLS and writing a policy that references the session variable current_setting('app.current_tenant_id') — but failing to verify that the application always sets this variable before executing queries. A single unguarded code path returns every row in the table.
We add a database-level function that throws an exception if the tenant context variable is not set, and we call that function in every RLS policy. This turns misconfigured application code into a loud runtime error instead of a silent data leak.
-- Create a tenant context function that fails loudly
CREATE OR REPLACE FUNCTION current_tenant_id() RETURNS uuid AS $$
DECLARE
tenant_id text;
BEGIN
tenant_id := current_setting('app.tenant_id', true);
IF tenant_id IS NULL OR tenant_id = '' THEN
RAISE EXCEPTION 'app.tenant_id is not set in session context';
END IF;
RETURN tenant_id::uuid;
END;
$$ LANGUAGE plpgsql STABLE;
-- Attach to every tenant-scoped table
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON projects
USING (tenant_id = current_tenant_id());Migrations at Scale: The Fan-Out Problem
Schema migrations become operationally complex the moment you exceed 50 tenants with the schema-per-tenant model. A single ALTER TABLE statement that takes 200ms on one schema takes 10+ seconds across 50, and risks locking tables that tenant workload is actively writing to.
We solve this with an async migration orchestrator: a background worker that applies migrations one schema at a time during low-traffic windows (typically 2–6am UTC), records per-tenant migration state, and retries failed schemas with exponential back-off. No tenant is blocked, no single migration deployment holds up the release train.
Tenant-Aware Caching and Connection Pooling
Naïve caching in multi-tenant applications is a data isolation disaster. A cached API response that omits the tenant key from the cache key will serve Tenant A's data to Tenant B. We enforce a mandatory cache key prefix convention at the infrastructure level using a TenantCacheWrapper that injects the tenant context before every read and write to Redis.
Connection pooling deserves equal attention. PgBouncer in transaction mode is incompatible with PostgreSQL session variables (which RLS relies on). You must use session mode for RLS, which significantly reduces the scalability benefit of connection pooling. For high-tenancy-count deployments, a connection-per-tenant pool strategy or per-tenant proxy instances are necessary.
Conclusion
The right multi-tenancy model for your SaaS product is not a technology decision — it is a business model decision. Your pricing tiers, target compliance certifications, and projected tenant count at scale should drive the choice, not the preferences of your initial engineering team. Get this right on day one. The cost of getting it wrong compounds with every tenant you onboard.
Key Takeaways
- Choose your tenancy model based on compliance requirements and projected scale, not engineering preference
- Row-Level Security is powerful but dangerous — always fail loudly on missing tenant context
- Schema-per-tenant is the best default for most B2B SaaS products
- Build an async migration orchestrator before you exceed 20 tenants
- Never cache data without tenant context in the cache key