Building multi-tenant applications
Hello folks. I need some help with tech decision I'm about to make. I'm less experienced, so inputs from the more experienced folks will be invaluable.
I lead a very small team and we're in the very very early stage of the product we're building. It's meant to be a multi-tenant SaaS app, related to healthcare. We expect our customers to have their own users, activity logs and so on (multi-tenant). The tech decision I'm trying to make right now is which database to go with. I already setup postgres with our backend, so we isolate each org's data by postgres schema. But lately I've been thinking about the scalability of the system and after doing a little research, I'm finding that traditional sql databases are not that easy to scale, especially horizontally. I'd have to look at efficiently implementing sharding at that point. I'm tempted to forgo it for now as we literally have no users yet, but I also don't want to do something that would come back and cause the product harm in the future. I don't want to let my team down or incur unnecessary tech debt for everyone. This is the first time I am building such a service, that is, a multi-tenant application like this, so I'd like to know how best to approach this.
How are these types handled in the industry nowadays? I hear nosql dbs, like mongodb, are easier to scale horizontally, but I don't know.
Any insight will be deeply appreciated! 🙏
13 Replies
Hmm, it really depends on the database load you expect. An RDBMS worth its salt will scale pretty damn far for read operations for example, and there's caching and clustering that can help it scale further for read operations especially. If you need a ton of write operations it gets a lot harder to scale horizontally.
Also, even for write operations, scale is very much dependent on the type of data you're storing.
I've written some multi-tenant SaaS stuff in the past, and we had one system with over 2.5 million invoices in there that hummed along happily on a VM running on a Dell R310 with I think 32 gigs of RAM in the host? The web server had 4 and the database 8GB of RAM, and individual record query times were still well under 50ms with some useful indexes
there's also ways to scale horizontally in multi-tenant even if your RDMS doesn't necessarily support it. You say you're splitting by schema, but you could also potentially split by connection string, and have multiple independent database servers for larger data amounts
and I don't know your application of course, but the other thing we did with ours was just have multiple instances of the software set up and running. Each could run on its own set of VMs or even hardware, entirely independent of the others. Some customers requested it, out of some unfounded privacy concerns, but we also just had a couple different instances simply so a large customer could have access to their own admin section without seeing data they shouldn't.
That's incredible 😅
hmm, I'm not sure I understand what splitting by the connection string means 🤔 I currently just create a new postgres schema for each tenant and attach their tables to it. About 15 tables as it stands. I don't know if that counts as a lot of data 😅
the main thing that caused slowdowns was our RBAC system that was a little too flexible. There were a ton of joins on the main invoice list. But sharding or clustering or anything nosql could do wouldn't have solved that
You've got a connection to the database, right? That uses a connection string. You also must have a list of schema names for each tenant stored somewhere, to connect to the right schema
if you update the tenant list to store connection strings rather than just schema names, you could change the server IP and run two (or ten, or fifty) different database servers that don't even know of each other's existence
Yikes! The customers always throwing curveballs sometimes 😅 . I'm sorry, do you mean a couple different db instances or application or both, like a single-tenant application?
we had multiple instances of the entire application
Absolutely!
One of our SaaS suppliers at my current job does the same, they have different instances of their application, and the application itself supports multi-tenant or single-tenant workflows
Ohhh wait!! That's actually true!
it's got some implications if you want to do aggregate data analysis in the future, but it would let you scale very easily, and without really investing in a ton of planning and infrastructure before you know where your performance issues are going to be
we never expected the RBAC system to cause slowdowns, for example
Copy that!
Yeah that's interesting. I wouldn't imagine that could cause slowdowns. I'm sure it's more complicated and I might just be very naive (most likely to be frank 😅 ), but I mean, once the user has passed the auth check, you already know their role right? I'm curious what part of that system was causing slowdowns
we couldn't separate things at the database level, and one of our requirements was that users needed to have different levels of access to different lines in the invoice table depending on some parameters (like cost centers for example), but we also needed to show folks an overview of all invoices they had access to. It isn't anything super complicated, checking for one invoice took milliseconds, but once you start doing that for hundreds of thousands of invoices, especially with LIMIT values further on in the dataset, it could get quite slow
I mostly mention it because you can very rarely predict where the bottlenecks will be before you build the app. Your instinct here is right, to try to minimize future tech debt. The main recommendation I'd have at this point is to just generate a bunch of junk data using something like faker or whatever equivalent you have for your system, and test with what you'd currently consider large amounts of data.
Understood! Will do
Once again, thank you so much!
no worries, glad to help!