I am designing a SaaS application where thousands of users will be using this application. This application will do lots of data crunching and analytics. I am considering creating a main database which will hold all user credentials and configurations, then I will create individual databases for each customer for storing their data.
Main database will be used by services to determine what services should run at what time on which user’s database.
I am using postgres as my database.
How efficient is this design, or is there any better design which I should follow.
From the first glance, the design is not terribly efficient, but the caveat here is that it’s hard to be definite without knowing your scalability and redundancy requirements.
Multi-tenancy is one question mark for me. Your intent is to have a separate database per customer. How would that scale with the number of customers?
For example, let’s say you have a small customer A with a frequent usage pattern, and a large dormant customer B. With two separate instances, customer B’s postgres instance might be taking RAM unnecessarily.
Hence, it is quite common in SaaS services to go for multi-tenant instances, where all accounts share the same DB. This allows the DB to run a better caching strategy, and makes redundancy simpler, where you have one DB, rather than X DBs to replicate.
Another question revolves around scalability. postgresSQL is a classical RDBMS – nothing wrong with that. But, if you expect a moment in your project’s life where a single VM/hardware box won’t be able to hold all of your data, it will be time to think about sharding, replication etc, which could be a challenge.
In general, my advice would be look at pragmatic growth plans, target scale, and review postgres together with NoSQL DBs, while peeking at the CAP theorem.
Yet another angle is data crunching and concurrency. If mapreduce is in your list of considerations, it might push you to a backend choice that provides a good map-reduce framework.
Again, it might well be that you’ll find out that postgres is more than adequate for the task, and that you already considered most the above.