Multi-tenancy: how to choose before it's expensive to change
Shared schema, database-per-tenant, or sharded? The deciding factor isn't cost - it's what happens when one customer needs something the others don't.
7 min read
The multi-tenancy model you pick determines how your data is stored, and Microsoft's own guidance says the quiet part plainly: "switching to a different model later is sometimes costly." The right way to choose isn't by comparing infrastructure bills — it's by asking what has to happen when one customer needs something the other thousand don't. Restore their data to Tuesday. Throttle them without touching anyone else. Prove to their auditor that their records are physically separate. Those questions decide the architecture. Cost follows.
Why this matters now
This is a decision most teams make implicitly, in week one, by writing the first migration — and it's one of the few architectural choices that gets harder to reverse every month you operate.
Microsoft's multi-tenant SaaS patterns guidance for Azure SQL is unusually concrete about the trade-offs, and its comparison table is worth internalising:
| Standalone app | Database-per-tenant | Sharded multitenant | |
|---|---|---|---|
| Scale | Medium (1–100s) | High (1–100,000s) | Unlimited (1–1,000,000s) |
| Tenant isolation | High | High | Low |
| Cost per tenant | High — sized for peaks | Low — pooled | Lowest |
| Dev complexity | Low | Low | Medium — sharding |
| Operational complexity | Low, but hard at scale | Low–medium | Low, but per-tenant ops are hard |
Two rows deserve more attention than they usually get. Standalone-per-tenant is the most expensive option, because each deployment must be provisioned for its own peak load and can't share pooled capacity. And sharded multitenant "necessarily sacrifices tenant isolation" — that's not a caveat, it's the definition. Every tenant's data sits in the same tables, and only your query correctness keeps it apart.
The mechanism: it's about the blast radius of a per-tenant operation
Here's the reframe that makes this decision tractable. Stop comparing steady-state costs and start listing the operations you'll need to perform on one tenant, without touching the others:
- Restore a single tenant to a point in time. With database-per-tenant this is restoring one small database from a backup — no impact on anyone else. In a single shared database, Microsoft notes these operations "are more complex to implement" and "at scale might become unacceptably slow." You cannot roll back one customer's bad import without rolling back everyone's.
- Contain a noisy neighbour. A shared database has "no built-in way to monitor or manage the use of these resources by an individual tenant." One tenant's runaway report degrades everyone, and your first line of defence is application-level metering you have to build yourself.
- Meet one tenant's compliance requirement. When an enterprise buyer asks for physically separated data, residency in a specific region, or their own encryption key, shared schema gives you no answer that doesn't involve re-architecting.
- Customise one tenant's schema. Single-tenant databases let you add a field or an index for one customer without affecting others. Shared schema makes every customisation a change to everyone's tables.
Notice that all four are rare in year one and routine by year three. That's precisely why the decision feels free when you make it and expensive when it bites.
Shared schema optimises for the cost of a thousand identical tenants. Database-per-tenant optimises for the cost of one tenant being different. Your roadmap tells you which problem you'll actually have.
The counterweight is real, and Microsoft's index example makes it vivid: a single 1,000-tenant database with 20 indexes becomes 20,000 indexes when split into 1,000 single-tenant databases. Per-tenant isolation multiplies every operational object you own — backups, migrations, connection pools, monitoring. That's manageable with modern tooling and automation. It is not free.
How to actually decide
- Start from your buyer, not your bill. Selling to consumers or small teams at low ACV? Shared schema, almost certainly. Selling into regulated enterprises where procurement asks about data isolation? You will end up with per-tenant databases; building shared-schema first just means doing it twice.
- Put
tenant_idin every table from day one — even if you never shard. It costs nothing now and it's the column that keeps every future option open, including the migration to a sharded model. Retrofitting it across a mature schema is the expensive version of this conversation. - If you go shared schema, enforce isolation in the database, not in application code. Row-level security scopes results to a single tenant at the engine, so a forgotten
WHERE tenant_id = ?fails closed instead of leaking. Relying on every developer remembering the filter forever is not a security model. - Plan for hybrid, because that's where you'll land. The mature pattern is pooled shared-schema infrastructure for free and standard tiers, with isolated databases for enterprise tenants who need — and pay for — the isolation. Microsoft's hybrid model puts
tenant_idin every database so a tenant can be moved between shared and dedicated as they upgrade. Design the move path before you need it. - Cost-model at your third-year tenant count, not your current one. Standalone-per-tenant looks reasonable at five customers and absurd at five hundred. Sharded multitenant looks efficient until one customer is 40% of your load.
- Write down which operations you're accepting as hard. Every model makes something painful. Choosing deliberately means you know what you've traded and can price it — into your enterprise tier, your SLA, or your roadmap.
Our opinion
The most common mistake we see isn't picking the wrong model — it's picking one by default and discovering it later. The first migration gets written, tenant_id gets added to some tables and not others, and eighteen months on the answer to "can you restore just our data?" is an engineering project nobody scoped.
We'd also push back on the instinct to reach for per-tenant databases early as a hedge. Isolation isn't free: it multiplies your migration surface, your backup jobs, your connection management, and your monitoring, and most early-stage products don't have the operational maturity to absorb that while also finding product-market fit. Start pooled, keep the tenant_id discipline absolute, and buy isolation when a customer is paying for it.
The strongest version of this argument is that the decision is genuinely reversible if you design for the move. A tenant-to-database catalog plus a universal tenant_id turns "which model did we choose?" into "which model is this tenant on today?" — and that's a much better question to be able to answer.
How Ashvara helps
We design the data layer around the per-tenant operations a product will actually need — isolation enforced at the database rather than by convention, a catalog that maps tenants to storage, and a documented path for promoting a tenant to their own database when a contract requires it. It's the same instinct we bring to designing an API that lasts: the cheap decision and the reversible decision are usually not the same one.
That's core backend and API work for us, and it's much cheaper to get right before launch than after. If you're about to write your first migration — or you've got a shared-schema product and an enterprise buyer asking hard questions — tell us what you're building and we'll map the options against your roadmap.
Sources: Microsoft Learn, "Multitenant SaaS patterns — Azure SQL Database," covering tenancy model criteria, the standalone / database-per-tenant / sharded comparison, and hybrid tenancy (learn.microsoft.com).