8
15 Comments

Should I keep only one database for each customer in a Saas Product?

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.

  1. 9

    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

  2. 4

    I've deal with this a couple of times. These are your options IMHO:

    1. One database schema per customer -> Hard to maintain, upgrade, backup, monitorization, etc. Unless you have a DBA team.

    2. 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.

    3. 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:

    • Make sure you implement a middleware in your code to alter your queries and only show data related to the customer making the request. This should be AUTOMATICALLY done so you can forget about it and prevent any leaks.
    • Make sure you choose the right indexes for your table. Using a customer ID as an index is a very bad idea because the cardinality will be very low.

    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

  3. 4

    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

  4. 2

    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:

    • Can all customers always accept upgrades at the same time? Depending on your model and price point, and what kind of problem you solve, you may be dealing with customers that demand control over their upgrade timing. This can make multi-db easier than single-db.
    • Will customers accept co-mingling of data? Again, depending on your customer and their use case, they may demand logical (and in some extreme cases physical!) separation of data. This really forces your hand into multi-db.
    • Do you plan to offer an on-prem "enterprise" version of your software? You can do enterprise with a single-db, but it's a lot easier to ship customers between on-prem and cloud if they have their own db by default.
    • Will the scale of your customer databases in aggregate exceed the capability of your database engine, while single customers never will? This is a bit nuanced, because basically what we're trying to avoid is sharding. If you think multi-db is hard, then wait until you are dealing with a sharded database at scale with heavy load. I would prefer multi-db over that any time. The only thing you really want to avoid are situations when a single customer has their own sharded database.

    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!

  5. 2

    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..)

  6. 2

    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:

    1. 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.

    2. Queries and reports for all customers (in the admin) were a lot easier to do and only had to be run once.

    3. 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:

    1. 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).

    2. 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!

  7. 1

    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.

  8. 1

    Everything should be in one database only, just use different tables for different things like customers, transactions, etc...

  9. 1

    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.

  10. 1

    From a Product perspective, it depends:

    1. Are buyers and users willing to pay a premium for their own tenancy?
    2. If so what are the main reasons for that? Is it that they want control over where they want their data hosted, or is it around security/confidentiality risk?
    3. Can these needs be met in other ways?
    4. How much complexity/support is needed in each scenario, and can you handle it?

    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!

  11. 1

    I see everyone answers based on different context, I guess the leading questions should be around:

    • Are you selling resources (like webhosting) or just a service on your custom developed solution?
    • Is the service very resource hungry?
    • How stable is your schema / mature product? Are you working with RDBS or nosql system?..
    • Is your solution based on an existing system that's designed for a single user?
    • What's the minimal price per customer expected? is it b2c low ticket or huge b2b 10k+ sales?
      ..
  12. 1

    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.

  13. 1

    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.

  14. 1

    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 :)

  15. 1

    This comment was deleted a year ago.

    1. 1

      My sentiments, exactly - very confusing.

Trending on Indie Hackers
How I grew a side project to 100k Unique Visitors in 7 days with 0 audience 49 comments Competing with Product Hunt: a month later 33 comments Why do you hate marketing? 29 comments My Top 20 Free Tools That I Use Everyday as an Indie Hacker 16 comments $15k revenues in <4 months as a solopreneur 14 comments Use Your Product 13 comments