Multi-tenancy for SaaS: shared schema or database per tenant

Every SaaS product has to answer one architectural question early: how is each customer's data kept apart? The answer shapes how you enforce security, what your infrastructure costs, how long migrations take, how you handle a large customer who wants their data in a particular region, and how painful it is to change your mind later. There are three common models, each with real strengths. The mistake we see most often is not picking the wrong one; it is picking one by default without understanding what it commits you to.

Model one: shared tables with a tenant column

All customers share the same database and tables, and every tenant-owned row carries a tenant identifier. Queries filter by that identifier. This is the most common model and our default for most new products.

Its strengths are simplicity and cost. There is one database to run, back up and migrate. Adding a customer is inserting a row. Cross-tenant reporting for your own analytics is a single query. Infrastructure costs grow with total usage, not with customer count, which suits products with many small customers.

The weakness is that isolation depends entirely on every query including the tenant filter. One missing condition in one report can expose one customer's data to another. That risk is manageable, but only with deliberate safeguards.

Making shared tables safe

We never rely on developers remembering to add a filter. Instead, isolation is enforced in layers:

• Automatic scoping in the ORM. A global scope applies the current tenant to every query on tenant-owned models, so the default is safe and bypassing it requires an explicit, reviewable call.
• Row-level security in the database. Where the database supports it, policies reject rows from other tenants even if the application forgets. This is the strongest safeguard for shared tables.
• Composite keys and foreign keys that include the tenant, so a record cannot reference a record belonging to another tenant.
• Tests that try to cross the boundary. For each endpoint, a test creates two tenants and asserts that one cannot read or modify the other's data.

ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON projects
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

In a shared-table system, tenant isolation is a feature you build and test, not a property you get for free.

Model two: schema per tenant

Some databases support multiple schemas, or namespaces, within one database. Each tenant gets its own copy of the tables in a separate schema, and the application switches schema per request. Isolation is stronger than with a tenant column, because a query without the right schema simply finds nothing. Per-tenant backup and restore is easier, and one noisy tenant's large tables do not bloat indexes for everyone else.

The costs arrive with scale. Every migration must run once per tenant, so a schema change across 3,000 tenants becomes a long-running operation that must handle partial failure. Connection pooling becomes more complex, and some tools and hosted services handle thousands of schemas poorly. We find this model works well for products with tens to a few hundred larger tenants, less well beyond that.

Model three: database per tenant

Each tenant gets its own database, possibly on its own server. Isolation is as strong as it gets short of separate infrastructure. Large customers can be placed in a specific region to satisfy data residency, given dedicated resources, restored independently, and even run on a different version during a staged upgrade. For enterprise and regulated markets, this can be a selling point in its own right.

It is also the most expensive and operationally demanding option. Each database has a baseline cost, monitoring and backup multiply, and migrations become a fleet rollout that needs tooling. Onboarding a new customer means provisioning infrastructure, which must be automated from day one.

How we choose

We work through a short set of questions with the client:

1. How many tenants do you expect in three years, and how large is the biggest compared with the median?
2. Do customers contractually require data residency, dedicated infrastructure or independent restore?
3. What does a data leak between tenants cost you, in regulatory and commercial terms?
4. How large is the team that will operate this, and does it include infrastructure specialists?
5. Will you sell a self-serve tier with many small customers, an enterprise tier with few large ones, or both?

For self-serve products with many small customers, shared tables with row-level security is almost always right. For products selling mainly to large enterprises, database per tenant is often worth its cost. The hybrid option is increasingly common: shared tables for the standard tier, with the ability to move a large customer onto a dedicated database. If you design for it from the start, by routing every data access through a tenant-aware connection resolver, that move becomes a data migration rather than a rewrite.

Decisions to make on day one

Whatever model you choose, a few decisions are cheap early and expensive later: use globally unique identifiers rather than per-table sequences so records can move between databases; resolve the current tenant in one place in the request lifecycle; include the tenant in every log line and background job payload; and keep tenant-specific configuration in data rather than code. For one B2B client, those choices meant moving their three largest customers onto dedicated databases took two weeks instead of the quarter they had feared.

Multi-tenancy design is a core part of our SaaS platform builds, and our database architecture team reviews existing tenancy models for products that are outgrowing their first design. Where regulated customers are involved, we often pair that review with penetration testing focused on tenant isolation.

Design tenancy you will not regret

If you are starting a SaaS product, or your current tenancy model is showing strain, tell us about your customers and plans. We will send a fixed-price proposal for the architecture or the build within 24 hours. Get in touch with us.