Hi all,
I have a question, right now I am building a Saas product and I am confused about the database implementation.
Should I keep only one database for each customer (subscriber) or create one database for each customer (and monitor each one with some monitoring software)?
I want to read your answers: ')
Thanks in advance.
Don't have multiple databases per a customer. Store all customers and their data in a single database. If you have multiple databases per a customer it will be a maintenance/upgrade/backup nightmare.
Just have different databases for your different environments, e.g. database for production, separate database for staging and separate database for dev etc
I've deal with this a couple of times. These are your options IMHO:
One database schema per customer -> Hard to maintain, upgrade, backup, monitorization, etc. Unless you have a DBA team.
One database, N table per customer -> This is a more flexible option since you only have one schema to backup and you will be keeping customer's data apart in different tables (maybe using customer id at the end o the table name like tbl_sales_445 or use native table partitions). You can also decide wich table to partition and which ones you don't.
One database, customer ID column on each table (if needed) -> This is the best option in my experience but you need to take a few things into consideration:
I'm more than glad to take a look and give you more advice if you need it.
You can schedule a free call with me at https://getadvice.github.io/adriano.galello.html
I think you might be talking about "multi tenancy". In which case there was an excellent discussion on HN a few months ago:
https://news.ycombinator.com/item?id=23305111
In a nutshell, the overall sentiment was, don't do it. Even the author of a well-known Rails gem that enables multi tenancy has migrated away from this approach:
https://influitive.io/our-multi-tenancy-journey-with-postgres-schemas-and-apartment-6ecda151a21f?gi=e7023756aa3e
I've created and worked with several companies that used both approach. I have scaled a db-per-customer setup to nearly 2,000 customers without issue. It can be done. It requires careful planning. I've also had thousands of customers on a single db. It can be done. It also requires careful planning.
Whether or not you want to put customers into their own db really depends on customer requirements and your business model. Here are some questions you should ask before making this decision:
If the answer to the first 3 questions is yes, and the 4th is no, then pretty much always cram all your customers into a single database. If your answers are any different then that, then you need to seriously consider database-per-customer.
Hope this helps!
If you don't have too, don't make multiple DBs... (aside from dev, test, staging, prod)
Especially not per customer.
You might do something per datatype if you run different things, like a 3rd party.. (like wordpress, site analytics vs your main product)
In huge companies, DBs might be split per region or just to shard... but if you don't have at least 10k customers and 100 eng. don't even think about things like that.
You normally have a customers table, and whatever other table has customer_id....
you might want to read a bit about DB design..
(there are cases of special requirements.. but than you wouldn't be asking that here..)
I worked with a startup years ago that was doing this for about 20 - 50 customers (each one was a local business). It was already causing problems... and they were going to bring in hundreds more soon.
It took a pretty complex refactoring for me to update all of the code to use one merged database (over a few months) while the rest of the team kept developing the software and I pulled in their changes. Once that was done we were able to deploy the updated code pretty seamlessly and it solved a bunch of issues.
Some reasons it worked better:
Data wants to be messy. When it's all in one place it's easier to restrict the format and catch any data that doesn't fit that. There's no chance that your code will break because one of the databases doesn't follow the format. When you make changes they will apply to everyone at once and you'll see easily if the existing data conflicts with them.
Queries and reports for all customers (in the admin) were a lot easier to do and only had to be run once.
There are a lot of optimization opportunities. There were some concerns about whether the MySQL database could handle bigger tables but since I've worked with databases that hold hundreds of millions of rows I knew how to make that run fast. When you use the database engine right it can handle a lot of data. On the other hand if you have tens of thousands of tables to manage that can cause performance problems.
There are some downsides including:
There could be a security issue any time you have a query that's not properly filtered. I modified the test runner for the code so it would detect this and give a warning (except for a handful of queries that were actually allowed to access all customer data).
If you screw up the database, that's all of your customer data. But you need to have good backups anyways. And in some ways it can make restoring data easier (apart from the fact that you're dealing with larger tables).
Personally I would put it all in one database unless there are some very strong and specific reasons not to do that!
Having worked on both types of systems, I think it depends on your requirements. When we were dealing with point of sale/back office SAAS product, we chose multi-tenancy. It allowed us to enforce version licensing without having to worry about Db changes being compatible with prior releases, on an individual tenant basis. We had over 3000 tenants (yes, over 3000 databases), some with millions of records others with 10's of thousands.
All the complaints people bring up about complexity are valid, but we managed it with a team 5 and it didn't require nights and weekends - we just took advantage of the tools available to us, and some of the early choices made in the design of the system turned out to be very helpful.
The current system I work on uses a single database, but we chose to use a row-level security implementation as the first step in enforcing data segregation per tenant.
Everything should be in one database only, just use different tables for different things like customers, transactions, etc...
I think one database per customer is overkill.
One schema per customer, on the other hand is interesting. In some databases, like Postgres, this is an option. You get the data isolation of having multiple database without the maintenance nightmare.
A good use case for this is multitenancy (when a "customer" or an "account" has several users under it). Having customers isolated by schemas will easily prevent, for example, one tenant's users from accessing or changing data of another tenant.
So, for multitenancy, I think data isolation of is something to think of. A good post explaining the multitenancy options is here: https://vladmihalcea.com/database-multitenancy/
If we're not talking multitenancy however, then I agree with my fellow indiehackers. It's too much a hassle for no tangible benefit.
From a Product perspective, it depends:
I would avoid adding complexity unless it's a must-have for your product, if it's a must have then it's table stakes to get into the game. And also presents a barrier to entry competitively.
But I suspect if you're asking the question here, it's not. So go with whatever is simpler, and perhaps look at something like Row Level Security or the like on a shared database.
YMMV, I have some very limited experience in this. Good luck!
I see everyone answers based on different context, I guess the leading questions should be around:
..
100% depends on your pricing. If you’re selling a SaaS at $10 a month there is no way you could even afford to have each customer on its own DB. Now on the flip side if you have an enterprise SaaS selling for $10k a month, it’s probably a lot easier to separate the clients data on their own servers.
I’m guessing you’re probably between the two. So you’ll want a single database, eventually you’ll grow and you’ll need to add more. But that can wait for a long time.
I agree with everyone who commented before. I don't recommend doing it. In particular, imagine some of your customers database version is outdated. It is a nightmare to manage a separate database. That being said managing everyone's data in one central place also has some downsides. You have start thinking about how to tune database performance as you have more customers. If I were you, I would choose having one central database.
It's a very vague question, so all I can give you is a very vague answer:
It's a nightmare to manage one DB/customer once you have more than a handful customers (speaking from experience). Don't do it unless they pay you enough for that effort and they explicitly ask for it.
Tell us more about what you're trying to achieve :)
This comment was deleted a year ago.
My sentiments, exactly - very confusing.