Multi-Tenant SaaS Architecture: Database Isolation Models Compared

The three isolation models

The shared model places all tenants in the same tables with a tenant identifier on every row. It uses infrastructure efficiently, keeps migrations to a single schema change and makes cross-tenant analytics straightforward, at the cost of requiring absolute discipline in scoping every query.

Schema per tenant gives each tenant its own set of tables within one database. It provides clearer separation and allows per-tenant schema customisation, but migrations must run across every schema, and databases with thousands of schemas become slow to introspect and back up.

Database per tenant provides the strongest isolation, including independent backup, restore, encryption keys and residency. It is also the most expensive per tenant and the most complex to operate, because connection management, migrations and observability all multiply by tenant count.

Enforcement is what makes sharing safe

If tenant scoping depends on every developer remembering to add a where clause, the model is unsafe. Enforcement belongs in the data access layer so that a query cannot be issued without a tenant context, and in the database so that a scoping mistake is rejected rather than silently returning another tenant's data.

Row level security in PostgreSQL is the strongest practical control: policies restrict rows to the current tenant, and the application sets the tenant context on each connection. A query that forgets scoping then returns nothing rather than everything.

Add automated tests that attempt cross-tenant access for every endpoint, using two real tenants, and fail the build on success. This is the only reliable way to keep the guarantee as the codebase grows.

• Enforce tenant scope in the data access layer, not in feature code.
• Use database row level security with a per-connection tenant context.
• Test cross-tenant access attempts for every endpoint in CI.
• Never allow a query path that can bypass tenant scoping.

Cost, scale and noisy neighbours

In shared models the infrastructure cost per tenant falls as tenant count rises, which is exactly what makes small-tenant SaaS viable. In database-per-tenant the cost is roughly linear in tenant count, so it only makes sense when tenants pay enough to cover a dedicated database.

A common and effective pattern is tiered isolation: shared tables for the long tail of small tenants, and a dedicated database for enterprise tenants who contract for it. The application must abstract the storage location so that a tenant can be migrated between tiers, which is a design requirement rather than an afterthought.

Noisy neighbours are the hidden cost of sharing. One tenant running an expensive report can degrade everyone, so per-tenant rate limits, statement timeouts, query cost limits and separate queues for background work are necessary rather than optional.

Operations and data residency

Every model needs a tenant registry that records the tenant, its isolation tier, its region and its encryption context. Without it, routing, migration and compliance reporting all become manual.

Data residency requirements frequently override cost considerations. If tenants in a region require data to remain in that region, the architecture needs regional deployment with routing by tenant, which forces the isolation decision regardless of which model would otherwise be cheapest.

Finally, plan tenant deletion properly. Deleting a tenant must remove all of its data, including backups, derived analytics and search indexes, within a contractual window. In shared models this is a filtered delete that must be provably complete, which is another argument for a registry that can enumerate every store holding tenant data.

• Maintain a tenant registry with tier, region and encryption context.
• Route by tenant when residency requirements apply.
• Enumerate every data store holding tenant data for deletion.
• Prove deletion completeness within the contractual window.