Early in my career I worked at a company that was closing a big client. The client was not one company. It was about a hundred sub-companies under one parent. The app was built for one tenant, and we needed it to serve a hundred on the cheapest plan we could offer. So we gave each sub-company its own schema inside one SQL Server database. One database, a hundred schemas, one copy of the tables in each.

It was a bad design. I did not see it that way at the time. It hurt us in more than one place.

Performance was the worst of it. One web app served all hundred sub-companies, and every one of them hit the same database. The problem was not a slow query here or there. It was the server itself. One machine was carrying the load of a hundred tenants at once, and there is no query you can tune to fix that.

Deployment was the other part. We shipped schema changes with DACPAC, and every change had to reach a hundred schemas. So I wrote a tool that ran the DACPAC against one dbo schema, took the script it produced, rewrote dbo to each tenant’s code, and ran it a hundred times. The tool worked. The trouble was what happened when a deploy failed. It was surgical, one schema at a time, with no transaction around the hundred runs, so a failure halfway through left some schemas on the new version and some on the old, with no clean way to roll back. That cleanup landed on support.

We moved to Postgres later, mostly for the licensing cost, and that finally let us give each tenant its own database. By then, that shortcut had cost us more than it ever saved.

I have since shipped the other two models in production. A shared database with a tenant column on every row. A database for each tenant. So when a founder asks me which to pick, I am not reading off a comparison table. I have run all three, and been on call for all three. What I learned is that this is not really a technical decision. You pick a model for a business reason, and your team lives inside that choice for years.

TL;DR

  • There are three ways to keep tenants apart: one shared database with a tenant column, a schema for each tenant, or a database for each tenant. Wiring up any of them is a day of work.
  • Schema per tenant is the one I stopped using. One database server still carries everyone’s load, every deploy has to reach every schema, and you spend your schemas on tenants instead of on modules.
  • A shared database is the cheapest to run and the fastest to onboard. The framework filters every query by tenant for you, so the real risk is the queries that slip past the filter, and you cannot back up or restore one tenant on its own.
  • A database for each tenant gives you clean isolation, per-tenant backup and restore, and no noisy neighbors. The cost is that every migration and every new tenant becomes an operation you run and watch, not a command.
  • This is not really a technical decision. Choose on who your tenants are and what one tenant seeing another would cost you, then build the boundary so the model is a per-tenant choice. Put the big clients on their own database and let the long tail share one.

The three models

Strip back the words and there are three ways to keep tenants apart.

A shared database. One set of tables, every row carries a TenantId, and every query filters on it. Your tenants live together, and you keep them apart in code.

A schema for each tenant. One database, but every tenant gets its own schema and its own copy of the tables. They share a server, not tables.

A database for each tenant. Every tenant gets a database of their own. They share nothing except the application in front of them.

You will see these called pooled, bridge, and siloed. The labels do not help you much. What helps is knowing what each one asks of you once you have real customers. So I will go through them one at a time, with what each one costs once you are past the demo.

Schema per tenant

This is the one I no longer use. The story above was one project, but none of the pain was specific to it. It comes with the model.

The performance problem is structural. A schema for each tenant still puts every tenant on one database and one server. You can give each tenant their own tables and they will still compete for the same CPU and disk. Separate schemas do not give you separate machines.

The deployment problem is structural too. Every change has to reach every schema, with no transaction across them, so a failure partway through leaves some tenants migrated and some not, and nothing to roll back to. The tool I built could push a hundred schemas. It could not undo them.

The cost that took me longest to see is the quietest one. Schemas are a good way to organize a system. A clean pattern is one schema for each module, say orders, billing, and reporting, so the boundaries in your code show up in the database. Spend your schemas on tenants and you cannot use them for that. You take a tool meant for structure and spend it on isolation, and then you need a second way to draw the lines you just gave up.

There is a narrow case for it, and it is not small apps with a few tables. With only a handful of tenants, a database for each is simpler and gives you more. The honest case is the one I was actually in: an app built for a single tenant that suddenly has to serve many, where rewriting every query to filter by tenant is too big a job to take on right now. A schema for each tenant gets you there with almost no change to the application. That is a real reason, and it was ours.

It still cost us everything above. Modern Postgres even has tooling built for it now, like Citus and its schema-based sharding. Old SQL Server with DACPAC, where we met it, did not. The day we moved to Postgres, we finally did the thing we had skipped and added the tenant column. Even at its best it tops out in the low thousands of tenants, because every schema swells the database catalog and every migration runs against all of them. Past that, the tenant column belongs there from the start.

Shared database

This is the model you get when you add that tenant column from the start, and for most products it is the right one to begin with. One set of tables, a TenantId on every row, every tenant in the same place. It is the cheapest to run and the easiest to live with. Onboarding a tenant is an insert. A report across all your tenants is one query, not a pipeline. If you have a lot of small tenants, or you let people sign up self-serve, this is usually where you should start.

The worry everyone has is the obvious one. All the data sits in the same tables, so what stops one tenant from reading another’s?

You do not stop them by filtering every query by hand, the way most people assume. In EF Core you set a global query filter once, and every normal query gets WHERE TenantId = ... added for you, for free. The framework holds the line on the common path.

The leaks come from the queries that go around it. Raw SQL. A place where someone called IgnoreQueryFilters for a good reason and forgot to put the scope back. A DbContext opened in a background job or a message handler before the current tenant was set. An admin screen built to read across tenants on purpose, pointed at the wrong one. A cache key that left the tenant out, so one tenant is served another’s result. The filter handles almost every query you write. The ones that skip it are where the trouble starts.

The quieter problem is operational. In a shared database you cannot do anything to one tenant on its own. A tenant asks for a copy of their data, or a clean delete, or a reset back to zero, and none of it is an operation. It is a careful script against shared tables with a WHERE you have to get exactly right, while everyone else’s rows sit in the same place. You gave up the ability to pick up one tenant and put it down somewhere else.

A database for each tenant

This is the model that gives back what the shared database took away. With a database for each tenant, anything you do to one tenant stops at that tenant. Back one up, restore it to last week, wipe it clean, move a heavy one onto its own server. On a platform I built, that operational separation was the whole reason we went with it. The customer needed to run each tenant on its own, and a shared table cannot give you that.

None of this needs a second application. One app serves every tenant, and what changes per request is the connection string. You resolve the tenant at the edge, and that tells you which database to open. Usually it is the same server with a different database name. For a tenant big enough to need it, a different server entirely. That resolver becomes a real part of the system, and everything has to go through it, not just web requests but every background job and every migration. A context opened without it is pointed at the wrong tenant, or at nothing.

The cost is that everything you did once, you now do for every database. A schema change is not one migration, it is the same migration run against each tenant in turn, by a runner that tracks which databases are done and picks up the ones that failed. A failure stops at one tenant instead of spreading, which beats the shared-schema version, but you are still watching a job that has to touch all of them. Onboarding is not an insert either. Standing up a tenant means creating a database, running every migration into it, and seeding it before anyone can log in. On the platform I built that was an orchestration of about a dozen steps, and creating the database was only one of them.

The running costs are quieter, and they add up. Your app holds open connection pools to every database it talks to, so an instance serving a lot of tenants sits on a lot of connections, and you usually find the ceiling the hard way, in production, when a node runs out. Empty databases still cost money. The demo tenant nobody touches is on the bill until you put it in an elastic pool or on a tier that pauses when idle. And a report that used to be one query across all your tenants now has to visit every database and add the results up, so cross-tenant reporting becomes a pipeline you build on the side.

One more thing, and it matters for what comes next. Even with the databases fully separate, I kept the TenantId on every row and the filter on every read. The boundary already keeps tenants apart, so it looks redundant. I kept it for two reasons. It is cheap insurance for the day a connection string resolves to the wrong place. And it means a tenant is not stuck where it started. It can move from its own database into a shared one, or back, without changing the data model, because every row already knows whose it is.

Build it so you can change your mind

I learned this the expensive way: you do not have to pick one of these models for the whole platform. You can build the boundary so placement is a choice you make per tenant, and change.

The mechanism is the two things from the sections above, used together. Every row carries a TenantId, even inside a dedicated database, and resolving a tenant is really resolving a connection string. So a tenant’s home becomes configuration. The customer who needs isolation gets their own database. The trials and the long tail of small accounts share one database and stay apart by their TenantId. Same code, same queries. The only thing that changes per tenant is what the connection string points at.

If resolving a tenant is picking a connection string, then moving a tenant starts with changing it. That part is config. The data is the other half, and the data is the real work: you lift one tenant out of shared tables into its own database without dropping a write, then flip the string. Cheap to wire up. Real work to run.

It is not free to carry, either. Supporting both models means you build and keep the resolver, the per-tenant config, and the move tooling from the start, including for the tenants who never move. If you know you will only ever have ten enterprise customers, build a database for each and skip all of it. This earns its keep when you have a long tail and a few big accounts and can see that coming. It is a known pattern, not a trick I invented. Microsoft documents the same shape as its hybrid model, so you are not taking only my word for it.

That first project had none of this. We picked one model, locked ourselves in, and when it stopped fitting, the only way out was a re-platform. The plumbing that would have saved us is a day or two of work. Knowing which tenant to move, and when, and moving it without losing data, is the part that takes someone who has done it before.

So, which one?

Look at what each of these models takes to build. A tenant column and a query filter. A connection string that changes per tenant. A runner that applies migrations to every database. You could write all three in an afternoon, and today you can have an AI write them for you.

None of that was ever the hard part. That early project did not go wrong because someone fumbled a migration. It went wrong because we picked a model to fit a deal, and the model outlived the reasons we picked it, and unwinding it once real customers were on top of it cost far more than the shortcut ever saved. The hard part is making that call well, when the cheap option and the right option are not the same, and the bill does not arrive until years later.

That is judgment, and it did not get cheaper when the code did. The models are the easy part. Knowing which one you will not have to tear out in three years is the rest of the job.

The hard part was never the code.

FAQ

What are the three multi-tenant database models?

Shared database, schema per tenant, and database per tenant. In a shared database every tenant lives in the same tables and a TenantId column keeps them apart. Schema per tenant gives each tenant its own schema and its own copy of the tables inside one database. Database per tenant gives each tenant a database of its own. They are sometimes called pooled, bridge, and siloed.

Is a shared database or a database per tenant better for multi-tenant SaaS?

Neither is better in the abstract. A shared database is cheaper to run and faster to onboard, and it suits many small or self-serve tenants. A database per tenant gives clean isolation, per-tenant backup and restore, and no noisy neighbors, at the cost of running migrations and provisioning per database. Choose on who your tenants are and what one tenant seeing another would cost you, and remember you can mix the two.

What is the risk of a shared database in multi-tenancy?

The isolation lives in your queries, not the database. In EF Core a global query filter adds the tenant condition to every normal query for you, so the risk is the queries that go around it: raw SQL, a context opened before the tenant is set, an admin path that reads across tenants, or a cache key that forgot the tenant. You also cannot back up, restore, or delete one tenant on its own without surgery against shared tables.

When should you use schema per tenant?

Rarely. It puts every tenant on one database server, so they still compete for the same resources, and every schema change has to run against every schema. The honest case for it is retrofitting an app built for a single tenant into a multi-tenant one without rewriting every query, and even then it strains in the low thousands of tenants, where the catalog and the per-schema migrations start to drag. Past that, a shared database with a tenant column is the better tool.

How do database-per-tenant migrations work at scale?

Each tenant database is migrated on its own, so a schema change is the same migration run once per database by a tool that tracks which are done and retries the ones that fail. A failure stops at one tenant instead of spreading, but you are running and watching a job that touches all of them. Onboarding works the same way: it becomes a job that creates a database, migrates it, and seeds it, not a single insert.

Can you mix multi-tenant isolation models?

Yes, and it is a documented pattern. If every row carries a TenantId and resolving a tenant means resolving a connection string, then a tenant’s home is configuration. Big or regulated tenants get their own database, the long tail shares one, and a tenant can move between them as it grows. Microsoft documents the same approach as its hybrid model.