There are basically two options for multi-tenancy with their own tradeoffs.

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.

The secret bomb in option 1 is that you generally have to have smarter primary keys that fully embrace multitenancy and while Atlassian hires smart folks and I'm sure they at some level know this--that's a relatively hard retrofit to work into a system.

Option 2 has many unforeseen consequences.

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.

The second problem is mitigated by the fact that schemas are trivially migratable between database servers. Once you grow too big for one cluster just make another.

> 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...

> But that means your software backend needs to be able to work with different schemas concurrently.

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.

True. But then you have an additional problem ...

I have built multiple multi-tenancy platforms and I never create separate databases for each customer. If you have separate databases, it's almost impossible to run meaningful queries across all of them. That architectural choice creates far more headaches than it solves. Usually people end up with the split-database architecture when they want a quick retrofit for a system that wasn't designed with multiple tenants.

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.

It's likely a mixture of all these factors, the brain drain could absolutely be responsible.

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.

I can't believe anyone would do separate databases.

Just wait until a migration doesn't run on 2 of your 400+ customer databases. Or multi-hour migrations.

When all customer data lives in the unified database: Just wait until a bug in a query exposes the data of customers to each other, creating instant regulatory and privacy nightmares for everyone.

With an orm and customer objects to create scoped queries, I haven't found this to be a problem. It's also very easy to check in code reviews. And not a painful issue from, well, the lack of this happening given it's an extremely common app design.

Sounds good to me. Now you've got 398 happy customers on the new version, and a small scale issue to resolve with two customers.

It is like any other architectural choice - there are pros and cons both directions. If you have separate db instances, you have to scale up the operations to manage each one - migrations, scripts, etc need to be either run against them all, or you need good tooling in place to automate it. A single instance avoids all that, but is more complex in the actual software and definitely more complex for security. A single DB also would let you share data amongst organizations fairly easily, but whether that is good or bad depends on your product. I've created and run products both ways, and I like separate DBs at small scales, single DBs at medium scale, but separate DBs again at huge scale if you also put management tooling in place.

I believe you can sign up an account for free or incredibly cheap ($5/user). You would potentially have tens of thousands of databases. Imagine trying to do something like a database migration to add a column. I believe the day to day operations would be a nightmare as no RDBMS has probably had that kind of feature stress tested.

The company I work at (Workday) does this, but it's for business / liability reasons.

Bearing in mind the licence fees of Workday, the costs of separate databases pale in comparison!

Answer: it depends on the application. For example big social app is not going to provision a new db for every user, or for every customer that runs an ad. Likewise, a lot of enterprise software fits a model where each customer getting it's own db makes sense. So, really, just a design decision.

Separate DB instances doesn't scale as well cost wise, and generally means onboarding takes a few minutes instead of being instant. It is very common though.

The solution that satisfies everyone is having a separate schema per customer and a number of database clusters. Then each customer is assigned to a particular cluster. Always make sure you have excess capacity on your pool of clusters and onboarding is still instant.