0

I'm contemplating a multi-tenant SaaS application wherein each client would have its own allocated database with its own client-defined schema. None of the underlying clients would have a large scale of data by themselves (likely in range of 1M rows on avg across tables). Some latency in processing would be fine -- this does not need to be real time / is not serving e.g. live web apps. And from the client's perspective, this would be serverless / manageable by non-technical users.

All the commentary / technical blogs I read online (understandably) discuss scaling databases into a large number of rows (with e.g. horizontal sharding on primary key, vertical scaling into high-powered servers, etc.). While scaling into more DBs is not discussed, it feels like an easier thing to tackle because the data within a single DB doesn't need to be split... Each DB can live on its own next to other DBs on a single server. Just a matter of routing queries.

Are there any databases that you think would well-support what I'm talking about? Or any combination of things like Vitess/k8s that might be able to do this database / query routing, maintain each of the underlying DBs, etc.? You can see PlanetScale.com has built something like this, but they're a bit more knowledgeable having literally written Vitess... Same comment on MongoDB Atlas, but Mongo's product does not obfuscate the underlying servers.

Andrew
  • 3,901
  • 15
  • 50
  • 64
  • 1
    Recently I had implemented mutli-tenancy in a health care application. I had used the AWS Dynamo DB for this purpose. In my scenario, every pharmacy has its own database. So whenever a new pharmacy company registered according to the defined schemas the separate tables are created and We enter that pharmacy company detail in one central table. We had implemented it through prefixes that will separate every table. I think you can also go with this model. – Arslan Ali May 30 '21 at 16:56
  • 1
    Thank you Arslan -- I was actually thinking about implementing something similar on a Mongo Atlas cluster because it's possible to maintain multiple DBs. Also like their change stream functionality. Curious if anyone has seen this implemented with a traditional relational DB e.g. MySQL/Postgres? – Andrew May 30 '21 at 19:32
  • Yes, My friend has implemented the same functionality with Postgress. In his implementation, he had created a new database for every tenant. I am inviting him to this conversation, so he can elaborate more concisely. – Arslan Ali May 31 '21 at 05:46
  • 1
    We are creating seperate DB for each client. The system is under contruction. So current algorithm, when we create tenant DB, we save its details in main DB (like db name, owner username, password, ..) and then send an email to tenant owner with his username and password. When user login here, system checks if tenant details available in main DB, so it saves id in user's JWT token (or cookie can also be used). When user visit any URL we get tenant id from token and get details from main DB, then just update the connection URL (inside DB Manager, SqlAlchemy in our case) to relevant DB. – Faizan AlHassan May 31 '21 at 06:13

0 Answers0