Three Ways to Isolate a Tenant in .NET and PostgreSQL

Every multi-tenant system answers one question before all others: how far apart do tenants live? Answer it too casually and you will rediscover it later as a compliance finding, a noisy-neighbour incident, or a migration that takes a weekend per customer.

This series walks through a working platform I built to explore that question properly: a .NET 9 + PostgreSQL system where a tenant-management control plane provisions tenants under any of the three classic isolation strategies, and a single set of EF Core migrations serves all of them. This first part frames the three strategies and the domain model that lets one codebase hold them together.

The three distances

1. Database-per-tenant

Each tenant gets its own PostgreSQL database, its own connection string, its own backups. Provisioning issues a real CREATE DATABASE, installs the extensions the app needs (uuid-ossp, ltree, pg_trgm), and runs migrations against the fresh database.

• Strongest isolation. Backup, restore, and delete one tenant without touching the rest. The story regulators like to hear.
• Costs scale linearly. Connection pools, maintenance windows and migration runs multiply by tenant count.

2. Schema-per-tenant

Tenants share a database but each owns a PostgreSQL schema (tenant_a1b2c3d4), with the application setting the schema at context-creation time. Data is separated by namespace, not by server.

• Good isolation-to-cost ratio. One database to run, but no WHERE TenantId = ... discipline needed and no accidental cross-tenant joins.
• The hard part is migrations. EF Core really wants one model and one __EFMigrationsHistory. Making one migration set apply per-schema is the technical heart of this series.

3. Shared database, capacity-managed groups

The pragmatic middle: schema-based tenants are packed into shared “database groups”, each group holding up to a configured maximum before the platform spins up the next one. In the domain model that is an explicit aggregate:

public class DatabaseGroup : AuditableEntity
{
    public string Name { get; private set; }
    public int MaxTenants { get; private set; }   // default 50
    public int TenantCount { get; private set; }

    public bool HasCapacity() => TenantCount < MaxTenants;
    public void IncrementTenantCount() => TenantCount++;
}

Provisioning asks the group allocator for a connection string; when every group is full, a new database is created and becomes the open group. Tenants get schema-level separation, the platform gets a bounded number of databases, and cost stops scaling per-tenant.

Modelling the choice instead of hard-wiring it

Most codebases bake one strategy into every query. The alternative is to make isolation a property of the tenant, decided at provisioning time:

public enum TenantIsolationType
{
    SchemaBased = 1,       // schema in a shared/grouped database
    DatabasePerTenant = 2  // dedicated database
}

Alongside the Tenant aggregate, a TenantConnection entity persists what provisioning resolved: the database name, the schema name, the (encrypted) connection string and the isolation type. From that point on, the data plane does not care which strategy a tenant uses — it just asks the connection provider for “the connection and schema for tenant X” and gets a cached answer.

That indirection is what lets the platform host a compliance-sensitive enterprise tenant on its own database and a thousand small tenants packed fifty-to-a-database, from the same codebase and the same admin console.

Choosing between them

The honest answer for most SaaS products is “more than one of these, eventually”. Which is exactly why the platform treats it as data, not architecture.

Where the series goes next

The interesting engineering is in making EF Core cooperate, and that is what the rest of the series covers:

1. Three ways to isolate a tenant — this post.
2. Resolving the tenant on every request — header, route, subdomain and query-string resolution, middleware, and connection caching.
3. Making EF Core migrations schema-aware — a schema-parameterised DbContext, per-schema __EFMigrationsHistory, and replacing EF's internal IMigrationsAssembly so one migration class provisions any schema.
4. The model-cache trap — why exactly one schema migrates and the rest silently don't, and the IModelCacheKeyFactory fix.
5. Provisioning tenants at runtime — creating databases and schemas on demand safely, with validated and quoted identifiers.
6. A control plane needs its own domain model — tenants, database groups, and isolation as data rather than architecture.
7. Tenant offboarding — soft delete, purge and restore, and the order they have to happen in.
8. Rolling out migrations across a tenant fleet — fan-out, ordering, and what to do when tenant 47 of 200 fails.
9. The PostgreSQL payoff — ltree hierarchies and jsonb payloads with GiST/GIN indexes.
10. A retrospective — what I'd keep and what I'd do differently.

If you have ever typed HasDefaultSchema and wondered why only your first tenant got the new table, part 4 is for you.