3 ms·

> shard your customer databases 1:1 What are the downsides to this?

inopinatus 5y ago
* makes it much harder to distribute your tables by any other factor, for whatever reason (usually performance, sometimes archival) * disaggregates data that the SaaS might be interested in querying/updating as an aggregate * not all ORM frameworks handle this case well, if at all * dumps are more than a single trivial command basically all your data operations gain an additional dimension of complexity, and you may not perceive the benefits until much later
- seanwilson 5y ago
  Would it be fair to estimate that the majority of SaaS companies aren't sharding like this then? Seems like a lot of downsides that impact everything often except for backups, which you'd restore rarely.
  - mypalmike 5y ago
    Per-customer is a common sharding strategy for noSQL databases, so it may not be entirely uncommon.
- deckard1 5y ago
  > not all ORM frameworks handle this case well, if at all typically this is probably for internal reporting/metrics. But yeah, a custom script with direct SQL is in order. Personally my opinion is avoid ORM at all costs. Never seen a benefit that wasn't trivially done in SQL, and the downsides are incredibly painful. The big downside of sharding out, per customer, is that's a lot of databases to migrate on upgrades. Or rollback if shit hits the fan. The upside? You can have customers on different versions of your app if you really wanted to do such a thing. In any case, proper tooling goes a long way to making it the difference between wonderfully manageable and torturous nightmare. Think idempotent backup scripts that are capable of failing at any time and resuming where they died, etc.
- darkwater 5y ago
  All of your points (minus maybe the first one) should be "easily" solved/implemented in a company the size of Atlassian, and maybe there are newer costumers sharded like this already. IMO what happened in this case is basically tech debt that is now being paid with loooot of interests.
- treis 5y ago
  Migrations suck too.

* makes it much harder to distribute your tables by any other factor, for whatever reason (usually performance, sometimes archival) * disaggregates data that the SaaS might be interested in querying/updating as an aggregate * not all ORM frameworks handle this case well, if at all * dumps are more than a single trivial command basically all your data operations gain an additional dimension of complexity, and you may not perceive the benefits until much later

seanwilson 5y ago
Would it be fair to estimate that the majority of SaaS companies aren't sharding like this then? Seems like a lot of downsides that impact everything often except for backups, which you'd restore rarely.
- mypalmike 5y ago
  Per-customer is a common sharding strategy for noSQL databases, so it may not be entirely uncommon.
deckard1 5y ago
> not all ORM frameworks handle this case well, if at all typically this is probably for internal reporting/metrics. But yeah, a custom script with direct SQL is in order. Personally my opinion is avoid ORM at all costs. Never seen a benefit that wasn't trivially done in SQL, and the downsides are incredibly painful. The big downside of sharding out, per customer, is that's a lot of databases to migrate on upgrades. Or rollback if shit hits the fan. The upside? You can have customers on different versions of your app if you really wanted to do such a thing. In any case, proper tooling goes a long way to making it the difference between wonderfully manageable and torturous nightmare. Think idempotent backup scripts that are capable of failing at any time and resuming where they died, etc.
darkwater 5y ago
All of your points (minus maybe the first one) should be "easily" solved/implemented in a company the size of Atlassian, and maybe there are newer costumers sharded like this already. IMO what happened in this case is basically tech debt that is now being paid with loooot of interests.
treis 5y ago
Migrations suck too.