SaaS Multi Tenancy Patterns: Database per Tenant vs Shared Schema

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: 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

1. 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.

1. 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.

1. 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.

1. 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.

1. 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

1. 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.

1. 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.

1. 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.

Related reading

• Plan Upgrades and Downgrades: A Billing Architecture Story
• Schema Design Decisions That Haunt You at Million User Scale
• SCIM Provisioning: The Feature Enterprise Customers Will Demand
• Single Sign-On for Enterprise SaaS: SAML and OIDC Compared