3 ms·

>I've never worked on a product where 100% of the schema could be partitioned like this SaaS for B2B works well with this, you partition by organization. Each

by kgeist 2y ago

>I've never worked on a product where 100% of the schema could be partitioned like this SaaS for B2B works well with this, you partition by organization. Each organization has their own DB. Most organizations are typically small (no more than 100 users), and it greatly simplifies things. However, it's still problematic with large organizations (200k-300k is where it starts to fall apart).

NathanFlurry 2y ago
Yep. Nile (https://www.thenile.dev/ https://www.thenile.dev/) is trying to address this use case with a fully isolated PG databases. Though, I don't know how they handle scaling/sharding.
skrebbel 2y ago
But then any sort of management information, eg "find out how many active customers we have" or "which customer has the most activity" becomes a query over all the DBs. Similarly, admin tools for customer support etc easily become very hard to build. I'd love to know if anybody has a solution for this because otherwise DB-per-tenant sounds very nice indeed.
- kgeist 2y ago
  >But then any sort of management information, eg "find out how many active customers we have" or "which customer has the most activity" becomes a query over all the DBs Create a separate DWH storage where all necessary data from all databases is pulled and processed/aggregated in the background (via change data capture) and saved in a format that is more suitable for analytical queries. The downside is that it requires additional preparation and some upfront design. >Similarly, admin tools for customer support etc easily become very hard to build. Our CRM understands the concept of organizations/tenants, so when a customer files a ticket, we know their organization, and from there, for a tech support person, accessing "coolcompany.mysaas.com" is no different from accessing "mysaas.com". In my opinion, it makes tech support easier, because you have far less data/fewer logs to analyze/sift through (only a specific organization's DB) when investigating a problem.
- osigurdson 2y ago
  I don't think we currently have any solutions that allow you get the answer to any question with optimal performance. You could instead push the needed metrics to a stream during your normal processing and aggregate it. This of course duplicates the data as the same information is already available in the tenant databases but is a very reasonable trade-off.
- superq 2y ago
  OLAP questions are usually out-of-band and preferably by a tool designed for it (like Clickhouse). Scanning all DB's is something that can be done in the background for most of these use cases.

by kgeist 2y ago

NathanFlurry 2y ago

Yep. Nile (https://www.thenile.dev/ https://www.thenile.dev/) is trying to address this use case with a fully isolated PG databases. Though, I don't know how they handle scaling/sharding.

skrebbel 2y ago

But then any sort of management information, eg "find out how many active customers we have" or "which customer has the most activity" becomes a query over all the DBs. Similarly, admin tools for customer support etc easily become very hard to build. I'd love to know if anybody has a solution for this because otherwise DB-per-tenant sounds very nice indeed.

kgeist 2y ago
>But then any sort of management information, eg "find out how many active customers we have" or "which customer has the most activity" becomes a query over all the DBs Create a separate DWH storage where all necessary data from all databases is pulled and processed/aggregated in the background (via change data capture) and saved in a format that is more suitable for analytical queries. The downside is that it requires additional preparation and some upfront design. >Similarly, admin tools for customer support etc easily become very hard to build. Our CRM understands the concept of organizations/tenants, so when a customer files a ticket, we know their organization, and from there, for a tech support person, accessing "coolcompany.mysaas.com" is no different from accessing "mysaas.com". In my opinion, it makes tech support easier, because you have far less data/fewer logs to analyze/sift through (only a specific organization's DB) when investigating a problem.
osigurdson 2y ago
I don't think we currently have any solutions that allow you get the answer to any question with optimal performance. You could instead push the needed metrics to a stream during your normal processing and aggregate it. This of course duplicates the data as the same information is already available in the tenant databases but is a very reasonable trade-off.
superq 2y ago
OLAP questions are usually out-of-band and preferably by a tool designed for it (like Clickhouse). Scanning all DB's is something that can be done in the background for most of these use cases.