Designing Multi-Tenant Data Isolation: Schema-per-Tenant vs Row-Level
Row-level multi-tenancy — a single schema with a tenant_id column on every table — was our default for a SaaS client with hundreds of small tenants. It's operationally simple: one set of migrations, one connection pool, one set of indexes to tune. The risk is entirely concentrated in application code: forget a WHERE tenant_id = ? clause in one query and you've got a cross-tenant data leak, which is exactly the kind of bug that doesn't show up in testing with a single seeded tenant.
We mitigated that risk with Postgres row-level security policies rather than trusting every query to remember the filter. Every table gets a policy tied to a session variable set at the start of each request (SET app.current_tenant = ...), and the database itself refuses to return rows outside that tenant regardless of what the application query looks like. It's a second layer of defense that caught two would-be leaks in code review before they shipped.
Schema-per-tenant made sense for a different client in a regulated industry where contracts required demonstrable data isolation, not just application-level guarantees. Each tenant got its own Postgres schema with identical table structure, migrated in lockstep via a script that looped through all schemas. It's a stronger isolation story for auditors, but it turned what used to be a single migration into an operation that had to succeed across 200+ schemas — one failed migration partway through left us with schema drift we had to reconcile manually.
The schema-per-tenant model also broke our assumptions about connection pooling. Each tenant's queries needed to target the right schema, and with a shared connection pool that meant either a SET search_path on every checkout from the pool or maintaining separate smaller pools per tenant. We went with the search_path approach, but it added a class of bug where a connection returned to the pool without resetting search_path could leak one tenant's queries into another tenant's schema.
For new projects now, we default to row-level tenancy with RLS enforced at the database layer unless there's a specific compliance requirement for physical or logical schema separation. It scales operationally to thousands of tenants in a way schema-per-tenant doesn't, and the RLS policies close most of the gap in isolation guarantees that used to be the main argument for separate schemas.
