SaaS Multi Tenancy Patterns: Database per Tenant vs Shared Schema
SaaS multi tenancy is the architecture that allows multiple customers (tenants) to share the same application infrastructure while keeping their data isolated from other customers. The three common patterns are: database per tenant (each customer has a separate database, providing the strongest isolation), schema per tenant (each customer has a separate schema in a shared database), and shared schema (all customers share the same tables with a tenant_id column for isolation). Each pattern has different tradeoffs for isolation strength, operational complexity, and cost at scale.
What you need to know
- Shared schema with row level security is the correct starting point for most SaaS products. It is the simplest to operate and easiest to evolve.
- Database per tenant provides the strongest isolation but multiplies operational complexity by the number of tenants. Adopt it only when contractually or compliance required.
- Row level security in PostgreSQL is the technical mechanism that makes shared schema safe. Configure it from the start.
- Migrating from shared schema to database per tenant later is painful. Make the right choice for the next two to three years of scale, not just for today.
- Schema per tenant is the useful middle ground when enterprise isolation requirements exist but database per tenant operational complexity is too high.
The core argument
The multi tenancy decision is made once and affects the system for years. Most teams make it wrong in one of two directions: they start with database per tenant because "enterprise customers need isolation," before they have enterprise customers, and then spend months managing migration complexity for a dozen customers who did not require it. Or they start with shared schema without row level security and discover that a missing WHERE clause caused a data leak in production.
The right decision depends on the current customer base and the next 12 months of expected enterprise requirements. For SMB focused SaaS where no customer has contractual isolation requirements, the answer is shared schema with PostgreSQL row level security: simple to implement, simple to operate, safe with proper RLS configuration. For SaaS actively selling to enterprises who are asking about data isolation, evaluate whether schema per tenant satisfies the requirement before committing to database per tenant. Many enterprise security teams accept schema per tenant with appropriate access controls.
The Velmora team made this call early in the product's architecture phase: shared schema with row level security for the first 18 months while we were serving SMB customers. When the first enterprise customer appeared with explicit database level isolation requirements, we migrated that customer to a dedicated database using the routing layer. The ability to mix isolation levels, routing some customers to shared infrastructure and others to dedicated databases, was the architecture decision that let us start with simplicity and scale to enterprise requirements incrementally.
Common mistakes
Implementing shared schema without row level security. Shared schema without RLS relies entirely on application layer WHERE clauses for isolation. A query that forgets the tenant filter returns all tenants' data. PostgreSQL's RLS makes tenant isolation mandatory at the database level, independent of application code. This is not optional if the application handles sensitive data.
Using database per tenant from day one without enterprise customers. Database per tenant before enterprise customers creates operational overhead (multiple databases, multiple migration processes, multiple backup jobs) without the business justification that enterprise contracts provide. Start simple and add isolation when customers require and pay for it.
Not including tenant_id in every table's primary index. In a shared schema, queries that filter by tenant_id are the most common query pattern. A composite index on (tenant_id, id) for every major table ensures these queries are fast. Tables without tenant_id in the index produce sequential scans for tenant filtered queries as data volume grows.
Building the tenant routing layer as an afterthought. The routing layer (which database or schema serves which tenant) needs to exist from the first day you have more than one isolation level, even if it starts simple. Routing logic embedded in application code rather than a proper routing layer becomes impossible to maintain as the number of tenants across different isolation levels grows.
Not testing cross tenant isolation in the test suite. Write tests that verify that tenant A cannot read tenant B's data. These tests run against every release and catch regressions in the tenant isolation logic before they reach production. Without these tests, isolation correctness is verified only by code review, which is insufficient.
Where to start
Start with PostgreSQL row level security configured before any tenant data enters the database. Enable RLS on every table that contains tenant specific data. Write the policy before writing application code. Test the policy by connecting with the application user and verifying that queries without setting the tenant context return zero rows.
Build the tenant resolution middleware from day one. Every request needs to resolve the tenant identity (from JWT claims, domain, or API key), set the tenant context for row level security, and pass the tenant ID to the application layer. This middleware is simple to write but important to have as a first class concern before any multi tenant data exists.
Document the isolation level offered in the pricing and terms. Different customers will want different isolation guarantees. Define the isolation levels (shared schema, schema per tenant, database per tenant) and which pricing tiers they correspond to before a customer asks. Being able to answer "what does your data isolation look like?" with a specific, documented answer is a sales advantage in enterprise deals.
FAQ
Frequently asked
- What is the simplest multi tenancy architecture for an early stage SaaS?
- When does database per tenant isolation become necessary?
- What is row level security in PostgreSQL and how does it enforce tenant isolation?
- What is the schema per tenant pattern and when is it useful?
- How do you migrate from shared schema to database per tenant?
Author
Why this work lands with me
I am Yashveer Singh. Founder of Yashveer Labs. I take this kind of project because I have done enough of them to know what kills them. The version of me that writes a post like this is the same one who builds the system afterward. There is no handoff to a junior, no agency middleman, no surprise scope. That is the bet I am making on my own brand.