6 ms·

This. It would be an impossible nightmare for every account to have their own DB. Hundreds of thousands of accounts and databases....

taeric 5y ago
Wait. Why? This sounds like something that feels hard, if you are used to the giant DBs of old. But you can probably get many many instances of the smaller databases without much trouble. Would still be some maintenance, don't get me wrong. But far from impossible.
- ezekg 5y ago
  Imagine the database schema migrations...
  - jagged-chisel 5y ago
    the good news is by the time you get to the 100th client, you'll likely have run into all possible bugs and the remaining 6900 will be pretty smooth.
  - Spivak 5y ago
    Having worked at shops that used this architecture it's really not that bad. Can you write the code to do one schema migration? Great, now you can do 1000. App server boots and runs the schema migrations, drops privs and launches the app. Now you've staved off your scaling issues from "how to have a db large enough to hold all our customer data" to "how to have a db large enough to hold our biggest customer's data." Much easier.
    - robertlagrant 5y ago
      You can write the code to do 1000 schema migrations, but the problem is if you've migrated 40% of them and hit an issue. What do?
      - spookthesunset 5y ago
        One of the many reasons to put good constrains on fields and use referential integrity! If you don't let the database enforce data validity you are gonna get fucked at some point! source: every single place I've worked at that poo-poos referential integrity has a database that is full of bullshit that "the application code" never cleaned up Always use referential integrity. The people who are against it almost always are against it for superstitious reasons (eg: "it makes things slow" or "only one codebase calls it so the code can enforce the integrity"). All it takes is exactly one bug in the application code to corrupt the whole damn thing. And that bug will happen over the lifetime of the product regardless of how "good" or "awesome" the programmers think they are.... ... I'll get off my soapbox now!
        robertlagrant 4y ago
        That's one thing yes. What if there's a transient network error, or the DB runs out of memory, and now you have some data in an old state and some in a new. You're lecturing about table design. I'm talking about more general transactionality over any errors.
- outworlder 5y ago
  It would be a nightmarish scenario if you have thousands of customers. And completely unnecessary. You can create multiple databases and or schemas in a single instance. Don't do any of the above unless you understand the implications.
- oauea 5y ago
  You'll quickly run into limitations of how many tcp connections you can hold open. Unless you also want to run separate app servers for each customer, which will cost a lot of $$$ Oh, and just forget about allowing your customers to share their data with each other, which most enterprises want in one way or another.
  - kubami 5y ago
    Wait. What? None of the enterprise customers want to share data with each other. And definitely not on a DB level. That should happen in the business logic.
    - lalaithion 5y ago
      Lots of companies have consultants, and want to be able to share their consulting-related tickets with their consultants. And the consultants want one system they can log into and see the tickets from all of the companies that are hiring them.
lelandbatey 5y ago
I worked at company that architected their multi-tenancy in almost exactly this style. In their particular case, only a few of the very largest customers had their database set aside on their own dedicated instance, but every customer did have their own DB with their own set of tables. Having worked in that world (every customer had their own DB) and on a product where all customers had their data intermingled in one gigantic set of tables in one giant DB on one logical instance, I'd definitely encourage the "every customer gets their own DB". Giving every customer their own table means you're going to need database administrators. For these folks their dedicated job was maintaining, operating, and changing their fleet of databases, but they where very technical and were amazing to work with.
- david422 5y ago
  > I'd definitely encourage the "every customer gets their own DB". Does this extend to services as well? We have a suite of (micro) services. Are they all segregated?
mdoms 5y ago
This is the case. I won't comment on your "hundreds of thousands" figure because the number of Cloud customers was a closely guarded secret at least when I worked there, but yes one DB per tenant, dozens to hundreds of DBs per server, and some complicated shuffling of tenant DBs when you run into noisy neighbours.
- mh- 5y ago
  That makes this prolonged restore process all the more confusing, then. I (and many others) assumed they had to graft in data from backups since a full restore would clobber newer changes from unaffected customers. If they're all isolated in their own logical per-tenant DBs, I'm really at a loss for what is making restoration take 3 weeks for 400 tenants. I understand if you'd rather not venture into it, but care to offer any speculation?
  - mdoms 5y ago
    To be honest I'm at a bit of a loss too. My speculation is that since they went all-in on microservices and utilizing various AWS services (something that was underway when I worked there) their data stores have become very much more disparate. For example, they have the main PostgreSQL data store. Surely that's easy to restore. But the users in that DB have a "foreign key" (in a logical sense, not physical) to the Identity service. This is a real life example that occurred while I was there. So now we have a mixture of multi and single tenancy. So perhaps the identity records are also tied to this app ID and deletes were propagated to that service. And perhaps there is an SQS queue and a serverless function to handle, say, outgoing mail from Jira. Where does this data go? I dunno maybe some Go-powered microservice with its own DocumentDB store. Do deletes propagate here too? Who knows. You can see how this gets complicated and how issues multiply with more services. Again, this is only speculation. But "decomposing the monolith" was a big deal and it was coming from the top.
    - mh- 5y ago
      Thanks for the insights. It does seem to have, ahem, decomposed.

taeric 5y ago

Wait. Why? This sounds like something that feels hard, if you are used to the giant DBs of old. But you can probably get many many instances of the smaller databases without much trouble. Would still be some maintenance, don't get me wrong. But far from impossible.

ezekg 5y ago
Imagine the database schema migrations...
- jagged-chisel 5y ago
  the good news is by the time you get to the 100th client, you'll likely have run into all possible bugs and the remaining 6900 will be pretty smooth.
- Spivak 5y ago
  Having worked at shops that used this architecture it's really not that bad. Can you write the code to do one schema migration? Great, now you can do 1000. App server boots and runs the schema migrations, drops privs and launches the app. Now you've staved off your scaling issues from "how to have a db large enough to hold all our customer data" to "how to have a db large enough to hold our biggest customer's data." Much easier.
  - robertlagrant 5y ago
    You can write the code to do 1000 schema migrations, but the problem is if you've migrated 40% of them and hit an issue. What do?
    - spookthesunset 5y ago
      One of the many reasons to put good constrains on fields and use referential integrity! If you don't let the database enforce data validity you are gonna get fucked at some point! source: every single place I've worked at that poo-poos referential integrity has a database that is full of bullshit that "the application code" never cleaned up Always use referential integrity. The people who are against it almost always are against it for superstitious reasons (eg: "it makes things slow" or "only one codebase calls it so the code can enforce the integrity"). All it takes is exactly one bug in the application code to corrupt the whole damn thing. And that bug will happen over the lifetime of the product regardless of how "good" or "awesome" the programmers think they are.... ... I'll get off my soapbox now!
      - robertlagrant 4y ago
        That's one thing yes. What if there's a transient network error, or the DB runs out of memory, and now you have some data in an old state and some in a new. You're lecturing about table design. I'm talking about more general transactionality over any errors.
outworlder 5y ago
It would be a nightmarish scenario if you have thousands of customers. And completely unnecessary. You can create multiple databases and or schemas in a single instance. Don't do any of the above unless you understand the implications.
oauea 5y ago
You'll quickly run into limitations of how many tcp connections you can hold open. Unless you also want to run separate app servers for each customer, which will cost a lot of $$$ Oh, and just forget about allowing your customers to share their data with each other, which most enterprises want in one way or another.
- kubami 5y ago
  Wait. What? None of the enterprise customers want to share data with each other. And definitely not on a DB level. That should happen in the business logic.
  - lalaithion 5y ago
    Lots of companies have consultants, and want to be able to share their consulting-related tickets with their consultants. And the consultants want one system they can log into and see the tickets from all of the companies that are hiring them.

lelandbatey 5y ago

I worked at company that architected their multi-tenancy in almost exactly this style. In their particular case, only a few of the very largest customers had their database set aside on their own dedicated instance, but every customer did have their own DB with their own set of tables. Having worked in that world (every customer had their own DB) and on a product where all customers had their data intermingled in one gigantic set of tables in one giant DB on one logical instance, I'd definitely encourage the "every customer gets their own DB". Giving every customer their own table means you're going to need database administrators. For these folks their dedicated job was maintaining, operating, and changing their fleet of databases, but they where very technical and were amazing to work with.

david422 5y ago
> I'd definitely encourage the "every customer gets their own DB". Does this extend to services as well? We have a suite of (micro) services. Are they all segregated?

mdoms 5y ago

This is the case. I won't comment on your "hundreds of thousands" figure because the number of Cloud customers was a closely guarded secret at least when I worked there, but yes one DB per tenant, dozens to hundreds of DBs per server, and some complicated shuffling of tenant DBs when you run into noisy neighbours.

mh- 5y ago
That makes this prolonged restore process all the more confusing, then. I (and many others) assumed they had to graft in data from backups since a full restore would clobber newer changes from unaffected customers. If they're all isolated in their own logical per-tenant DBs, I'm really at a loss for what is making restoration take 3 weeks for 400 tenants. I understand if you'd rather not venture into it, but care to offer any speculation?
- mdoms 5y ago
  To be honest I'm at a bit of a loss too. My speculation is that since they went all-in on microservices and utilizing various AWS services (something that was underway when I worked there) their data stores have become very much more disparate. For example, they have the main PostgreSQL data store. Surely that's easy to restore. But the users in that DB have a "foreign key" (in a logical sense, not physical) to the Identity service. This is a real life example that occurred while I was there. So now we have a mixture of multi and single tenancy. So perhaps the identity records are also tied to this app ID and deletes were propagated to that service. And perhaps there is an SQS queue and a serverless function to handle, say, outgoing mail from Jira. Where does this data go? I dunno maybe some Go-powered microservice with its own DocumentDB store. Do deletes propagate here too? Who knows. You can see how this gets complicated and how issues multiply with more services. Again, this is only speculation. But "decomposing the monolith" was a big deal and it was coming from the top.
  - mh- 5y ago
    Thanks for the insights. It does seem to have, ahem, decomposed.