8 ms·

We serve enterprise customers, and use the 'separate database' strategy for these reasons - 1. Stronger data isolation. Technically schemas provide the same le

by e1g 10y ago

We serve enterprise customers, and use the 'separate database' strategy for these reasons - 1. Stronger data isolation. Technically schemas provide the same level of isolation, but they are much harder to explain or defend during compliance audits. 2. More secure backups. Client data can be encrypted with their own key and stored as per their needs. 3. More useful backups. The frequency and retention of backups can vary to meet the SLA requirements (and costs). And we if something goes wrong, we can recover that particular customer's data in isolation without impacting the rest of the application/customers or worry about merging it in. 4. Secure data deletion. Many European customers demand that all their data is securely removed upon termination. This creates a massive problem with purging backup information if everything is in the same dump. 5. Independent load & performance. If one customer is particularly high-load or chatty, we can move them onto a separate server where they don't impact well behaved folks. 6. Easier horizontal scalability. Just move heavy loads to their own servers + read replicas. 7. Direct access to data. Specifically, we can use BI tools that do not understand schemas and even give direct access to clients' analysts. 8.Independent migration paths. Sometimes the customer's timetable does not allow them to upgrade to the newest version right now (e.g. they are in the middle of using the app for a key process this month). We can leave their account routed to the previous version in both the codebase and the data store. Out of those, the key 3 reasons are: stronger data isolation, better backup strategy, and more predictable compliance story. But that's enterprise: even if we're widely successful, we'll have "thousands" of customers - never millions. And we can manage thousands of databases, so this architecture path is preferable to us within those boundaries.

koolba 10y ago
Separate clusters (not just databases) also gives you the ability to create read only replicas/hot standby on a per customer/application basis.
noxee 10y ago
How do you handle look ups that would require cross tenant queries? e.g. each customer has multiple devices connected devices to our system and we want to know the health (is it online, is it responding, etc). The one way I've thought of current is either having an aggregate table in a "central" database that would be used to collect these kind of statistics. It would be "real-time" but it would be near real-time depending on the frequency of updates. The downside is you have the over head of maintaining a separate data source. The other option was to just have the software set up to query each tenant at a time and take the performance/time hit. That's not really the best experience and probably violates the idea of data isolation.
- xemdetia 10y ago
  I'm having a trouble understanding what you are saying by cross-tenant queries in context. What you are describing is application monitoring which would be completely independent of the DB holding customer data, which is what they are describing in your parent's post and the OP. Application runtime/health status could trivially be handled in a separate stream.
  - noxee 10y ago
    It's not really the application runtime/health status, it's the status of individual assets for each tenant. So a scenario would be as technical support person I would like to look at what customer assets are currently off-line across all tenants. One way to do that would be to query each individual tenant database and then combine the information. But this would void the idea of security/isolation. The other downside is that it's slow. But I think I've been looking at this from the wrong point of view, there should be a central health check service for these assets that collects the status of the assets and then that would be queried by the support person.
    - xemdetia 10y ago
      Generally I see it the other direction as a technical support person- each tenant's host is responsible for reporting uptime to a central service (because it's SaaS) and the customer is aware of this (even if it's basic syslog forwarding). Because we are not reaching in and they are sending out their customer data is still isolated. Any time you are reaching into the customer datasets that is where you have the possibility of breach via that access and would have to make sure all of those events are audited and that audit records are secure. If it is sending monitoring data to an aggregation service that can be well-defined in contents and much easier to demonstrate that no sensitive tenant information is shared. It's much easier and saner to setup a server to receive and configure each tenant's host to send exactly what is needed and then analyze who didn't send their stuff at regular intervals for a more direct investigation and everyone can agree on what needs to be done based on that.
andreygrehov 10y ago
> 8.Independent migration paths. In this case, how is the business logic (codewise) handled across different states of databases?
- icebraining 10y ago
  Not OP, but in our case, every customer (company, not user) gets its own process, running whatever version of the codebase they're on, much like if they were hosting it in their premises. We don't support very old versions, mind you, except for security fixes.
  - andreygrehov 10y ago
    Thank you, Andre. That's an interesting concept I haven't thought of.
leemac 10y ago
I second this strategy. It does cause a few more headaches, but overall clients prefer it to a single DB. We're in enterprise finance, so this comes up a lot and the answer is typically well received. Almost all the points above are the precise reason we went database-per-tenant. We also chose this method because our system was self-hosted originally (Single DB, non-multi-tenant) for 4 years or so. Adding columns to each table to separate was not ideal and we didn't want to run the risk of 'forgetting a where clause' at any point. For those wondering, all of our databases have the same schema. We use Visual Studio Database Projects to handle updates to keep it all in sync.
taude 10y ago
Ditto, here. On top of the fact that our large Enterprise customers have their own data isolation/security requirements (each one their own unique requirements).
pedrocarvalho 10y ago
Same here. Also working with enterprise customers, sometimes competitors (or at least, tangentially). Thinking of business reassons, there is absolutely no way that they would accept to "share" a database. On everyday ops, it's reason 8 - Independent upgrades on different time points.
Roboprog 10y ago
Having spent almost 5 years at a financial company, I get you. To others out there, make sure you charge clients accordingly if you have to do this. "Captain Obvious" out!
- hackits 10y ago
  Just curious, with your 5 years with financial companies do you see them do things differently than most organisations? eg.. deadlines?, SLA?, tech-understanding? I had a couple of colleges working for the banking financial sector and they where pretty high strung most of the time. They where a bit more direct in regards to putting money on the table to get shit done.
  - Roboprog 10y ago
    I worked in a smaller, "high value investor" unit, within a parent mutual fund company. It was very much not a tech company, although there were a few good people in the org. There was of course a big focus on buying supported packages, and off-shoring as much as possible. I understand buying "non core competency commodities", but the off shoring was dubious. The custom stuff in my unit was very much neglected, except for integration projects when changing vendors, which of course have to happen when you move some of your dependencies. Anyway, uptime was VERY important, which also implies a fair amount of risk management/reduction. In fairness, buying external services does make costs much more predictable. However, if you are on the vendor side of this, you need to make sure you build in pricing to cover what will be a very demanding customer.
    - Roboprog 10y ago
      Offshoring fun & games: another team bought this expensive Java caching service thing from IBM. They did not bother to learn how to properly code the fetches so that the calls included "cache miss" logic to get missing items (d'oh!). Whenever they restarted the web site, they had to first spend N minutes running a custom program to regenerate the "cache" content. I suspect they didn't really understand the definition of "cache" :-) (they of course did some other ignorant things which made this an issue, as well)
mbesto 10y ago
> But that's enterprise: even if we're widely successful, we'll have "thousands" of customers - never millions. And we can manage thousands of databases, so this architecture path is preferable to us within those boundaries. Exactly. Unless you're ACV is $100k+ this is strategy isn't really sustainable and might materially impact your gross margins. EDIT: Further, read the more technical limitations of this approach: https://news.ycombinator.com/item?id=12652021 https://news.ycombinator.com/item?id=12652021
- icebraining 10y ago
  Exactly. Unless you're ACV is $100k+ this is strategy isn't really sustainable and might materially impact your gross margins. Why? We have customers with ACVs under $10k (even after adjusting for cost of living/general prices level) with this model. We simply automated the whole process, so the clients can register for our service and get a new database (created from a template) without any manual intervention.
  - mbesto 10y ago
    Good luck with schema migrations and backups when you hit 1,000 customers (see Citus rep's comment).
    - icebraining 10y ago
      I think they're talking about one-schema-per-tenant, not one-database-per-tenant like e1g. Still, we'll never hit anything close to 1000 customers per server, we distribute them over more servers instead. In fact, that's an advantage of this model over the single-database one: since every tenant is isolated, the model is perfectly horizontally scalable. You can simply fill up servers without worrying about massively distributed databases or datacenter-wide load balancing. Hell, we just migrated across hosting providers with barely breaking a sweat: we simply moved machine by machine without ever worrying about latency between DCs and all that.
      - mbesto 10y ago
icebraining 10y ago
Same here (European company, hosting a business SaaS platform for companies of all sizes). I frankly prefer this approach, since it completely bypasses the problems that come with sharding and distributed deployments, since every customer is completely isolated. I just wish there was more tooling available that supported our architecture.

by e1g 10y ago

koolba 10y ago

Separate clusters (not just databases) also gives you the ability to create read only replicas/hot standby on a per customer/application basis.

noxee 10y ago

How do you handle look ups that would require cross tenant queries? e.g. each customer has multiple devices connected devices to our system and we want to know the health (is it online, is it responding, etc). The one way I've thought of current is either having an aggregate table in a "central" database that would be used to collect these kind of statistics. It would be "real-time" but it would be near real-time depending on the frequency of updates. The downside is you have the over head of maintaining a separate data source. The other option was to just have the software set up to query each tenant at a time and take the performance/time hit. That's not really the best experience and probably violates the idea of data isolation.

xemdetia 10y ago
I'm having a trouble understanding what you are saying by cross-tenant queries in context. What you are describing is application monitoring which would be completely independent of the DB holding customer data, which is what they are describing in your parent's post and the OP. Application runtime/health status could trivially be handled in a separate stream.
- noxee 10y ago
  It's not really the application runtime/health status, it's the status of individual assets for each tenant. So a scenario would be as technical support person I would like to look at what customer assets are currently off-line across all tenants. One way to do that would be to query each individual tenant database and then combine the information. But this would void the idea of security/isolation. The other downside is that it's slow. But I think I've been looking at this from the wrong point of view, there should be a central health check service for these assets that collects the status of the assets and then that would be queried by the support person.
  - xemdetia 10y ago
    Generally I see it the other direction as a technical support person- each tenant's host is responsible for reporting uptime to a central service (because it's SaaS) and the customer is aware of this (even if it's basic syslog forwarding). Because we are not reaching in and they are sending out their customer data is still isolated. Any time you are reaching into the customer datasets that is where you have the possibility of breach via that access and would have to make sure all of those events are audited and that audit records are secure. If it is sending monitoring data to an aggregation service that can be well-defined in contents and much easier to demonstrate that no sensitive tenant information is shared. It's much easier and saner to setup a server to receive and configure each tenant's host to send exactly what is needed and then analyze who didn't send their stuff at regular intervals for a more direct investigation and everyone can agree on what needs to be done based on that.

andreygrehov 10y ago

> 8.Independent migration paths. In this case, how is the business logic (codewise) handled across different states of databases?

icebraining 10y ago
Not OP, but in our case, every customer (company, not user) gets its own process, running whatever version of the codebase they're on, much like if they were hosting it in their premises. We don't support very old versions, mind you, except for security fixes.
- andreygrehov 10y ago
  Thank you, Andre. That's an interesting concept I haven't thought of.

leemac 10y ago

I second this strategy. It does cause a few more headaches, but overall clients prefer it to a single DB. We're in enterprise finance, so this comes up a lot and the answer is typically well received. Almost all the points above are the precise reason we went database-per-tenant. We also chose this method because our system was self-hosted originally (Single DB, non-multi-tenant) for 4 years or so. Adding columns to each table to separate was not ideal and we didn't want to run the risk of 'forgetting a where clause' at any point. For those wondering, all of our databases have the same schema. We use Visual Studio Database Projects to handle updates to keep it all in sync.

taude 10y ago

Ditto, here. On top of the fact that our large Enterprise customers have their own data isolation/security requirements (each one their own unique requirements).

pedrocarvalho 10y ago

Same here. Also working with enterprise customers, sometimes competitors (or at least, tangentially). Thinking of business reassons, there is absolutely no way that they would accept to "share" a database. On everyday ops, it's reason 8 - Independent upgrades on different time points.

Roboprog 10y ago

Having spent almost 5 years at a financial company, I get you. To others out there, make sure you charge clients accordingly if you have to do this. "Captain Obvious" out!

hackits 10y ago
Just curious, with your 5 years with financial companies do you see them do things differently than most organisations? eg.. deadlines?, SLA?, tech-understanding? I had a couple of colleges working for the banking financial sector and they where pretty high strung most of the time. They where a bit more direct in regards to putting money on the table to get shit done.
- Roboprog 10y ago
  I worked in a smaller, "high value investor" unit, within a parent mutual fund company. It was very much not a tech company, although there were a few good people in the org. There was of course a big focus on buying supported packages, and off-shoring as much as possible. I understand buying "non core competency commodities", but the off shoring was dubious. The custom stuff in my unit was very much neglected, except for integration projects when changing vendors, which of course have to happen when you move some of your dependencies. Anyway, uptime was VERY important, which also implies a fair amount of risk management/reduction. In fairness, buying external services does make costs much more predictable. However, if you are on the vendor side of this, you need to make sure you build in pricing to cover what will be a very demanding customer.
  - Roboprog 10y ago
    Offshoring fun & games: another team bought this expensive Java caching service thing from IBM. They did not bother to learn how to properly code the fetches so that the calls included "cache miss" logic to get missing items (d'oh!). Whenever they restarted the web site, they had to first spend N minutes running a custom program to regenerate the "cache" content. I suspect they didn't really understand the definition of "cache" :-) (they of course did some other ignorant things which made this an issue, as well)

mbesto 10y ago

> But that's enterprise: even if we're widely successful, we'll have "thousands" of customers - never millions. And we can manage thousands of databases, so this architecture path is preferable to us within those boundaries. Exactly. Unless you're ACV is $100k+ this is strategy isn't really sustainable and might materially impact your gross margins. EDIT: Further, read the more technical limitations of this approach: https://news.ycombinator.com/item?id=12652021 https://news.ycombinator.com/item?id=12652021

icebraining 10y ago
Exactly. Unless you're ACV is $100k+ this is strategy isn't really sustainable and might materially impact your gross margins. Why? We have customers with ACVs under $10k (even after adjusting for cost of living/general prices level) with this model. We simply automated the whole process, so the clients can register for our service and get a new database (created from a template) without any manual intervention.
- mbesto 10y ago
  Good luck with schema migrations and backups when you hit 1,000 customers (see Citus rep's comment).
  - icebraining 10y ago
    I think they're talking about one-schema-per-tenant, not one-database-per-tenant like e1g. Still, we'll never hit anything close to 1000 customers per server, we distribute them over more servers instead. In fact, that's an advantage of this model over the single-database one: since every tenant is isolated, the model is perfectly horizontally scalable. You can simply fill up servers without worrying about massively distributed databases or datacenter-wide load balancing. Hell, we just migrated across hosting providers with barely breaking a sweat: we simply moved machine by machine without ever worrying about latency between DCs and all that.
    - mbesto 10y ago

icebraining 10y ago

Same here (European company, hosting a business SaaS platform for companies of all sizes). I frankly prefer this approach, since it completely bypasses the problems that come with sharding and distributed deployments, since every customer is completely isolated. I just wish there was more tooling available that supported our architecture.