How do you support multi-tenancy in your SaaS?
#tech-questions
For many B2B applications it's a typical case to create an "app" per-customer, and then within that app create users that belong to a customer's organization.
My question is how do you go about separating out data that belongs to different organizations?
Our current approach at OneBar is to create separate schemas/indexes/folders in each storage backend we use (postgres, elasticsearch, etc). It gives us some assurance that no matter what we do in our code, we won't accidentally leak one customer data to another customer, however, it seems to be quite a huge performance burden. Each new account slows down our infrastructure and given a very low typical conversion rate of trial -> paid customers, I'm imagining it would be quite expensive for us to support a heavy stream of new signups over time.
I'm curious how do other people here go about this problem. Do you just throw everything into one huge database or do you do something similar to what we do? In that case, how well does it scale for you?
Been doing this a long time, and even with something as sensitive as people's health insurance information, we've always had the data all sitting next to each other.
The only exception was my first startup experience, and I did venture down the path of a single database per customer for the first startup I was a part of. That was definitely a bad approach, especially with RDBMS where we needed to make sure all of the databases were in sync schema wise. We righted that ship pretty early on.
Knowing a bit about your particular service, I'd say that I'd only go as far as having a separate bucket out in S3 for each team's assets, but nothing more granular than that. Database / data store would be the usual deal, having a team ID or whatever associated with the data.
As an engineer, I will say that your concern of data bleeding isn't entirely unfounded, but it's probably a rarer circumstance than you think. Code reviews and unit testing can probably eliminate 99% of any sort of snafu that could actually occur.
If the infrastructure choices now are slowing you down right now, I'd definitely address them, but if you're concerned with the future problems with your tech choices now, I'd probably try to defer them until you're at least ramen profitable.
Thanks for sharing your experience, Josh!
I'm kind of starting feeling the same way about what we've already made. Schema migrations are slower with each new signup and the worst part is that the signup itself is taking 2-3 minutes.
Yeah, a couple of minutes to get up and running could be the different between a customer or not.
Obviously don't know much about your infrastructure, so hopefully it won't take much to untangle things so you can get back to worrying about building features and not infrastructure :)
Just want to chime in to support the one DB for all your users approach. I've done the one-db-per-customer approach, and it becomes really hard to maintain past a certain size. It's also prone to ideas relying on different DB schemas, and different codebases per customers which will be a huge PITA in the long run.
I agree with @joshtronic that you can mitigate a lot of the risks with good testing and code reviews.
Another way to think about it: the less complex your deployments are, the more you can focus on quality and security of your code. If releasing a change to 50 customers require 50 custom deployments then you'll spend much more time debugging what's going on with each problem.
Another thing to consider is that if you do have a security issue (it can be a badly configured permission, etc) then it'll take you X deployments where X is the number of users, vs 1 in the case of a shared code and DB.
This comment was deleted 5 years ago.
Recommendation from Microsoft: https://docs.microsoft.com/en-us/azure/sql-database/saas-tenancy-app-design-patterns
Really depends on your needs, you can always start small and migrate as you grow
This comment was deleted 5 years ago.
So, in the past, we've just done all this in the same db, NBD.
But, that's missing the point. The point is, this all probably doesn't really matter. You're talking about scaling, but you're at just one paying customer right now. At this point, any attention you pay to scaling and infrastructure is at the expense of working on product-market fit and customer acquisition.
Anyway, the takeaway is this: probably shouldn't really worry about this, as long as it works reasonably well enough for today. Worrying about scaling at this point is putting the cart WAY before the horse. Work on getting all those signups, then figure out how to scale it.
Thanks for replying @maderalabs!
The reason why I asked this is basically because we already feel the pain of a schema-per-customer approach. We'd like to do more marketing and grow the number of daily signups, but each signups is taking 2-3 minutes (super frustrating experience) and is actually non-free for us. I just recently had to double the size of our elasticsearch server because it was crashing due to insane number of shards it had to host. Basically, we're spending ~$300/month for storing less than 100mb of data and I feel that this is not how things should be even at the beginning.
Go back to a single DB for all, doesn't worth the struggle.
Thanks everyone for comments! I can see that people agree on the single DB pretty much unanimously. After some thinking of my own I'm also on the side of the single DB approach. The only problem is that we already went the multi-schema way and it seems like a great refactoring is ahead of us :)
Been doing this multi-tenancy quite often and I'll side with those who think you definitely shouldn't go the "one DB per customer" way. I worked for a company that did that (before I arrived!) and it killed them in the same way it's killing you.
I also worked for a ultra-high-security company (anti-terrorism, airports, police and such) and they had both kinds of systems but 1) they had infinite resources and 2) they absolutely automated everything and 3) they were dealing with pretty much the most sensitive data that I can think of.
I'm building a multi-tenant system right now and I guaranty it will have a single DB.
I agree with the other comments: single database all the way. You can use an
account_idcolumn to query by account for all resources. Write simple integration tests to ensure you don't leak data across accounts. What you've outlined is over-engineering and it's already coming back to bite you and you're only at your first customer.If I go down the 'single DB' path, do I have to create a separate 'schema' for each tenant?
Single DB for all customers. Any other thing you are doing is an unnecessary headache
The simplest way to do this with the least amount of refactoring is to include an "organization id" for each high-level object. For example, an invoice is split into many tables in your relational database, but you only need the "organization id" on the main table that joins all the records together. Next is for each request a user makes they provide the organization id and include it as an AND clause. You already have this information because I don't see another way you could be picking schemas. Example from Microsoft: https://docs.microsoft.com/en-us/azure/sql-database/saas-tenancy-elastic-tools-multi-tenant-row-level-security
Hey, I wrote a pretty technical and in depth blog series on this. This is the first article.
https://blog.checklyhq.com/building-a-multi-tenant-saas-data-model/
it has an example database schema and how to handle users and accounts.
There are more blog posts that dive into plans and features and all that kind of stuff.
Up till now, It works really well.
Great article, reading this 2 years later. Thank you!
This is awesome, Tim! Thanks for writing/sharing!
This comment was deleted 4 years ago.