Multi-tenancy decides where one customer's data ends and another's begins. That boundary is hard to move once production data exists, and it affects more than table design: it shapes how you write queries, run migrations, back up and restore a single customer, price the product and answer a security questionnaire.
There are three common ways to model it in a relational database. This article compares them, using PostgreSQL for the examples, and explains the decision I made for Occentra.
Three ways to separate tenants
invoicestenant_id on every row
tenant_a.invoicestenant_b.invoices
db_a: invoicesdb_b: invoices
- Shared tables: every tenant's rows live in the same tables, distinguished by a
tenant_idcolumn. - Schema-per-tenant: one database, with an identical set of tables in a separate PostgreSQL schema for each tenant.
- Database-per-tenant: each tenant has its own database. Those databases can share a PostgreSQL server or run on dedicated infrastructure, and that is a separate decision.
A tenant here means a customer organisation, not an individual user. Treating these three options as a simple spectrum from weak to strong isolation is misleading, because logical data separation, database-enforced access controls and infrastructure isolation are separate decisions. The right combination depends on your workload, how sensitive the data is, what customers ask of you contractually and how much operational tooling you are prepared to run.
Shared tables put isolation in your queries
This is the simplest model. Every tenant-owned table carries a tenant_id, and every query must filter on it.
CREATE TABLE invoices (
tenant_id uuid NOT NULL REFERENCES tenants (id),
id uuid NOT NULL DEFAULT gen_random_uuid(),
number text NOT NULL,
total numeric(12,2) NOT NULL,
PRIMARY KEY (tenant_id, id),
UNIQUE (tenant_id, number)
);Leading the primary key and unique constraints with tenant_id keeps uniqueness per tenant and indexes the common access path. Child tables can include tenant_id in composite foreign keys so rows cannot reference another tenant's records.
Migrations are straightforward: one schema change, applied once. Connection pooling is simple because any connection can serve any tenant, and internal cross-tenant reporting is an ordinary query. The trade-off is that isolation relies on every query being scoped. A missing WHERE tenant_id = ? in a report, raw query or background job leaks data silently.
Application scoping, then Row-Level Security
Framework global scopes or query middleware handle routine filtering, though raw SQL, imports and reporting queries can still bypass them. PostgreSQL Row-Level Security (RLS) adds a database-enforced check:
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoices FORCE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON invoices
USING (tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::uuid)
WITH CHECK (tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::uuid);Inside a transaction, the application sets the active tenant with SELECT set_config('app.tenant_id', $1, true), keeping the value local to that transaction so pooled connections do not leak context between requests. If no tenant is set, reads return no rows and writes are rejected. For RLS to hold, the application must connect as a role that neither owns the tables nor carries BYPASSRLS, with migrations run under a separate role.
RLS reduces the blast radius of an application bug, though it can affect query plans when policy expressions grow complex, and tenants still share tables, indexes and server resources. Shared tables suit products with uniform schemas and large or fast-growing tenant populations, where fixed table counts keep operations simple. Their main weaknesses are single-tenant recovery, which requires extracting and reinserting one customer's rows, and large tenants skewing table sizes and statistics for everyone else.
Schema-per-tenant gives each tenant a namespace
A PostgreSQL schema is a namespace inside a database. Each tenant gets its own copy of the application tables, so tenant_a.invoices and tenant_b.invoices are separate relations, and an individual tenant can be exported logically with pg_dump --schema.
CREATE SCHEMA tenant_7f3a;
BEGIN;
SET LOCAL search_path TO tenant_7f3a, public;
SELECT * FROM invoices; -- resolves to tenant_7f3a.invoices
COMMIT;search_path and connection pooling
Unqualified table names resolve through search_path, which shifts the main risk from missing WHERE clauses to connection state: the wrong search_path reads another tenant's tables.
How that context is managed depends on pooling. When connections stay bound to a client session, middleware or job setup can set and reset search_path per request or job. With transaction poolers such as PgBouncer in transaction mode, backend connections rotate between transactions, so session-level SET statements are unsafe; SET LOCAL scopes the path to each database transaction instead, without wrapping an entire HTTP request in one long transaction.
When the application connects as a single database role, schemas organise data rather than enforce access control, because that role can still query any schema. Per-tenant database roles make the boundary enforceable, at the cost of extra role management and less effective connection sharing.
Migrations and tenant counts
Every schema change has to run once per tenant. That requires tooling to loop across schemas, track per-tenant migration state and handle partial failures or lock contention during deployments.
Each tenant also multiplies tables and indexes (schemas × tables), increasing PostgreSQL catalogue size, pg_dump duration and background maintenance work. Practical limits depend on table count per schema, workload and how automated your migration and backup tooling is, rather than a fixed tenant threshold. Cross-tenant reporting also becomes harder, usually requiring UNION ALL queries or a separate reporting pipeline.
Schema-per-tenant suits products where structural separation, per-tenant logical exports and clean customer offboarding justify tenant-aware tooling, while total relation counts remain comfortable to manage.
Database-per-tenant separates the database, not necessarily the server
Each tenant has its own database. Because a PostgreSQL connection attaches to a single database and does not query across databases in normal operation, accidental cross-tenant access is much harder. A tenant database can be dumped and restored logically on its own with pg_dump and pg_restore, dropped cleanly when a customer leaves, or kept on an older schema version during a staged rollout.
Separate databases do not automatically mean separate servers or separate physical backups. Many databases can share one PostgreSQL cluster or managed instance, sharing CPU, memory, storage, WAL and max_connections. Because physical backups, storage snapshots and WAL-based point-in-time recovery operate on the whole cluster rather than a single database, a shared cluster gives logical isolation and per-database dumps, not independent PITR, resource isolation or separate failure domains. Dedicated clusters or instances per tenant are what provide independent physical backups and PITR, separate maintenance windows, customer-specific encryption keys, regional placement and dedicated capacity, and they are priced accordingly.
The operational trade-off is fleet management. Connection pools cannot be shared across databases, so active tenants compete for max_connections on shared clusters, and you need automated provisioning, connection routing, monitoring and migration orchestration across every database. Many teams use a hybrid approach: shared clusters for standard plans, and dedicated instances for customers whose contracts or workloads require them.
Side-by-side comparison
| Aspect | Shared tables | Schema-per-tenant | Database-per-tenant |
|---|---|---|---|
| Logical data isolation | Row-level via query scoping, optionally backed by RLS | Separate tables per schema; a single app role can still reach all schemas | Separate databases per connection |
| Operational complexity | Lowest | Moderate; tenant-aware tooling needed | Highest; provisioning, routing and fleet monitoring |
| Migration management | One migration, run once | Run per schema; needs orchestration | Run per database; allows staged rollouts |
| Per-tenant customisation | Awkward; shared structure | Possible, at the cost of uniformity | Straightforward, at a maintenance cost |
| Tenant backup and restore | Row extraction and reinsertion; cluster-wide physical backups | Per-schema logical dump and restore; cluster-wide physical backups | Per-database logical dump and restore; physical backups and PITR remain cluster-wide unless dedicated |
| Resource isolation | None beyond application quotas | None; shared cluster resources | None on a shared cluster; full on dedicated instances |
| Cost characteristics | Lowest per tenant | Low infrastructure cost, higher engineering cost | Modest on shared clusters; highest on dedicated instances |
| Scaling with tenants | Fixed table count; scales with row volume and write load | Scales with total relations (schemas × tables) and migration time | Scales with per-database connections on shared clusters, and fleet tooling across instances |
How far each model scales depends on tables per tenant, workload, connection pooling and operational tooling rather than fixed tenant-count thresholds.
Why I chose schema-per-tenant for Occentra
Occentra is an occupational health SaaS platform that I founded, designed and engineered. It is built with Laravel, React, TypeScript, PostgreSQL and AWS, and it serves occupational health providers. It manages referrals, cases, appointments, clinical assessments and reports, so the data is sensitive clinical and operational information. Health data is special category data under UK GDPR, which raises the cost of getting separation wrong.
Each provider has its own PostgreSQL schema. The main appeal was a clearer logical separation between customer datasets without running a separate database deployment for each provider. With shared tables, isolation would depend on every query carrying the right filter. With database-per-tenant, I would take on provisioning, connection and fleet management for every new customer. Schema-per-tenant sat between the two: the structure itself separates one provider's records from another's, while there is still one database to run, monitor and back up.
It is not a free choice, and I do not regard it as the right answer in general. These are the costs I accepted:
- Migrations need orchestration across every tenant schema, rather than a single statement.
- Database operations have to be tenant-aware, including jobs, imports, exports and reports.
- Connection and schema context must be managed carefully, as described above, because the wrong context means the wrong dataset.
- Operational considerations grow with the number of tenants.
- The database is still shared infrastructure. A schema is logical namespace separation, not the isolation of a dedicated server, so the shared database instance remains a common resource and a common failure domain.
Occentra's schema separation is one layer. Access is also limited by roles and permissions, with audit history for meaningful activity. If a customer required dedicated infrastructure, I would treat that as a separate decision and not assume schemas satisfy it. The case study describes how the platform fits together.
Choosing by constraint
Start from the requirement that is hardest to change later.
- Large numbers of small, uniform tenants: shared tables, with application scoping and RLS as a backstop. Keeping relation count fixed avoids per-tenant migration fan-out and catalogue growth when onboarding is high-volume and self-serve.
- Organisations with sensitive data and bounded relation growth: schema-per-tenant is a reasonable fit when total table counts stay manageable and you are prepared to maintain migration tooling and strict tenant-context handling. Shared tables with RLS and a non-owner application role are also defensible, so weigh structural separation against operational simplicity.
- Customers requiring dedicated infrastructure or independent recovery controls: database-per-tenant on dedicated clusters or instances, often as an enterprise tier alongside a shared multi-tenant deployment.
- An early-stage product where simplicity matters most: shared tables. Add
tenant_idfrom the first migration, scope consistently and enable RLS if you can. You can run fewer systems and ship faster, and many products do not need more.
Requirements change, and moving between models is a significant migration involving data movement, application changes, new tooling and often downtime planning. That is no argument for the heaviest option up front, but build tenant context as a single, well-defined concept in the code so a later change is contained.
No universally correct tenancy model
Shared tables are entirely appropriate for many applications, and their operational simplicity is often worth more than structural separation, especially with RLS as a safety net. Separate databases give cleaner logical boundaries and independent logical dumps, and gain independent physical backups, point-in-time recovery and resource isolation when placed on dedicated clusters, at the price of fleet management. PostgreSQL schemas sit usefully in between when customer datasets warrant structural separation and your workload and tooling suit per-schema operations.
The deciding factors are the product's requirements, regulatory context, expected tenant population, your operational capability and how much complexity you can carry. If you are weighing this for a SaaS product, I work on this kind of SaaS development with Laravel and PostgreSQL, and you can get in touch to discuss it.
