Database-per-tenant is great for read-heavy apps - most tenants are small and their tables don't have a lot of records, so even very complex joins are very fast.
The main problem is that release times can increase substantially, because you have to migrate lots of individual databases one by one, and sometimes schema/data drift can occur between DBs, and then your release stops midway and you have to figure out why in some tenants the feature works, and in some it breaks... So it's not free launch, the complexity just shifts to a different place.
This one-by-one migration nicely contains problems though if something goes wrong - only one customer affected at a time (or whatever your batch size was) I’ve done deployments this way pretty much everywhere.
I'm curious, how do you handle being in a partial state?
It seems like it could be operationally tricky to have some users migrated, some pending, and some in an error state.
On the other hand, I could imagine doing lazy migrations as user or tenants sign in as a nice way to smooth out the increased effort in migrating if you can engineer for it.
Good point. Also: nothing makes it impossible to apply migrations to multiple tenants in parallel, this is pretty much only IO. I didn't have to since my tenant numbers were very low, but very doable.
I think its ok if you have a traditional server approach, but in the cloud this is a really great way to make yourself pay a lot for a little.
Most systems have a minimum amount of traffic they'll let you pay for, most object stores are not setup to stream small updates in a transactionally consistent way, there's a ton of complexity "in the small" as it were.
I worked as Ops person for a company that was database per user and that's we did. Every database had table with its current schema. On login, that current schema would be checked, if it was not up to date, RabbitMQ message was sent and database schema updater would update their database. User would get a screen saying "Updating to latest version" and it was just checking every 15 seconds to see if schema field updated. Most of time, it was done in less than 15 seconds.
For more important customers, you could always preemptively run their database schemas updates for them so they didn't see update screen.
I really like SQLite but I wonder if what we are seeing here is a need for conventional OLTP databases to be able to unload parts of indexes from memory.
Because essentially what’s happening with database per user is that we don’t keep anything in memory for users who are inactive, or only active on another instance, for essentially the same schema (or maybe a couple versions of a schema).
This is starting to smell like the JSON situation with Mongo. Where Postgres is now twice as fast as Mongo at the thing Mongo is supposed to be an expert in.
I wouldn't say so, with use cases I saw the databases of tenants would almost never exceed available memory. A database could be loaded into memory in its entirety for the duration of the request, provided the disk IO on the server is cooperating (i.e. "does not degrade to floppy drive speed once you are out of IOPS credit").
> Because essentially what’s happening with database per user is that we don’t keep anything in memory for users who are inactive, or only active on another instance, for essentially the same schema (or maybe a couple versions of a schema).
The benefits of prefetching and page-level abstractions are certainly reduced when every tenant is guaranteed to live in a different file.
I would agree that you should have to deal with an uphill battle when arguing for a DB-per-customer architecture. There needs to be a special reason to not simply add a customer/tenant id column in each table.
I think one good argument for placing a tenant per database file is that it makes total destruction of their information possible without any impact to other tenants. If you don't require immediate destruction of the tenant's information (i.e., can wait for background cleanup activities), then I would begin to push back.
When data can be this isolated from each other and you don't have any scaling issues within a single tenant it's pretty hard to make a wrong design choice. Almost anything will work.
I would say most people don't need a database per tenant and that is definitely not the norm. There are specific cases that you would need to negate the drawbacks such as migrations and schema drift.
I think the lack of gems/libraries/patterns is proof of this. Just because you can doesn't mean you should.
Not saying there's no reason to ever do it, proceed with caution and know for a fact you need db per tenant.
Seems like it’s actually really convenient as independent customer data ends up being nicely isolated. Deleting churned customer data is as trivial as deleting their database.
> I think lack of gems/libraries/patterns is proof of this
This would effectively disqualify any new pattern from emerging. There have been many new patterns that have challenged the consensus that ended up becoming dominant.
This is more proof of what I am saying. The urge to have something "nicely isolated" really needs to come with a checklist of why you need this, otherwise this comes off as a developer's desire for cleanliness regardless of major drawbacks.
Running some `DELETE FROM x WHERE TenantId = 1` is not a huge deal.
And yes I see your point about new patterns, but were talking about multi-tenancy not some new AI thing.
Ss someone with decades of experience I have found that if I go to implement [insert common pattern] and there is 0 libraries one way and 20 libraries the other way I need to check myself and determine if I am swimming against the current for little upside.
Coincidentally I'm working on FeebDB[0], which is similar but for Elixir instead. It can be seen as a replacement to Ecto (which won't work well when you have thousands of databases).
Mostly as a fun experiment, but also from the realization that every place I worked at in the past (small/medium-sized B2B startups) would greatly benefit from such architecture.
Yes, there are massive trade-offs to this approach, and the concerns raised in the comment section are valid. This doesn't mean the database-per-tenant is never worth it. There's a sweet spot for it, and if it fits your business/application, I personally would consider it a technical advantage over competitors.
My goal with FeebDB is to eliminate or reduce the common pain points of database-per-tenant, including:
- ensure there is a single writer per database.
- improved connection management across all tenants (e.g. only keep open at most 1000 DB connections, similar to an LRU cache).
- on-demand migration (all shards are migrated on application startup, but if a shard that hasn't migrated yet receives a request, it will first perform the migration and then serve the request),
- on-demand backups and replication (e.g. the library knows which shards were updated in the last X minutes, so it can trigger Litestream or similar on demand).
- support for enumeration of databases (performing map/reduce/filter operations across multiple DBs)
- support for clustered deployment with "pinned" tenants (for now I'm assuming the IOPS of a single beefy server should be enough for all use cases, but once that's no longer sufficient you can have "shards of shards")
This is how we did it at mailchimp. I think this is ignored or overlooked because this means devs might have to care a bit more about operations or the company has to care more.
That is what most tenanting scale-ups do. "Jumbo"-tenants get relocated either to separate partitions, or to partitions which are more sparsely populated.
Readit News
Database-per-tenant is great for read-heavy apps - most tenants are small and their tables don't have a lot of records, so even very complex joins are very fast.
The main problem is that release times can increase substantially, because you have to migrate lots of individual databases one by one, and sometimes schema/data drift can occur between DBs, and then your release stops midway and you have to figure out why in some tenants the feature works, and in some it breaks... So it's not free launch, the complexity just shifts to a different place.
On the other hand, I could imagine doing lazy migrations as user or tenants sign in as a nice way to smooth out the increased effort in migrating if you can engineer for it.
At least with Single Point of Success database design you either move or don’t.
Most systems have a minimum amount of traffic they'll let you pay for, most object stores are not setup to stream small updates in a transactionally consistent way, there's a ton of complexity "in the small" as it were.
Do you store the DB too, or rebuild it from your records if the client loses their DB?
Why not use Postgres with row level security instead?
For more important customers, you could always preemptively run their database schemas updates for them so they didn't see update screen.
I really like the free lunch / free launch pun here, intentional or not.
Deleted Comment
Because essentially what’s happening with database per user is that we don’t keep anything in memory for users who are inactive, or only active on another instance, for essentially the same schema (or maybe a couple versions of a schema).
This is starting to smell like the JSON situation with Mongo. Where Postgres is now twice as fast as Mongo at the thing Mongo is supposed to be an expert in.
The benefits of prefetching and page-level abstractions are certainly reduced when every tenant is guaranteed to live in a different file.
I would agree that you should have to deal with an uphill battle when arguing for a DB-per-customer architecture. There needs to be a special reason to not simply add a customer/tenant id column in each table.
I think one good argument for placing a tenant per database file is that it makes total destruction of their information possible without any impact to other tenants. If you don't require immediate destruction of the tenant's information (i.e., can wait for background cleanup activities), then I would begin to push back.
I think the lack of gems/libraries/patterns is proof of this. Just because you can doesn't mean you should.
Not saying there's no reason to ever do it, proceed with caution and know for a fact you need db per tenant.
> I think lack of gems/libraries/patterns is proof of this
This would effectively disqualify any new pattern from emerging. There have been many new patterns that have challenged the consensus that ended up becoming dominant.
Running some `DELETE FROM x WHERE TenantId = 1` is not a huge deal.
And yes I see your point about new patterns, but were talking about multi-tenancy not some new AI thing.
Ss someone with decades of experience I have found that if I go to implement [insert common pattern] and there is 0 libraries one way and 20 libraries the other way I need to check myself and determine if I am swimming against the current for little upside.
Mostly as a fun experiment, but also from the realization that every place I worked at in the past (small/medium-sized B2B startups) would greatly benefit from such architecture.
Yes, there are massive trade-offs to this approach, and the concerns raised in the comment section are valid. This doesn't mean the database-per-tenant is never worth it. There's a sweet spot for it, and if it fits your business/application, I personally would consider it a technical advantage over competitors.
My goal with FeebDB is to eliminate or reduce the common pain points of database-per-tenant, including:
- ensure there is a single writer per database.
- improved connection management across all tenants (e.g. only keep open at most 1000 DB connections, similar to an LRU cache).
- on-demand migration (all shards are migrated on application startup, but if a shard that hasn't migrated yet receives a request, it will first perform the migration and then serve the request),
- on-demand backups and replication (e.g. the library knows which shards were updated in the last X minutes, so it can trigger Litestream or similar on demand).
- support for enumeration of databases (performing map/reduce/filter operations across multiple DBs)
- support for clustered deployment with "pinned" tenants (for now I'm assuming the IOPS of a single beefy server should be enough for all use cases, but once that's no longer sufficient you can have "shards of shards")
[0] - https://github.com/renatomassaro/FeebDB/
1. Identify top-N tenants
2. Separate the DB for these tenants
The top-N could be based on mix of IOPS, importance (revenue wise), etc.
The data model should be designed in such a way that from rows pertaining to each tenant can be extracted.
--humbly signed-- a fellow admirer of Geert Van Schlänger