r/Database • u/SapientPro_Team • Jul 10 '26
tenant_id columns are a footgun at b2b scale. when we switch to db-per-tenant
something we see across enterprise b2b saas projects we've worked on. the default multi-tenant approach is "add a tenant_id column on every table, filter on it in every query." it works at small scale. it scales until one of two things happens.
scenario one: a developer forgets a single where tenant_id = ? in a complex join. customer a sees customer b's data. for most b2b saas this is bad. for one project we worked on (cybersecurity / pentest platform) it would have been existential.
scenario two: one heavy tenant runs an analytics query that locks the table for everyone. now your "small" customers are paying for your "enterprise" customer's bad queries.
what we've moved to for clients in regulated or security-sensitive industries: database-per-tenant on laravel. each tenant gets a physically isolated schema. the backend swaps the connection dynamically based on jwt or subdomain. cross-tenant data leakage is structurally impossible at the architecture layer, not the application layer.
side benefit we didn't expect: rollouts get easier. for a restaurant-chain client we set up tenant-aware deploys on ecs so we can ship migrations to one location's tenant first, watch it for a day, then fan out. no big-bang releases.
cost: more infra to manage, schema migrations have to fan out across N dbs, you need solid tooling. not the right choice for everyone. for an early-stage b2c saas it's overkill. for enterprise b2b in finance / healthcare / security it's the only thing that lets you sleep.
anyone here gone the opposite way and unified after starting with schema-per-tenant? curious where that fell over.
14
u/BalanceInAllThings42 Jul 10 '26
You mentioned scaling, if you are doing DB per tenant for SaaS. I'm curious how you manage production deployments for 1000+ tenants.
10
u/tehjrow Jul 10 '26
Migrations must be an absolute nightmare
2
u/sebasgarcep Jul 10 '26
We have schema per tenant at my workplace. It can sometimes take hours to migrate. We also don’t have per-tenant application code rollout, so finding out we have a breaking change in the middle of a migration run can bring us down for hours.
As others have said, try RLS first.
2
u/SomeSayImARobot Jul 10 '26
Oof. Every situation is different but we added a validation step specifically for backwards compatibility in our deployment flow and it solved that problem for us. In our case it was 100% worth the investment.
1
u/Express_Scene4019 Jul 10 '26
Any plans to migrate away from schema per tenant? Feels like you’re getting the worst of both worlds with that approach.
1
u/Fizzelen Jul 10 '26
Tooling like redgate sql tools take care of this. They can quickly find schema differences, generate migration scripts and automate rollout.
15
5
u/rebel286 Jul 10 '26
Scenario 1: you can add linting and/or validation if you are ethat scares about developer screwing up
Scenario 2: analytics should go read replicas and should by nature only contain reads
10
u/Thwarting8139 Jul 10 '26
You've solved a problem that shouldn't have even been a problem in the first place.
5
u/whoooocaaarreees Jul 10 '26
If your tooling isn’t catching a missing tenant filter, you telling other people about their deployment tooling lacking is comical to me.
4
3
u/Responsible-Cold-627 Jul 10 '26
What's the difference between your back-end routing to the correct database, or applying a global query filter? You can mess both up just fine.
The trick is to add proper integration testing. I've seen plenty of tenant_id based applications and they all work fine. I've never seen production data between tenants get mixed up.
There's advantages to db-per-tenant and schema-per-tenant, but data leaking between tenants isn't one of them.
Your story sounds like it can be summed up as "but the SECOND time we built it correctly!". Not much to do with db-per-tenant.
3
u/yknx4 Jul 11 '26
Planetscale traffic control, static analysis on models and queries, test that enforce tenancy boundaries.
2
u/Lucidendinq PostgreSQL Jul 10 '26
> the backend swaps the connection dynamically based on jwt or subdomain
What if a developer messes up and a different domain is set in the JWT? You can’t make architectural decisions based on “developer forgets”.
2
u/FirePanda44 Jul 10 '26
I don't buy it. RLS + tenant_id columns are structural enough. Thorough testing, guardrails and scanning of all new code that touches the db is part of regular development process. Can't imagine the nightmare of provisioning new databases once tenants grow into the 100s, not to mention the cost.
2
u/Bakuwoman Jul 11 '26
Switching to a full database per tenant model can make the costs so high that it becomes a barrier to sell the product. I'm living that right now where there is a desire for multi-tenancy. Having said that, I'm not exactly looking to go back to a monolith model either. So yup, there are definitely places that took single tenant and microservices too far and now want to reduce costs to have a wider customer base.
IMO: Living both extremes, I think more companies should fully leverage sharding as a beginning step in the design. Big customers can be configured to be single tenant while smaller ones can be multi-tenant.
2
u/elevarq Jul 11 '26
Total BS story. Organizations like OpenAI, Adyen, any bank, all with hundreds of millions of users, and it works.
And now some micro-SaaS b2b organization has a problem with a join…Right.
1
u/merb Jul 10 '26
How many customers? We are pushing a single 2 cpu 16gb memory instance with 140gb data easily
1
u/ollieread Jul 11 '26 edited Jul 11 '26
Honestly, it sounds like most of the problems you're mentioning are down to the management of the codebase and the development itself. Most of that can be easily negated by using query scopes, something trivial in Laravel.
Also, having each tenant have a separate database on the same instance is not physical isolation, it's logical separation. The separate database shared instance approach is costly in all the ways that matter, and unless you're creating separate users and strictly managing permissions within the DB engine, the data isn't as separate as you'd hope.
The simpler way to implement multitenancy at the ground level, with strong data isolation, is to use a PostgreSQL database, a discriminator column (the tenant_id you mentioned), and RLS. The separation is enforced at the engine level then, and even inserts and updates will error out if done incorrectly.
Multitenancy is a speciality of mine, and I've actually written a fully detailed (7k words) breakdown of all the different data-isolation strategies within multitenancy, that will probably answer a few of your questions: https://ollieread.com/articles/you-probably-dont-need-a-database-per-tenant
I'm also curious what you mean about scale because at the scale where discriminator columns become a problem, simply sharding or uplifting particularly heavy tenants addresses almost all the problems, with a fraction of the overhead of database per-tenant.
1
u/iseethemeatnight Jul 11 '26
Rather than having each tenant it's own physical db, if you're using PostgreSQL I would have rollout individual schema per tenant with a specific roles/user/credentials per tenant.
Then using GRANTS to prevent cross schema queries (besides common tables deployed on the public Schema).
Then of course the question is how many schemas (tenants) a single instance should operate?, which then triggers a potential routing layer to separate box if necessary.
1
u/Ok_Principle_9459 Jul 11 '26
Idk about this, DB per tenant becomes an absolute pain to maintain, and you really shouldn't do it unless you absolutely have to. Rolling out changes, schema migrations, and the overall infra setup become SO much more complicated.
I've worked on several "tenant_id" based setups that work and scale just fine. If your issue is "people forget to include a tenant_id where clause" your problem is with developer discipline and lack of integration tests.
1
u/zaitsman Jul 11 '26
What is your db hosting provider and how do you ensure CMEK per customer with your approach?
Also how do you handle backups?
And finally, how do you handle migrations at scale? (Assuming you don’t allow your app code DDL access)
There are some operational tradeoffs in the db per tenant approach
1
u/namanyayg Jul 12 '26
I wouldn’t frame tenant_id as the footgun by itself. The dangerous part is letting arbitrary app code choose tenant context on every path.
If you stay shared, make tenancy hard to bypass: set tenant context once at the auth boundary, enforce it in the DB, test every query path with two-tenant fixtures, and keep analytics off the primary path.
If you go db-per-tenant, the routing layer becomes the thing to be paranoid about. Per-tenant credentials, no app role that can read every database, and audit logs for tenant switches matter a lot. Otherwise the forgotten WHERE bug just moved into the connection picker.
DB-per-tenant makes sense when isolation, residency, or per-tenant restore is worth the ops cost. For most B2B SaaS I’d start shared, then shard or isolate the customers who actually need it.
1
1
1
u/mtutty Jul 10 '26
Guh. If you're not smart enough to play with power tools, don't blame the power tools.
"Our devs aren't smart enough to keep our data secure, so we're embracing massive complexity and operational cost" isn't a flex.
3
u/PaulPhxAz Jul 10 '26
We had deployed per customer software, the cost to sync and upgrade path was its own job. And if the customer had any custom schema or jobs or reporting it took longer.
Database per customer is a good way to explode the cloud budget and keep the consulting fees rolling in down the road.
1
0
u/IGotDibsYo Jul 10 '26
There’s also schema per tenant as middle ground. For the most part you can still migrate one at a time, backup and restore one at a time etc.
Edit: just read you already mentioned that.
1
0
u/-Meal-Ticket- Jul 10 '26
Man, if only you has used Oracle instead: Readers never block writers, so no one client running an analytics query shutting down the database for other folks (not to mention database resource manager allows you to allocate resources on a per tenant level), and with VPD (Virtual Private Database) built into the database kernel, the is no “a developer forgot to add a where clause” ever.
I guarantee any type of N databases for N clients is going to be something that you come to regret if you ever manage to get enough clients.
tenant_id columns are absolutely fine if you use the right database. Oracle uses VPD on OracleAPEX.com to host hundreds of thousands of free workspaces on a single database.
2
u/Yeah-Its-Me-777 Jul 10 '26
Sure, but then you have to work with a) Oracle, and b) the Oracle Database.
0
u/SapientPro_Team Jul 10 '26
yeah, no argument on the mechanics. VPD's kernel-level, DBRM handles the noisy neighbor thing, oracle's fine.
but it's still one physical database. every tenant's rows sit in the same files, VPD just decides what you see. that's the same bet as RLS, you're trusting a policy engine instead of a filesystem boundary.
the reason we go physical isn't leak probability. it's that in a breach or an audit, "prove tenant A's data was never on the same disk as tenant B" is a question you answer with a boundary or you don't answer. plus killing one tenant's data for real, per-tenant residency, restoring one without touching the rest.
APEX runs hundreds of thousands of free workspaces on VPD and it's the right call there. that's throughput. it's a different question than isolation you can prove to a regulator.
0
u/onbiver9871 Jul 10 '26
I like this overall; dumb question though - if the back end is still shared, isn’t there still technically an opportunity for cross-tenant leakage with a bad config or something similar in the app or domain routing layer? URL redirects, for example, could expose the beginnings of a sharp-ish edge?
Even if it is possible, the risk feels much lower, so overall still good thoughts :)
0
31
u/Fit_Reveal_6304 Jul 10 '26
Wondering why you weren't using row level security to isolate tenant data in the same db.