i expect to have 10 clients… or maybe more in the future. let’s say i get lucky and i get 500 clients (never going to happen that, I only propose a hypothetical situation)
what is the best approach for many clients. one DB or many?
note: i use a postgresql database that have a lot of transactions per day (2000 inserts aprox, 730000 per year, which is not much, but if they are many customers, all sum)
Use one. One of our PostgreSQL writes 2000 queries / second and reads 1.800.000 / second
Actually it does:
It depends. I use a single postgres database for all my clients. This way, if I need to change anything in the database, I only have to do that once.
For example, if I add a new column to a table, I do it only one time. Otherwise, you have to open each database and make the change on each one.
Multi-tenant multi-instance is the best way. You can start with one database that is multi-tenant aware but you can still split a large customer into their own instance as needed.
A good migration strategy will let you make changes in structure to multiple copies easily. It will always be faster to have one copy, but I wouldn’t use that as a deciding factor.
Multi-tenant is nice because it’s easier to upgrade your offering with having one database. Also it’s much more difficult to retrofit your database later to be multi-tenant than if you had just started with that.
However you want to embrace multi-instance as well because some organizations are required to keep their data separate. It also makes it easier to provide your customers with their data if they are isolated.
A lot depends on the kind of data you are storing.