Engineering question: How do you isolate client data in a high-throughput enterprise system without doubling infrastructure costs every time you onboard a new organization?
This is one of the first irreversible decisions in any multi-tenant ERP. Get it wrong and you either pay for it in monthly cloud bills forever, or you pay for it in a painful migration two years later. Below is how we reason about it in production.
The three isolation models
There are really only three places you can draw the tenant boundary, and the choice is a spectrum from "strongest isolation, highest cost" to "cheapest, most operational risk."
1. Database-per-tenant
Each organization gets its own physical database (or its own cluster). Strongest isolation, simplest mental model, easiest per-tenant backup and restore.
The cost is operational. A hundred tenants means a hundred databases to migrate on every schema change, a hundred connection pools, and a hundred sets of credentials to rotate. Connection overhead alone becomes the bottleneck long before query load does.
Use it when: you have a small number of large, compliance-sensitive tenants (healthcare, government, finance) where a noisy-neighbor incident or a cross-tenant data leak is unacceptable.
2. Schema-per-tenant (one database, many schemas)
A single PostgreSQL database holds one schema per tenant (tenant_001.invoices, tenant_002.invoices). You get logical isolation and a flat infrastructure footprint at the same time — this is the sweet spot for most mid-market ERP work, and the model we reach for by default.
You route the tenant context at the connection-pool level by setting the active schema per request:
SET search_path TO tenant_042, public;Every query in that transaction now resolves table names against the tenant's schema first, then falls back to public for shared reference data (currencies, country codes, tax tables). The application code stays tenant-agnostic; the routing happens in one middleware layer.
3. Shared schema with a tenant_id column
All tenants share the same tables, separated only by a tenant_id column on every row. Cheapest to run and easiest to query across tenants for analytics — but the isolation now lives entirely in your WHERE clauses. One missing WHERE tenant_id = ? and you have a cross-tenant data leak.
If you go this route, never rely on developers remembering the filter. Enforce it at the database with Row-Level Security:
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON invoices
USING (tenant_id = current_setting('app.current_tenant')::int);Now the database itself refuses to return another tenant's rows, regardless of what the query says.
Why we usually land on schema-per-tenant
The honest answer is cost-to-isolation ratio. Database-per-tenant gives the best isolation but its infrastructure cost grows linearly with the customer count — exactly the wrong shape for a SaaS business. Shared-schema is cheapest but pushes a permanent security tax onto every query for the life of the product.
Schema-per-tenant keeps a single database instance — one thing to monitor, one thing to scale — while still giving each client a hard logical boundary. You get a flat infrastructure footprint with strict isolation compliance, which is the combination an ERP needs.
The trade-off you accept: schema migrations now have to iterate across every tenant schema. We handle that with a migration runner that loops the schema list and applies each change inside its own transaction, with a failure in one tenant never blocking the others.
The decision checklist
- Regulated, few, large tenants? Database-per-tenant.
- Many mid-sized tenants, cost-sensitive, need a clean boundary? Schema-per-tenant.
- Thousands of tiny tenants, heavy cross-tenant analytics? Shared schema with enforced RLS.
Pick the boundary on day one. It is the one architectural decision in an ERP that is genuinely expensive to reverse once real client data is in production.