Multi-Tenant SaaS Architecture: Choosing a Data Model

Your tenancy model is one of the hardest decisions to reverse in a SaaS product, because it reaches every table, query and backup. Most teams should not spend weeks on it, though. For a typical B2B product, the answer is a shared schema with row-level security, plus a planned way to give one large customer their own database later. This guide shows how that works in real SQL, where it breaks, and when to choose something else.

Multi-Tenant vs Single-Tenant Architecture

Single-tenant means each customer gets a dedicated deployment. Multi-tenant means customers share some or all of it. Microsoft's architecture guidance puts it well: treat isolation as a spectrum, because you can share the app tier and separate the data tier, or share nearly everything.

One distinction saves a lot of confusion. AWS notes that authentication and authorization do not give you isolation. A user can be properly signed in, hold the right role, and still reach another tenant's data if nothing scopes the query. Isolation is its own layer, and you have to build it deliberately.

For the database, three models cover almost every case. AWS names them pool, bridge and silo.

Shared Schema With Row-Level Security

In PostgreSQL, row-level security lets the database itself enforce which rows a session can see. AWS recommends comparing a tenant column to a runtime variable set by the application, instead of creating a database user per tenant. Here is a minimal version:

create table orders (
  id         bigint generated always as identity primary key,
  tenant_id  uuid not null references tenant (tenant_id),
  total      numeric(12,2) not null
);

-- policies filter on this column, so index it
create index orders_tenant_idx on orders (tenant_id, id);

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

create policy tenant_isolation on orders
  using      (tenant_id = current_setting('app.tenant', true)::uuid)
  with check (tenant_id = current_setting('app.tenant', true)::uuid);

Three lines there matter more than they look. With RLS on and no policy, Postgres denies by default, so a table you forget fails closed. The with check clause stops a tenant writing rows into another tenant, and a policy with only using applies the same check implicitly, though we prefer to spell it out. And force is there because table owners normally bypass RLS, which is one of the quietest ways this setup fails. Superusers and roles with BYPASSRLS always bypass it, so the app must never connect as one.

On each request, set the tenant inside a transaction, using a value from your verified session and never from the request body:

begin;
select set_config('app.tenant', $1, true);  -- true = this transaction only
select id, total from orders;               -- no WHERE tenant_id needed
commit;

That third argument is the detail to get right. PostgreSQL documents that the local form lasts only for the current transaction, while a session-level setting persists. PgBouncer's feature table marks session-level SET as unsupported under transaction pooling, so a plain SET behind a pooler risks leaving one tenant's context on a connection that serves the next. Use the transaction-local form every time.

Try it below. Switch the tenant, then see what happens with RLS off, and when the app connects as the table owner.

Add these checks to your test suite. Connect as the application role, set tenant A, and assert that tenant B's rows are invisible and unwritable. Run it against every table. Microsoft's guidance says to test explicitly that one tenant's data cannot leak to another. We would also add a CI check that fails when a new table has a tenant_id column and no policy. Referential checks need thought too: Postgres docs warn that unique and foreign key checks bypass RLS, which can reveal that a value exists in another tenant, so scope unique constraints with tenant_id.

If you are on Supabase, two more rules apply. Its docs warn that the service_role key has bypassrls, so it must stay on the server, and that views bypass RLS by default unless you set security_invoker. Its RLS performance guide is worth reading: indexing the policy column took one test from 171 to under 0.1, and wrapping functions in a select took another from 11,000 to 7. The units are unlabeled there, and milliseconds is the likely reading. The size of the change is the point.

Fairness matters here. Microsoft notes that many multitenant solutions skip row-level security because tenant identity has to reach the database with each query, which is complex to build and test. Enforcing the filter in a shared data layer can work. We prefer RLS because a single missed where clause then fails safely, and the cost of setting it up early is low.

Schema Per Tenant and Database Per Tenant

Schema per tenant looks like a clean middle ground, and it is easy to explain to a buyer. AWS is blunt about it: the bridge model has the same noisy neighbor concerns as the pool model and adds provisioning work. pg_dump -n can export one schema, though Postgres warns that the dump includes no objects it may depend on elsewhere, so a clean restore is not guaranteed. The real cost arrives in migrations, which now run once per tenant, and each can fail halfway. We reach for this model rarely, usually with a modest tenant count and a buyer who needs the separation explained simply.

Database per tenant gives you the strongest story. Per AWS, a silo eliminates the noisy neighbor problem and can limit outages to one tenant, and what generally drives its adoption is strict security and regulatory constraints. The price is cost, a harder central view of activity, slower onboarding, and a mapping layer from tenant to database. Microsoft's warning is the one to take seriously: use automated deployment, or the work of managing those databases becomes overwhelming.

Noisy Neighbors, Migrations and Per-Tenant Restore

These three will decide how the model feels in year two.

• Noisy neighbors. AWS says the problem cannot be fully eliminated in a pool, only reduced, by offloading reads to replicas or a cache. Postgres does not know which tenant is using resources by default, so you need instrumentation in the app. Log tenant ID with every slow query, and add per-tenant rate limits before your largest customer finds the limits for you.
• Migrations. Azure warns against manual schema changes and against an app that depends on one schema version. Keep each release compatible with the previous schema, and split destructive changes across releases. This becomes mandatory once tenants upgrade at different times.
• Per-tenant restore. Microsoft's guidance is direct: in a shared database, restoring one tenant might mean restoring the database to a separate resource and selectively recovering that tenant's data. Write that procedure now and rehearse it. Keep tenant_id on every table, including child tables, or the copy turns into a painful join exercise.

When an Enterprise Customer Asks for Isolation

AWS expects this: some customers will not find RLS's logical separation sufficient. Before you promise anything, ask what they actually need. A security questionnaire is often answered by your policies, tests and audit logs. Azure lists the conditions that really push you to separate a tenant: their own encryption keys, individual backup and restore policies, or storage in a specific region.

If those apply, use what Microsoft calls vertical partitioning: most tenants on shared infrastructure, a few on dedicated. It also recovers cost, since you can charge more for the dedicated tier. The risk is that your code has to work in both shapes. The fix is small if done early. Route every connection through one function that looks up a tenant's database, even while every tenant points to the same one.

Choose a Model for Your Product

Answer four questions. The rules are simple: hard isolation requirements lead to a dedicated database or a hybrid, and everything else stays shared. The bars are our judgment on a five-point scale, so read them as an informed opinion rather than a measurement.

What We Learned Running This on AskOrac

AskOrac, our AI chatbot platform, keeps chunks, documents and tenants in one Postgres database with pgvector. Isolation, joins and backups stay simple, with no second system to keep in step. The trade-off appeared at the hardest possible place: filtered vector search.

Production traces showed vector search at a 2,087 ms median and 4,073 ms at p95, with wide swings between adjacent requests. The HNSW index is global, so a project filter either dropped most results or pushed Postgres into a sequential scan. pgvector's docs explain why: approximate indexes apply filters after the index scan. With the default hnsw.ef_search of 40, a condition matching 10% of rows yields about 4 rows.

We enabled pgvector's iterative scan, added in version 0.8.0, so the graph search keeps expanding until enough results survive the filter. The shape of it, with simplified names:

begin;
set local hnsw.iterative_scan = relaxed_order;
select id, content
from chunks
where project_id = $1
order by embedding <=> $2
limit 20;
commit;

We used relaxed ordering, because our re-ranker re-sorts every result anyway, and strict ordering would have bought nothing. Retrieval stopped being the bottleneck. Later tracing put most of the roughly 3.5-second median in the model call. Without per-stage traces, we would have gone on tuning the wrong layer. pgvector also documents partial indexes for a few distinct filter values and partitioning for many, so check those if your largest tenants dominate.

Where This Is Heading

Three dated shifts are changing the trade-offs.

• Per-tenant databases are getting cheaper to idle. Neon markets one project per tenant with idle compute paused and no compute charge until the tenant returns. That is a vendor's claim, and the page does not say storage drops to zero, but it narrows the old gap for products with many quiet tenants.
• Leaving a provider is becoming a right. The EU Data Act has applied since 12 September 2025 and sets rules for customers switching between data-processing services. If you sell to EU customers, ask counsel how it applies to you, and treat clean per-tenant export as a feature.
• AI features put tenancy into the index. pgvector's iterative scan arrived because filtered search is a real problem. Any product adding retrieval must decide how vectors are scoped before it loads a single tenant's documents. Our AI integration guide is a good next step.

What We Would Do First

1. Put tenant_id on every tenant-owned table, including child tables, from the first migration.
2. Enable and force RLS, connect as a non-owner role, and set context per transaction.
3. Add cross-tenant tests to CI.
4. Route all connections through one tenant lookup, even with one database.
5. Log tenant ID on slow queries and rehearse a one-tenant restore.

If you are planning a new product, our SaaS development work starts with this decision, and our backend and API service covers retrofits on live systems. Send us your schema and tenant count and we will tell you what we would change, if anything. For budget context, see our SaaS development cost guide.