6 ms·

Shardines: SQLite3 Database-per-Tenant with ActiveRecord

255 points by julik 1y ago

shauntest12321 1y ago
Awesome stuff, Forward Email does similar with an encrypted sqlite db per mailbox / per user. Great way to differentiate protection per user.
ahdanggit 1y ago
Been doing database per-tenant (MySQL though not SQLite) since 2012, app is still going strong.
bsaul 1y ago
wonder if active record couldn’t borrow some pattern from coredata with its managedobjectcontext property associated to every object. This lets you work with objects transparently, not having to carry a « db » property everywhere, and the framework always knowing where the object come from, it can trigger an error if you’re trying to do something unholy by mixing them.
- jrochkind1 1y ago
  I bet it would not be very hard to write that as an extension for proof of concept. There have been some good flexible operations added for multi-database stuff in activerecord over the past couple versions.
  - julik 1y ago
    And these are exactly the ones I'm using here ;-)
mrits 1y ago
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.
kgeist 1y ago
We use "database-per-tenant" with ~1 mln users. 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.
- jasonthorsness 1y ago
  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.
  - SOLAR_FIELDS 1y ago
    Good call out, it definitely reduces the blast radius to do it this way. Poor man’s isolation
    - deleted 1y ago
      [deleted]
  - julik 1y ago
    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.
  - tyre 1y ago
    Do you then run app instances per user? Or how do your upstream control for some DBs being on different schemas? At least with Single Point of Success database design you either move or don’t.
    - 1y ago
Mystery-Machine 1y ago
I use `site_id` in every model and keep everything in a single database. I think this is how Shopify started as well. You can see that because their product, variant, collection IDs are incremental across all shops. They might be using different solution today, but it feels like this is how they started. What I like about having everything in one db, until it grows too big, is that I can do cross-users analytics/reporting. I also had it happen, like it was mentioned in the article, that I needed to share some data between users. Having everything in a single database made this much simpler problem than needing to move data between databases.
- julik 1y ago
  The "hard problem" with DB tenanting is drawing the line where your "common" apex DB model begins, and where the tenanted data ends.
rcarmo 1y ago
Came here for the amazing title pun. Like the approach, was a bit sad it's about Ruby but won't judge.
- nop_slide 1y ago
  Why is it sad it’s about Ruby?
  - lgas 1y ago
    Obviously they are not a fan of Ruby.
- nine_k 1y ago
  With SQLite, the approach should equally trivially work with any language and framework. Unlike e.g. Postgres, there's no connection overhead, pgbouncer, etc; you can open the DB at the beginning of the request, once the user credentials are available, and close it before / along with sending the response, and it still would feel instant.
codaphiliac 1y ago
Wonder how many sqlite databases would be too many. At one point I assume not all databases can be kept opened at all time. what sort of overhead would there be serving a tenant not opened up yet? there has to be caches etc. not warmed up causing lots of disk IO
- koakuma-chan 1y ago
  In this scenario you would use short-lived connections, and the overhead would probably be approximately the same as reading a file.
- julik 1y ago
  That would be the FD limit, divided by 3 (the DB itself, the shm file and the WAL).
  - ncruces 1y ago
    But each SQLite connection (even to the same DB) will also consume 2 FDs for the DB and the WAL. You'll more easily pool connections to the same DB, of course, but the difference might not be so stark.
    - julik 1y ago
      Something like that, yes. A tenant that hasn't been opened yet - well, you create the tenant first, and then proceed "as normal". With ActiveRecord, your `pool:` configuration defines how many database handles you want to keep open at the same time. I set it relatively high but it can be tweaked, I'd say. And there is automatic eviction from the pool, so if you have a few sites which are popular and a lot of sites which are not popular - it should balance out. There could be merit to "open and close right after" though, for sure.
- toast0 1y ago
  At some level, it doesn't make a big difference if you've got a file open or not once the file's data falls out of the disk cache, you'll have the same kinds of latency to get it back. Sure, you'll avoid a round or two of latency pulling in the filesystem data to get to the file, but probably not a big deal on SSD. Chances are, the popular databases stay in cache and are quick to open anyway; and the unpopular ones are rarely accessed so delay is ok. But you'd also be able to monitor for disk activity/latency on a system level and add more disks if you need more throughput; possibly disks attached to other machines, if you also need more cpu/ram to go with it. Should be relatively simple to partition the low use databases, because they're low use.
huhtenberg 1y ago
If noshing else, the name is exshellent. Very Sean Connery.
- julik 1y ago
  Much appreciated! --humbly signed-- a fellow admirer of Geert Van Schlänger
scosman 1y ago
What’s the disk behind this strategy? Lots of sqllite DBs is easy until you have lots of servers, no?
- julik 1y ago
  Kind of, yes. If you are going to be doing this at scale, you will either need to pin servers to shards of users, or retrieve the DB from some durable storage "just in time", or... use a network mount. Note also, that with small-enough databases you can do the same thing Apple does - fetch your entire tenant from the "big datastore", set a lock on it, do the thing that you want to do - and then write it back into the "big datastore" at the end.
  - digianarchist 1y ago
    I guess if the tenant isolation is more important than the disk/network io overhead this would introduce then it's viable solution. It'd probably work better if tenant == user and more specifically tenant == single-user-session.
pushcx 1y ago
Could anyone who runs Rails with sqlite in production share some scale numbers like r/w per second, # rows in the db, and vps size? I have used it at trivial scale for SolidQueue and SolidCache, but I can't find experience reports for the primary OLTP db.
andrewstuart 1y ago
Database per tenant can get real nasty when schemas get out of sync, then you’re in real pain. Postgres with row based access control is a much better solution to database per tenant/strong isolation.
- digianarchist 1y ago
  I’d heard of using Postgres schemas before but not RLS. Interesting… https://www.crunchydata.com/blog/row-level-security-for-tenants-in-postgres https://www.crunchydata.com/blog/row-level-security-for-tena...
- julik 1y ago
  That is true, but in my experience this is less of an issue if your migration allow running against "current - 1". If your migration borks on one of the tenants - your system should still work, and you can grab that tenant and investigate. This problem also does happen with sharded databases - which is why most co's have a structured approach of deploying schema changes, observing them having applied everywhere, and only then deploying the feature relying on the schema changes.
  - andrewstuart 1y ago
    Yeah but “schemas out of sync!” Is a never ending development and operations problem with SQLite database per tenant. Postgres row level access control gives all the benefits of strong isolation with none of the pain of schemas getting out of sync.
    - julik 1y ago
      You do need to keep tabs on whether they are or not, that is true - and you will have a bit of management to do if they are under constant heavy write load. For me, this all works out because I don't have too many tenants - and I have tools to block writes when running migrations. It's all tradeoffs after all.
- devnull3 1y ago
  Shouldn't the service daemon upgrade schema and perform migrations on initialisation? Before performing upgrade make a copy of the db file. Also multiple DBs can be inited in parallel. I fail to see why this might be so hard.
devnull3 1y ago
I think an in-between approach could be: 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.
- leetrout 1y ago
  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.
- julik 1y ago
  That is what most tenanting scale-ups do. "Jumbo"-tenants get relocated either to separate partitions, or to partitions which are more sparsely populated.
Lord_Zero 1y ago
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.
- thethimble 1y ago
  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.
  - Lord_Zero 1y ago
    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.
napsterbr 1y ago
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") [0] - https://github.com/renatomassaro/FeebDB/ https://github.com/renatomassaro/FeebDB/
hinkley 1y ago
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.
- bob1029 1y ago
  > 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.
- julik 1y ago
  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").
DidYaWipe 1y ago
Love the name. That is all.
- julik 1y ago
  Thanks!
haarts 1y ago
I used something similar in the past. Loved it. User wants their data? Bang! Here's the entire database. User deletes their account? `rm username.sql` and you're done. Compliance was never easier.
breckenedge 1y ago
> And, of course, the “database per tenant” workflow is just starting James Edward Gray spoke about doing this at RailsConf in 2012. https://youtu.be/GRfJ9lni4QA?si=BoDI5gQ8GvM1PDhw https://youtu.be/GRfJ9lni4QA?si=BoDI5gQ8GvM1PDhw
evolve2k 1y ago
Fascinating article documenting a clear challenge over multiple versions of Rails. My read on the subtext of the article is that these issues of proper SQLite support within Active Record won’t fix with a clever gem or quick script but rather need dedicated effort in the rails core. What surprises me a little is that the article is written just now in April 2025 but makes no mention of recent developments in rails. Specially I’m referring to the Rails 8 release. My take away from Rails 8 is that one of the biggest features is significant work towards making SQLite very much a first class citizen of rails, with significant focus on enhancin support for it across the stack. The text below is from the announcement: “Rails 8 No PASS required” “Getting SQLite ready for production On top of the trifecta of Solid adapters that makes it possible for SQLite to power Action Cable, Rails.cache, and Active Job, a bunch of work has gone into making the SQLite adapter and Ruby driver suitable for real production use in Rails 8. At 37signals, we’re building a growing suite of apps that use SQLite in production with ONCE. There are now thousands of installations of both Campfire and Writebook running in the wild that all run SQLite. This has meant a lot of real-world pressure on ensuring that Rails (and Ruby) is working that wonderful file-based database as well as it can be.” https://rubyonrails.org/2024/11/7/rails-8-no-paas-required https://rubyonrails.org/2024/11/7/rails-8-no-paas-required Surely this work bears fruit for the challenges the developer speaks of here. Curious as to if anyone has further insight into what extent Rails 8 addresses the highlighted shortcomings and further if there is a significant gap what would be needed to have this resolved for Rails 9 which I expect is underway.

255 points by julik 1y ago

Awesome stuff, Forward Email does similar with an encrypted sqlite db per mailbox / per user. Great way to differentiate protection per user.

ahdanggit 1y ago

Been doing database per-tenant (MySQL though not SQLite) since 2012, app is still going strong.

bsaul 1y ago

wonder if active record couldn’t borrow some pattern from coredata with its managedobjectcontext property associated to every object. This lets you work with objects transparently, not having to carry a « db » property everywhere, and the framework always knowing where the object come from, it can trigger an error if you’re trying to do something unholy by mixing them.

jrochkind1 1y ago
I bet it would not be very hard to write that as an extension for proof of concept. There have been some good flexible operations added for multi-database stuff in activerecord over the past couple versions.
- julik 1y ago
  And these are exactly the ones I'm using here ;-)

mrits 1y ago

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.

kgeist 1y ago

We use "database-per-tenant" with ~1 mln users. 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.

jasonthorsness 1y ago
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.
- SOLAR_FIELDS 1y ago
  Good call out, it definitely reduces the blast radius to do it this way. Poor man’s isolation
  - deleted 1y ago
    [deleted]
- julik 1y ago
  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.
- tyre 1y ago
  Do you then run app instances per user? Or how do your upstream control for some DBs being on different schemas? At least with Single Point of Success database design you either move or don’t.
  - 1y ago

Mystery-Machine 1y ago

I use `site_id` in every model and keep everything in a single database. I think this is how Shopify started as well. You can see that because their product, variant, collection IDs are incremental across all shops. They might be using different solution today, but it feels like this is how they started. What I like about having everything in one db, until it grows too big, is that I can do cross-users analytics/reporting. I also had it happen, like it was mentioned in the article, that I needed to share some data between users. Having everything in a single database made this much simpler problem than needing to move data between databases.

julik 1y ago
The "hard problem" with DB tenanting is drawing the line where your "common" apex DB model begins, and where the tenanted data ends.

rcarmo 1y ago

Came here for the amazing title pun. Like the approach, was a bit sad it's about Ruby but won't judge.

nop_slide 1y ago
Why is it sad it’s about Ruby?
- lgas 1y ago
  Obviously they are not a fan of Ruby.
nine_k 1y ago
With SQLite, the approach should equally trivially work with any language and framework. Unlike e.g. Postgres, there's no connection overhead, pgbouncer, etc; you can open the DB at the beginning of the request, once the user credentials are available, and close it before / along with sending the response, and it still would feel instant.

codaphiliac 1y ago

Wonder how many sqlite databases would be too many. At one point I assume not all databases can be kept opened at all time. what sort of overhead would there be serving a tenant not opened up yet? there has to be caches etc. not warmed up causing lots of disk IO

koakuma-chan 1y ago
In this scenario you would use short-lived connections, and the overhead would probably be approximately the same as reading a file.
julik 1y ago
That would be the FD limit, divided by 3 (the DB itself, the shm file and the WAL).
- ncruces 1y ago
  But each SQLite connection (even to the same DB) will also consume 2 FDs for the DB and the WAL. You'll more easily pool connections to the same DB, of course, but the difference might not be so stark.
  - julik 1y ago
    Something like that, yes. A tenant that hasn't been opened yet - well, you create the tenant first, and then proceed "as normal". With ActiveRecord, your `pool:` configuration defines how many database handles you want to keep open at the same time. I set it relatively high but it can be tweaked, I'd say. And there is automatic eviction from the pool, so if you have a few sites which are popular and a lot of sites which are not popular - it should balance out. There could be merit to "open and close right after" though, for sure.
toast0 1y ago
At some level, it doesn't make a big difference if you've got a file open or not once the file's data falls out of the disk cache, you'll have the same kinds of latency to get it back. Sure, you'll avoid a round or two of latency pulling in the filesystem data to get to the file, but probably not a big deal on SSD. Chances are, the popular databases stay in cache and are quick to open anyway; and the unpopular ones are rarely accessed so delay is ok. But you'd also be able to monitor for disk activity/latency on a system level and add more disks if you need more throughput; possibly disks attached to other machines, if you also need more cpu/ram to go with it. Should be relatively simple to partition the low use databases, because they're low use.

huhtenberg 1y ago

If noshing else, the name is exshellent. Very Sean Connery.

julik 1y ago
Much appreciated! --humbly signed-- a fellow admirer of Geert Van Schlänger

scosman 1y ago

What’s the disk behind this strategy? Lots of sqllite DBs is easy until you have lots of servers, no?

julik 1y ago
Kind of, yes. If you are going to be doing this at scale, you will either need to pin servers to shards of users, or retrieve the DB from some durable storage "just in time", or... use a network mount. Note also, that with small-enough databases you can do the same thing Apple does - fetch your entire tenant from the "big datastore", set a lock on it, do the thing that you want to do - and then write it back into the "big datastore" at the end.
- digianarchist 1y ago
  I guess if the tenant isolation is more important than the disk/network io overhead this would introduce then it's viable solution. It'd probably work better if tenant == user and more specifically tenant == single-user-session.

pushcx 1y ago

Could anyone who runs Rails with sqlite in production share some scale numbers like r/w per second, # rows in the db, and vps size? I have used it at trivial scale for SolidQueue and SolidCache, but I can't find experience reports for the primary OLTP db.

andrewstuart 1y ago

Database per tenant can get real nasty when schemas get out of sync, then you’re in real pain. Postgres with row based access control is a much better solution to database per tenant/strong isolation.

digianarchist 1y ago
I’d heard of using Postgres schemas before but not RLS. Interesting… https://www.crunchydata.com/blog/row-level-security-for-tenants-in-postgres https://www.crunchydata.com/blog/row-level-security-for-tena...
julik 1y ago
That is true, but in my experience this is less of an issue if your migration allow running against "current - 1". If your migration borks on one of the tenants - your system should still work, and you can grab that tenant and investigate. This problem also does happen with sharded databases - which is why most co's have a structured approach of deploying schema changes, observing them having applied everywhere, and only then deploying the feature relying on the schema changes.
- andrewstuart 1y ago
  Yeah but “schemas out of sync!” Is a never ending development and operations problem with SQLite database per tenant. Postgres row level access control gives all the benefits of strong isolation with none of the pain of schemas getting out of sync.
  - julik 1y ago
    You do need to keep tabs on whether they are or not, that is true - and you will have a bit of management to do if they are under constant heavy write load. For me, this all works out because I don't have too many tenants - and I have tools to block writes when running migrations. It's all tradeoffs after all.
devnull3 1y ago
Shouldn't the service daemon upgrade schema and perform migrations on initialisation? Before performing upgrade make a copy of the db file. Also multiple DBs can be inited in parallel. I fail to see why this might be so hard.

devnull3 1y ago

I think an in-between approach could be: 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.

leetrout 1y ago
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.
julik 1y ago
That is what most tenanting scale-ups do. "Jumbo"-tenants get relocated either to separate partitions, or to partitions which are more sparsely populated.

Lord_Zero 1y ago

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.

thethimble 1y ago
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.
- Lord_Zero 1y ago
  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.

napsterbr 1y ago

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") [0] - https://github.com/renatomassaro/FeebDB/ https://github.com/renatomassaro/FeebDB/

hinkley 1y ago

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.

bob1029 1y ago
> 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.
julik 1y ago
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").

DidYaWipe 1y ago

Love the name. That is all.

julik 1y ago
Thanks!

haarts 1y ago

I used something similar in the past. Loved it. User wants their data? Bang! Here's the entire database. User deletes their account? `rm username.sql` and you're done. Compliance was never easier.

breckenedge 1y ago

> And, of course, the “database per tenant” workflow is just starting James Edward Gray spoke about doing this at RailsConf in 2012. https://youtu.be/GRfJ9lni4QA?si=BoDI5gQ8GvM1PDhw https://youtu.be/GRfJ9lni4QA?si=BoDI5gQ8GvM1PDhw

evolve2k 1y ago

Fascinating article documenting a clear challenge over multiple versions of Rails. My read on the subtext of the article is that these issues of proper SQLite support within Active Record won’t fix with a clever gem or quick script but rather need dedicated effort in the rails core. What surprises me a little is that the article is written just now in April 2025 but makes no mention of recent developments in rails. Specially I’m referring to the Rails 8 release. My take away from Rails 8 is that one of the biggest features is significant work towards making SQLite very much a first class citizen of rails, with significant focus on enhancin support for it across the stack. The text below is from the announcement: “Rails 8 No PASS required” “Getting SQLite ready for production On top of the trifecta of Solid adapters that makes it possible for SQLite to power Action Cable, Rails.cache, and Active Job, a bunch of work has gone into making the SQLite adapter and Ruby driver suitable for real production use in Rails 8. At 37signals, we’re building a growing suite of apps that use SQLite in production with ONCE. There are now thousands of installations of both Campfire and Writebook running in the wild that all run SQLite. This has meant a lot of real-world pressure on ensuring that Rails (and Ruby) is working that wonderful file-based database as well as it can be.” https://rubyonrails.org/2024/11/7/rails-8-no-paas-required https://rubyonrails.org/2024/11/7/rails-8-no-paas-required Surely this work bears fruit for the challenges the developer speaks of here. Curious as to if anyone has further insight into what extent Rails 8 addresses the highlighted shortcomings and further if there is a significant gap what would be needed to have this resolved for Rails 9 which I expect is underway.