The multi-tenancy decision is usually made in an afternoon, early, by someone adding a tenant_id column because it seemed obvious. It is one of the few architectural choices in a SaaS product that becomes harder to change every single day, and it quietly determines things nobody connects back to it a year later: how you restore one customer’s data, whether your biggest account can slow down everyone else, what your compliance answer is, and how much of your engineering time goes into schema migrations.
There are three architectures. Shared schema with a tenant column, one schema per tenant inside a shared database, and one database per tenant. Almost every discussion of these compares them on isolation and stops there, which makes the choice look like a simple security tradeoff. It is not. They differ most sharply on operational cost at tenant count, on migration mechanics, and on what happens when one tenant behaves badly.
The thing to hold onto: isolation is not a single property you have more or less of. It decomposes into data isolation, performance isolation, blast radius during a schema change, and the granularity at which you can back up, restore and delete. A given architecture is strong on some of these and weak on others, and the weak ones are what page you.
This guide covers the operational database, the one serving your product. Per-tenant analytics is a separate workload with a different shape, and I have written about where that aggregation work should happen in detail. Nothing here repeats it.
Key takeaways
- Isolation is four different properties: data, performance, migration blast radius, and per-tenant backup and deletion granularity. No architecture is strong at all four.
- Row-level security in Postgres is a real enforcement mechanism, not a convention, but it interacts with the query planner and with connection pooling in ways you must design around.
- Noisy neighbour problems are almost never solved at the database layer alone. Connection and query admission control at the application layer does more than any isolation setting.
- Per-tenant restore and hard deletion are the requirements that most often force a migration later. Decide how you will answer them before the first customer asks.
Three architectures and how each one breaks
Shared schema with a tenant column
Every table carries a tenant_id. Every query filters on it. One set of tables, one connection pool, one migration.
This is the right default for most products, and the reason is operational rather than architectural: a schema change is one ALTER TABLE, a connection pool is one pool, and a new tenant is one INSERT. Onboarding cost is effectively zero, which is what lets you have a free tier with many thousands of accounts.
What breaks:
A missing filter is a data breach. Not a bug, a breach. Every query, every join, every subquery, every new endpoint written on a Friday must carry the tenant predicate. Code review does not reliably catch this; the failure is an omission, and omissions are invisible in a diff. This is the single strongest argument for row-level security, which moves the guarantee from discipline to enforcement.
Indexes must lead with the tenant. An index on (created_at) is nearly useless when every query also filters by tenant, because the planner either scans a lot of other tenants’ rows or falls back to a sequential scan. Almost every index in a shared-schema design should have tenant_id as its leading column. Teams that skip this find out when their largest tenant arrives.
Data skew wrecks planner estimates. Tenant sizes in a real SaaS follow a steep distribution: most tenants are tiny, one or two are enormous. The planner uses statistics over the whole table, so it estimates the average tenant. The plan chosen for that average is wrong at both ends, and it is wrong in the expensive direction for your biggest customer. Extended statistics and per-query hints help; the underlying tension does not go away.
Hard deletion is expensive. A tenant leaves and asks you to delete their data. Now you are running large deletes across every table in a live system, generating dead tuples and vacuum work proportional to the deletion, in the same tables serving everyone else.
Schema per tenant
One database, one Postgres schema per tenant, identical table definitions inside each. The search path or a schema-qualified name routes queries.
What this buys you is a hard namespace boundary. A query without a tenant filter cannot silently return another tenant’s rows, because it is executing inside a schema that only contains one tenant’s tables. Per-tenant backup and restore become tractable, because a logical dump of one schema is a coherent unit.
What breaks:
Migrations multiply. A schema change must run against every schema. With a handful of tenants this is a loop. With thousands it is a long-running job with partial failure states, and you now need to reason about a system where some tenants are on the new schema and some are not. Your application has to tolerate both, for the duration of the rollout, every time.
Catalog bloat. Postgres stores every table, index and constraint in system catalogs. Thousands of schemas times dozens of tables each produces a very large catalog, and catalog size affects connection startup, planning, autovacuum and the cost of anything that enumerates relations, including many monitoring tools and backup utilities.
Connection pooling gets harder. Setting a search path is session state. A transaction-mode pooler hands you a different backend per transaction, so session-level search path settings do not survive. You either set it per transaction, use schema-qualified names everywhere, or pool per tenant, which reintroduces the connection count problem. This interaction is the most common thing teams discover too late; the connection pooler guide explains why session state and transaction pooling fight.
Still one instance. Schema separation gives you namespace isolation, not performance isolation. One tenant running a terrible query still consumes the shared buffer cache, the shared CPU, and the shared connection slots.
Database per tenant
Each tenant gets their own database, possibly on shared hardware, possibly not.
This is the strongest isolation on every axis that matters for compliance and blast radius. Backup, restore and deletion are per-tenant operations by construction. A tenant can be moved to dedicated hardware without touching anyone else. Data residency per tenant becomes possible rather than theoretical. Some enterprise deals are simply not winnable without this.
What breaks:
Cost per tenant has a floor. Every database has fixed overhead: connections, background processes, storage minimums, monitoring. Multiply by tenant count. This architecture is excellent at a hundred enterprise tenants and ruinous at a hundred thousand free ones, which is why the decision is really about your customer distribution rather than about your values.
Operational surface scales linearly. Every database needs monitoring, backup verification, version upgrades and connection management. What was one runbook becomes an inventory problem, and it needs automation before you have enough tenants for it to hurt, not after.
Cross-tenant queries stop being queries. Internal analytics, usage billing, admin tooling and support dashboards all want to ask questions across tenants. In this architecture that becomes a data pipeline, and you need it much earlier than you expect.
Connection multiplication. Each database has its own pool. An application server holding a few connections per tenant across many tenants exhausts the server’s connection limit quickly, which pushes you toward a proxy layer or toward tenants being routed rather than resident.
The serverless generation of Postgres providers changes the arithmetic here more than anything else has, because when an idle database costs nearly nothing to keep and provisions quickly, the per-tenant cost floor that ruled this architecture out for long-tail customers largely disappears.
Row-level security: a Postgres feature, not a product
Row-level security is built into Postgres. There is nothing to buy, and it is the mechanism underneath several of the products below rather than an alternative to them.
It works by attaching policies to a table. Once RLS is enabled, every query against that table has the policy’s predicate applied automatically by the planner, as if it had been written into the WHERE clause. The policy reads from something session-scoped, typically a configuration parameter you set at the start of a transaction or a database role, and the predicate compares it against the row’s tenant column.
The effect is that the tenant filter stops being a thing your developers remember and becomes a thing the database enforces. A new endpoint with a forgotten filter returns zero rows instead of everyone’s rows. That inversion is the entire value.
What it gives you
- Enforcement at the database rather than in application code, so the guarantee survives new engineers, generated queries and code paths nobody reviewed carefully.
- Policies apply to every access path including the psql session a support engineer opens, which is where a surprising number of real data exposure incidents come from.
- Separate policies per command, so a tenant can be allowed to read rows that they cannot update, expressed as data rather than as application logic.
- It composes with the shared-schema model, which means you can get much of the safety of stronger isolation without giving up single-migration simplicity.
What it does not do
- It does not give you performance isolation. A tenant’s expensive query is still expensive, and RLS adds a predicate rather than removing work.
- The policy predicate participates in planning, and a policy that is not index-friendly, or one that calls a function the planner cannot inline, can change plans in ways that are hard to trace back to the policy. Any RLS predicate must be satisfiable by an index that leads with the tenant column.
- Session-scoped settings and transaction-mode connection pooling interact badly. If you set a tenant context and the pooler hands the next transaction a different backend, your context is gone or, worse, stale. Set the context inside the same transaction as the query, every time, and test it under a pooler rather than against a direct connection.
- Table owners and superusers bypass policies by default unless you force it. An application connecting as the table owner has no protection at all, which is an easy and very quiet mistake.
- It does not help with deletion, export or per-tenant restore, which remain whole-table problems.
Needs first-hand data: Take your five highest-traffic queries and run each one twice against a copy of production data: once with RLS enabled and the tenant context set in-transaction, once with the equivalent predicate written literally into the
WHEREclause. CaptureEXPLAIN (ANALYZE, BUFFERS)for both. Compare plan shape and buffers read, not just duration. Any query where the RLS version picks a different plan is a query where your policy is not index-friendly, and that is a bug to fix before rollout rather than a cost to accept.
Noisy neighbours, and where isolation really lives
The noisy neighbour question gets asked as a database question and is mostly not one.
What actually happens when one tenant degrades everyone else, in rough order of frequency:
Connection exhaustion. One tenant triggers a burst of work, your application opens connections to serve it, and the connection limit is reached. Every other tenant now gets errors, not slowness. This is the most common cross-tenant failure and it is fixed at the pool and the application, not in the database.
One expensive query saturating shared resources. A report over a large tenant’s data evicts the buffer cache contents everyone else was relying on and consumes I/O bandwidth. The rest of the system does not error, it just gets slower in a way that is hard to attribute. Statement timeouts scoped per query class, and a separate connection pool with a small ceiling for reporting-style work, address this better than any isolation setting.
Lock contention. A tenant-triggered bulk update holding locks on shared tables, or a long transaction pinning the vacuum horizon for the whole database, affects everyone regardless of how the data is partitioned.
Autovacuum falling behind. A high-churn tenant generating dead tuples in shared tables makes vacuum work longer for everybody, and bloat degrades scans for all tenants including the quiet ones.
The mechanisms that genuinely help, in order of leverage:
Set statement_timeout per connection class, not globally. Interactive product queries get a short one. Background jobs get a longer one. Nothing gets unlimited.
Give each workload class its own pool with its own ceiling, so a background job surge cannot consume the connections that serve page loads.
Rate-limit expensive operations per tenant in the application, where you know which operation is expensive. The database does not know that a report is a report.
Partition your largest tables, usually by tenant for the very large tenants and by time for append-heavy ones. This bounds the work for maintenance operations and keeps one huge tenant from making every index scan worse.
Move the largest tenants to their own database once they are large enough to matter. The distribution makes this practical: moving a small number of tenants removes most of the variance, and a product that can move one tenant is a product that has solved the hard part of database-per-tenant without paying for it across the board.
Needs first-hand data: Produce your tenant size distribution before designing anything. Run a query that reports, per tenant, row counts on your three largest tables, storage consumed, and statements executed over a week from your query logs. Sort descending and look at the shape. The ratio between your largest tenant and your median tenant is the number that decides whether shared schema will hold, whether your indexes need partitioning, and which tenants to promote to dedicated resources first. Re-run it quarterly, because the shape changes as you move upmarket.
Per-tenant backup, export and deletion
These three requirements arrive in this order, roughly on this schedule: export when your first enterprise prospect asks for it in security review, deletion when your first customer leaves or a privacy request lands, and per-tenant restore the first time someone deletes something important through your own UI.
In a shared-schema design, all three are application-level features you have to build. Restore means recovering the whole database to a point in time on a separate instance and then copying one tenant’s rows back, respecting foreign key order, while the live system continues to change. It is doable and it is a project. Deletion means large deletes across every table with the vacuum consequences described earlier, and proving completeness to an auditor means enumerating every table containing tenant data including the ones added last quarter.
In a schema-per-tenant design, export and deletion become clean: dump a schema, drop a schema. Restore is a schema-level restore from a logical dump, which is far more tractable than picking rows out of a full-cluster recovery.
In a database-per-tenant design, all three are native operations, and this is the single strongest practical argument for the architecture, stronger than the security argument people usually lead with.
Whichever you choose, two things are worth building early because they are cheap early and expensive late. First, a registry of every table containing tenant data, maintained as a test that fails when a new table lacks a tenant column or an explicit exemption. Second, a periodic logical export per tenant, which gives you an answer to the export request, a coarse-grained recovery option, and a way to move a tenant between databases when you decide to. How the underlying physical backups work, and why a restore you have not tested is not a restore, is covered in the backup and point-in-time recovery guide.
Organisation modelling at the application layer
Before the database architecture there is a modelling decision that determines what “tenant” even means, and getting it wrong causes more pain than any storage choice.
The question is whether a user belongs to one tenant or many. Single membership is simpler in every respect and wrong for most B2B products, because consultants, agencies, contractors and people who change jobs all need to exist in more than one organisation. Retrofitting multi-membership onto a schema where users.tenant_id was the design means touching authentication, authorisation, session handling and every query, which is why it is worth deciding deliberately at the start even though the simple version looks adequate.
The shape that survives contact with reality is three concepts kept separate: a user identity that is global and unique, an organisation that is the tenant, and a membership that joins them and carries the role. The active organisation for a request then comes from the session or from the request path, is validated against membership on every request, and becomes the tenant context that your database layer enforces against.
Modern authentication providers model this directly, offering organisations, memberships, roles and an active-organisation claim in the session token, which saves you building invitation flows, role management and organisation switching. That is genuinely useful and it is worth being clear about the boundary: the identity provider tells you which tenant the request claims to be for, and your database enforces that the query only touches that tenant’s rows. Trusting the token alone, without enforcement below it, means an authorisation bug becomes a cross-tenant data leak. The authentication provider landscape covers the identity half of this in depth.
One more detail worth getting right early: make tenant identifiers opaque and non-sequential in anything a client sees. Sequential tenant identifiers in URLs invite enumeration, and enumeration plus one missing authorisation check is the standard shape of a multi-tenancy incident.
Nile

Nile is Postgres reshaped around tenancy as a first-class concept. Tables are declared as tenant-aware, the tenant context is set per connection or per transaction, and the platform handles the isolation and the routing rather than leaving it to your policies and your discipline. It also supports placing individual tenants on dedicated resources while keeping the same schema and the same application code, which is the migration path most teams eventually want and few can execute.
Pros
- Tenancy is a platform primitive rather than a convention, so the forgotten-filter failure mode is designed out rather than guarded against
- Individual tenants can be given dedicated resources without a schema change or an application rewrite, which is exactly the escape hatch a shared-schema design normally lacks
- Per-tenant operations such as isolation, placement and lifecycle are native, which removes the tooling you would otherwise build yourself
- Still Postgres underneath, so your extensions, drivers and SQL knowledge carry over rather than being replaced
Cons
- Newer and smaller than the established options, which matters for a component you cannot easily switch away from later
- The tenancy model is opinionated, and a product whose notion of a tenant does not match it will be fighting the platform rather than using it
- Building on a tenancy-aware layer means a migration away is not just a data move; it is reimplementing the isolation the platform was providing
Best for: B2B SaaS teams starting fresh who want tenancy enforced by the platform and a credible path to promoting large tenants to dedicated resources.
Pricing: Usage-based on compute and storage with a free tier, metered by consumption rather than per tenant; the shape rewards many small idle tenants.
Neon

Neon separates compute from storage, which makes a database a cheap logical object rather than a provisioned server. That changes what database-per-tenant costs: idle tenants scale their compute down, new tenants provision quickly, and branching makes per-tenant copies inexpensive. For a product with enterprise tenants who each want isolation, or for one that needs per-tenant restore without a full-cluster recovery, this is the architecture that was previously unaffordable becoming affordable.
Pros
- Scale-to-zero compute removes most of the per-tenant cost floor that ruled out database-per-tenant for long-tail customers
- Branching gives you cheap per-tenant copies for support investigation, testing and recovery scenarios without a full restore
- Point-in-time recovery operates per database, so restoring one tenant does not mean recovering everything and copying rows out
- Standard Postgres with standard drivers, so application code does not change when you move from shared schema to one database per tenant
Cons
- Many databases means many connection pools and a routing layer in your application, which is real work the platform does not do for you
- Cross-tenant queries for billing, analytics and admin tooling become a pipeline problem you must solve separately and earlier than you would expect
- Cold starts on scale-to-zero compute are a user-visible property for a tenant that has been idle, which needs a product decision rather than only an engineering one
Best for: Teams who want genuine database-per-tenant isolation for a moderate number of tenants without paying a per-tenant server cost floor.
Pricing: Metered on compute time and stored data with scale-to-zero, so idle databases cost close to storage alone rather than a per-instance minimum.
Citus

Citus is a Postgres extension that turns a group of Postgres nodes into a distributed database, sharding tables by a distribution column. For multi-tenant SaaS, that distribution column is your tenant identifier, which means every tenant’s rows for every table live together on one shard. Queries filtered by tenant are routed to a single node and execute as ordinary local Postgres, so you get horizontal scale without giving up joins and transactions within a tenant.
Pros
- Co-locating all of a tenant’s data on one shard means single-tenant queries, joins and transactions run as normal local Postgres rather than as distributed operations
- Scales past what one machine can hold while keeping the shared-schema simplicity of one logical database and one migration
- Reference tables replicated to every node let shared lookup data join locally without cross-node traffic
- Tenant isolation features can move a large tenant to its own shard, which handles the skew problem that breaks shared-schema designs at scale
Cons
- Cross-tenant queries and any query without the distribution column become distributed operations with very different performance, and some SQL is simply not supported in that path
- Picking the distribution column is effectively irreversible, and every table in the schema must agree on it, including ones you add later
- Running a distributed cluster is materially more operational work than a single Postgres, and the managed offerings tie you to a specific cloud
Best for: Shared-schema SaaS that has outgrown a single Postgres node and whose queries are almost always scoped to one tenant.
Pricing: Open source extension with no licence cost when self-managed; managed offerings are priced per node on compute and storage, so the meter scales with cluster size.
Crunchy Bridge

Crunchy Bridge is managed Postgres from a team with a deep Postgres operations background, deliberately conventional: real Postgres, standard extensions, no proprietary storage layer, with the operational work handled. For multi-tenancy that conventionality is the feature. Whichever of the three architectures you pick, it behaves the way the Postgres documentation says it will, which matters when your isolation guarantee depends on RLS semantics and pooler behaviour being exactly what you read about.
Pros
- Standard Postgres with no proprietary storage substitution, so RLS, partitioning and extension behaviour match the documentation exactly
- Strong extension support including the distributed and analytical extensions, so you are not blocked from changing architecture later
- Operational maturity in backups, upgrades and high availability, which is what you are actually buying from a managed provider
- Runs across the major clouds, so tenant data residency requirements are answerable without changing vendor
Cons
- No scale-to-zero or per-branch compute, so database-per-tenant carries a genuine per-instance cost floor and is only sensible for larger tenants
- Conventional by design, which means none of the tenancy-specific primitives the specialist platforms provide; you build the tenancy layer yourself
- Smaller ecosystem of integrations than the largest cloud providers, which occasionally shows up in tooling and vendor questionnaires
Best for: Teams who want plain, well-run Postgres to build their own tenancy model on, with predictable behaviour and no platform-specific semantics to learn.
Pricing: Per-instance subscription based on compute size and provisioned storage, billed on the resources you allocate rather than on usage.
How to choose
Start with your customer distribution, not with your security posture. It decides more than anything else.
Many small tenants, self-serve, a free tier. Shared schema with RLS. Anything else has a per-tenant cost or operational floor you cannot carry. Put tenant_id first in every index, write a test that fails when a new table lacks a tenant column, enforce with RLS rather than with code review, and plan to move your largest tenants out later.
Tens to hundreds of enterprise tenants, each paying real money. Database per tenant is affordable and buys you the answers to backup, deletion, residency and isolation questions that will otherwise consume your security review time repeatedly. Build the routing and the cross-tenant pipeline early, because both are worse to retrofit.
Shared schema that has outgrown one node. Distribute by tenant. It keeps the model you already have and the migrations you already write, and it addresses the skew problem directly.
Contractual hard isolation for some customers only. Hybrid. Shared schema for the long tail, dedicated databases for the ones who ask, with the same application code serving both. Design for this from the start by never letting a tenant’s database location be implicit; look it up from a tenant registry on every request even when the answer is always the same. That one indirection is what makes the hybrid possible later.
Whatever you choose, decide the organisation and membership model before the storage model, make tenant identifiers opaque, and write down today how you would delete one tenant completely. If you cannot answer the last one in a paragraph, you do not yet have an architecture; you have a tenant_id column.
| Approach | Data isolation | Migration cost | Per-tenant restore | Scales to many tenants | Performance isolation |
|---|---|---|---|---|---|
| Shared schema | Query-level, enforce with RLS | One migration | Build it yourself | Yes | Weak |
| Schema per tenant | Namespace boundary | Per schema, multiplies | Schema dump and restore | Limited by catalog size | Weak |
| Database per tenant | Strong | Per database, automated | Native | Cost-dependent | Strong |
| Distributed by tenant | Query-level, enforce with RLS | One migration | Build it yourself | Yes | Per shard |
| Postgres RLS (feature, not a product) | Enforced predicate | None | None | Not applicable | None |
Frequently asked questions
Is row-level security fast enough for production?
Yes, provided the policy predicate can be satisfied by an index that leads with the tenant column. RLS adds a predicate to the query; it does not add a separate check. The performance problems attributed to it are nearly always an index that does not support the predicate, or a policy calling a function the planner cannot inline. Measure with EXPLAIN (ANALYZE, BUFFERS) against a realistic data distribution rather than a uniform test dataset.
Should I start with shared schema or database per tenant?
Start with the one that matches your customer distribution. Self-serve products with a long tail start shared, because nothing else is affordable. Products selling to a small number of enterprises can start with database per tenant and save themselves the migration. The expensive mistake is starting shared with a product that is obviously enterprise-only, because you will migrate under deal pressure.
How do I handle schema migrations across thousands of tenant schemas?
Carefully, and with the assumption that it will partially fail. Migrations must be idempotent and re-runnable, the application must tolerate both old and new schema for the duration of the rollout, and the job needs per-tenant status tracking so a resume does not redo completed work. This overhead is the main reason schema-per-tenant is a harder sell than it looks, and it is worth reading up on migration tooling before committing to it.
Where should the tenant context come from?
From the session or the request path, validated against membership on every request, then set on the database connection inside the same transaction as the query. Never from a client-supplied parameter that is not validated, and never cached across requests on a pooled connection. Test the whole path under your production pooler configuration, because transaction-mode pooling is where session-scoped context silently disappears.
Related reading
- Best managed Postgres providers — the host decides what database-per-tenant actually costs you.
- Postgres vs ClickHouse vs Tinybird for per-tenant analytics — the analytics workload is a separate decision with a different shape.
- Best connection poolers for Postgres — why transaction pooling breaks session-scoped tenant context.
- Best database backup and point-in-time recovery tools — how per-tenant restore actually works under each architecture.
- Best authentication providers — organisation and membership modelling above the database.