I can't believe that they would intermix the data in that way... but if they did, godspeed to them, they're likely still overpromising what can be done in this time frame.
Is it not a good idea to spin up separate db instances for each client/company?
It depends, really. There is a trade-off in terms of software and operational complexity vs scalability/perf and isolation. And probably a bunch of other factors.
If you have separate databases for each customer, schema migrations can be staged over time. But that means your software backend needs to be able to work with different schemas concurrently. You can also benefit from resilience and isolation guarantees provided by the dbms. On the other hand, having a dbms manage lots of databases can affect perf. Linking between databases can be a minefield, especially w/r/t foreign keys and distributed transactions.
https://docs.microsoft.com/en-us/azure/azure-sql/database/sa...
Not if you're truly multi-tenant and each customer has their own app servers. Then your code and schema version are always in lock-step.
1. An account/tenant_id field for each table
2. A schema for each tenant wrapping all of the tables
Option 2 gives you cleaner separation but complicates your deployment process because now you have to run every database change across every schema every time you deploy. This gets more complicated as your code is deploying in case the code itself gets out of sync, there's a rollback or an error mid deploy due to an issue with some specific data.
The benefit of the approach is the option to do different backup policies for different customers, makes moving specific customers to specific instances easier and you avoid the extra index on tenant_id in every table.
Option 1 is significantly easier to shard out horizontally and simplifies the database change process, but you lose space on the extra indexes. Plus in many databases you can partition on the tenant_id.
Most people typically end up with option 1 after dealing with or reading horror stories about the operational complexity of option 2.
Business wants to run a query across customers? In most DBs you need either custom code or to create a stored procedure to iterate across schemas.
Every table that you create is multiplied by the number of customers. This has implications for some database systems (like PG's vacuum).
Your migrations will take _forever_ to run.
Etc.
I've also had to restore partial data from backups on a few occasions when customers fat-fingered some data and asked pretty-please to undo. If someone on staff understands the system well, it's not hard. I suspect Atlassian suffers from a complicated schema and a post-IPO brain drain.
Just wait until a migration doesn't run on 2 of your 400+ customer databases. Or multi-hour migrations.
At least it would not be the first time in history that a company has lost the engineering spirit. And instead the business people have taken over, so that details like disaster plans become less of a priority.
A business person and an engineer will always view risk differently, better disaster plans is a kind of insurance that is a lot harder to sell when too many business people run the company.
Would still be some maintenance, don't get me wrong. But far from impossible.
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!
You're lecturing about table design. I'm talking about more general transactionality over any errors.
Don't do any of the above unless you understand the implications.
Oh, and just forget about allowing your customers to share their data with each other, which most enterprises want in one way or another.
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.
Does this extend to services as well? We have a suite of (micro) services. Are they all segregated?
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?
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.
The best would be multiple separate database instances, which is not even hard to manage specially for qualified engineers like Atlassian surely has plenty of. The problem are business decisions of ignoring the tech debt, usually...
Automation wasn’t the issue here. It’s the symptom not the cause.
Really interested if you can share any details.
Edit: I know each wiki is on a subdomain. Does each wiki also have it's own server?
Some of this was open source before we unified all of our wiki products, which has a lot of the selection / db logic, at https://github.com/Wikia/app.
Source: worked at Atlassian, on Jira, 4 years ago.