Multi-tenant SaaS architecture explained, with the trade-offs

Multi-tenancy means one app serves many customers with their data kept apart. The three models, why shared tables suit most SaaS, and how to enforce it.

By Sohail Hussain21 November 20266 min read

A multi-tenant SaaS is one application, running one codebase, that serves many customers, called tenants, while keeping each customer's data separate. There are three ways to separate it: every tenant's rows in shared tables marked with a tenant ID, a separate database schema per tenant, or a separate database per tenant. For nearly every new SaaS product, shared tables with a tenant ID, enforced by the database itself, is the right choice. The other two models are for specific needs: a small number of very large customers, regulation, or contracts that demand physical separation.

The decision matters early because it shapes every table, every query and every background job. Changing it after launch is one of the most expensive rewrites a SaaS can face.

What are the three tenancy models?

Shared tables. One database, one set of tables. Every table that holds customer data has a tenant_id column, and every query filters on it. Adding a customer is inserting a row.

Schema per tenant. One database, but each tenant gets its own schema, a namespace with its own copy of every table. The application switches schema per request.

Database per tenant. Each tenant gets its own database, sometimes its own server. The application picks the connection per request.

Shared tablesSchema per tenantDatabase per tenant
IsolationLogical; enforced by code and database policiesStronger; separate tablesStrongest; separate databases
Cost per tenantLowestLow to mediumHighest
Schema migrationsRun onceRun once per schemaRun once per database
Reporting across tenantsOne queryA query per schema, or a copyA pipeline that collects data
One tenant slowing othersPossible; shared resourcesPossibleIsolated
Comfortable number of tenantsThousands to millionsHundreds to low thousandsTens to hundreds
Good fitMost SaaS productsFewer, larger tenants with custom fieldsEnterprise, regulated or data-residency deals

Choosing the tenancy model is one of the first decisions in our web app and SaaS development projects, alongside billing, because both shape the data model from the first table.

Why do shared tables suit most SaaS products?

They are the cheapest to run, the simplest to change and the easiest to report on. One migration updates every customer. One backup covers everyone. One query answers "how many active accounts used feature X last month". Your tenth customer costs the same to add as your ten-thousandth.

The objection is safety: a single missing where tenant_id = ... and one customer sees another's data. That risk is real, and it is why the filter should not depend on developers remembering it. The database can enforce it.

How do you enforce isolation in a shared database?

Use three layers, so a mistake in one is caught by another.

1. Scope every query in the application

Put data access in one layer of code that takes the current tenant and adds the filter automatically. Pages and API routes call that layer, never the database directly. Code review rejects any query that bypasses it.

2. Make the database enforce it with row level security

PostgreSQL's row level security lets you attach a policy to a table so the database itself hides rows from other tenants, whatever query the application sends.

sql
create table projects (
  id uuid primary key default gen_random_uuid(),
  tenant_id uuid not null references tenants(id),
  name text not null
);
create index on projects (tenant_id);

alter table projects enable row level security;
alter table projects force row level security;

create policy tenant_isolation on projects
  using (tenant_id = current_setting('app.tenant_id')::uuid)
  with check (tenant_id = current_setting('app.tenant_id')::uuid);

At the start of each request, inside a transaction, the application sets the tenant:

sql
begin;
select set_config('app.tenant_id', '0b6f2c1e-6c1a-4f43-9a0e-3d5f8c2b7a11', true);
select * from projects; -- only this tenant's rows
commit;

Three details matter. force row level security applies the policy to the table's owner too; without it, an application connecting as the owner bypasses the policy. Superusers and roles with the BYPASSRLS attribute always bypass it, so the application should connect as an ordinary role. And the true in set_config scopes the setting to the transaction, which keeps it safe with connection pools. If the setting is missing or empty, the policy raises an error instead of returning everything.

On Supabase the same idea uses the signed-in user: policies check that the row's tenant is one the user belongs to, through a memberships table. Supabase vs Firebase covers the platform.

3. Test that it holds

Write automated tests that sign in as tenant A and try to read, update and delete tenant B's records through every API route. Run them on every change. This is the test that finds the forgotten filter before a customer does.

What else has to be tenant-aware?

The database is the obvious part. These are the ones teams forget:

  • Background jobs. Every job must carry the tenant ID and set it before touching data. A job that runs "for all tenants" should loop through them explicitly.
  • Caches. Cache keys must include the tenant. A cached dashboard served to the wrong tenant is a data leak.
  • File storage. Store files under a tenant prefix, and check the tenant when issuing download links.
  • Search indexes and AI features. A search index or a vector store for retrieval must filter by tenant at query time, or one customer's documents can appear in another's answers.
  • Logs and error reports. Include the tenant ID in every log line, so you can trace one customer's problem without exposing others. What to log and monitor covers the rest.
  • Exports and deletion. A customer leaving or asking for their data needs every table, file and index for their tenant found and exported or deleted.
  • Your admin tools. Staff need to see across tenants, which means a separate, audited path around the isolation. Building an admin dashboard covers how to do that safely.

How do you identify the tenant on each request?

Three common ways:

MethodExampleGood for
Subdomainacme.yourapp.comBranded workspaces, custom domains later
Pathyourapp.com/acme/projectsSimple setup, one domain
SessionActive tenant stored in the user's sessionUsers who switch between several tenants

Whichever you use, the tenant must come from something the server trusts: a verified session and a membership check, not a value the browser sends in a form. Then check, on every request, that the signed-in user is a member of that tenant with the right role. Roles and permissions covers designing those roles.

Use non-guessable IDs for tenants and records, such as UUIDs, so IDs in URLs do not reveal how many customers you have or invite guessing. Our UUID generator creates v4 and v7 IDs; v7 sorts by creation time, which keeps database indexes efficient.

When should you move a tenant to its own database?

When a contract or a regulator requires it, when one tenant is large enough to slow everyone else, or when a customer needs its data in a specific country. Build the application so the database connection is chosen per tenant from the start, even if every tenant points at the same database for years. Then moving one tenant out is an operations task, not a rewrite.

Do not start with database per tenant because it feels safer. It multiplies migration work, monitoring and cost for every customer, and makes the reports founders need hard to produce. Start shared, enforce isolation properly, and move the exceptions when they arrive. The pillar guide on how to build a SaaS product shows where this decision sits among the other early ones.

Was this guide useful?

Questions

Frequently asked.

Is a shared database safe for customer data?

Yes, when isolation is enforced in more than one place. Every query filters by tenant, the database enforces the same rule with row level security, and tests check that one tenant cannot read another's data. Most large SaaS products use shared databases. The risk comes from relying on developers to remember a filter, not from sharing itself.

Can one user belong to several tenants?

Usually they should be able to. Accountants, agencies and consultants work for several clients. Model it with a memberships table linking users to tenants with a role, and let the user switch the active tenant. Retrofitting this later is painful, so decide early even if the first version allows only one.

How do I offer a dedicated database to one large customer?

Keep the application code the same and make the database connection depend on the tenant. Most tenants use the shared database; the enterprise tenant's requests are routed to its own. This hybrid is common, and it works best when the code already scopes every query by tenant, so nothing else changes.

Sohail Hussain

Founder, MSH Technologies

Sohail has designed and built websites, web apps and AI products for over 12 years, for clients from Singapore-based industrial groups to early-stage SaaS startups. He writes about what actually worked.

Keep reading

Related guides.

All posts

Your next move

Like what you see? Tell us about it.

A process that takes too long, a website that undersells you, or an idea ready to take shape. Tell us where you are and we will suggest a useful next step.

India · Working with clients worldwide

Sohail, Founder, replies within 1 working day (Mon–Sat, IST). Free and no obligation.