Most decisions in a SaaS build are reversible. You can change a framework, redo a UI, swap a payment provider over a weekend if you have to. Tenancy is not one of those. It reaches into every query, every migration, every backup and every support conversation, and changing it later means moving live customer data between structures while the product keeps serving requests.
So it is worth spending a week on before the first table exists. Here is how we decide.
The three shapes
Shared tables with a tenant column. One database, one schema, every row carries a tenant_id, and isolation is enforced by the query layer or by Postgres row-level security.
Schema per tenant. One database, one schema per customer, identical table structures inside each.
Database per tenant. A separate database, often a separate instance, per customer.
They are usually presented as a spectrum from cheap-and-risky to expensive-and-safe. That framing is not quite right, because the costs land in different places rather than simply increasing.
What each actually costs you
Shared tables are cheapest to run and cheapest to migrate: one schema change, applied once, done. The cost is that isolation is now a property of your code being correct, on every single query, forever. One missing WHERE clause in one endpoint is a cross-tenant data leak, and it will be found by a customer rather than by you.
Row-level security moves that guarantee into the database, which is a large improvement — the policy is enforced even if the query forgets. We use RLS by default now and treat hand-filtered queries as the exception that needs justifying. It is not free: policies need testing like any other logic, and a misconfigured connection role bypasses them entirely.
Schema per tenant gives real separation and keeps one connection pool. The cost arrives at migration time. Two hundred tenants means two hundred schema migrations per release, which must be transactional per tenant, resumable, and monitored — because the failure mode is that tenant 147 is on the new code and the old schema. Teams underestimate this badly. The first migration is fine. The one that rewrites a large table at tenant 180 is not.
Database per tenant is the strongest isolation and the easiest story for an enterprise buyer or a data-residency requirement. It is also the most operational overhead per customer: connections, backups, monitoring, cost, and a noticeably slower path to onboarding a new tenant. It makes cross-tenant analytics genuinely hard — every "how many customers did X" question becomes a fan-out.
How we actually choose
Four questions, in order.
Does a contract or a regulator require physical separation? If yes, database per tenant for those customers and stop deliberating. This is a sales and compliance fact, not an engineering preference.
How many tenants, and how fast? Thousands of small tenants makes per-tenant databases untenable. Dozens of large ones makes it quite reasonable.
How different is tenant data volume? One tenant with 40 million rows and ninety with 40,000 will make shared tables painful — index bloat, vacuum behaviour, and one customer's load becoming everyone's latency. That is an argument for separating the outliers rather than everyone.
Who runs it at 3am? A small team cannot operate two hundred databases well, and an architecture nobody can operate is not safer, only differently unsafe.
For most products we build, the answer is shared tables with RLS, plus the ability to promote a single large or contractually-sensitive tenant to its own database later. That hybrid is worth designing for from day one even if you never use it.
The three things to do on day one regardless
Put tenant_id on every table, including the ones that "obviously" do not need it. Lookup tables get customised eventually. Adding the column later means backfilling and rewriting every query that touches it.
Never let application code choose the tenant from user input. It comes from the authenticated session, is set on the connection, and is enforced by policy. If a request can name its own tenant, you have built a lookup service for other people's data.
Write the tenant-isolation test before the second tenant exists. Create two tenants in a test, write data as each, and assert that each read returns only its own. Run it on every build. It is twenty lines and it is the single highest-value test in the suite.
The migration nobody plans for
If you do get it wrong, the move is doable but not cheap. Shared to per-schema means a copy per tenant, a cutover window per tenant, and a period where both shapes are live and the code handles either. Budget a quarter for a real product, not a sprint — and expect the cost to be dominated by the endpoints nobody remembered were tenant-aware, not by moving the bytes.
That asymmetry is the whole argument for spending the week up front.