Rails on Sqlite: New Ways to Cause Outages
Posted4 months agoActive4 months ago
andre.arko.netTechstoryHigh profile
calmmixed
Debate
70/100
SqliteRailsDatabase Design
Key topics
Sqlite
Rails
Database Design
The article discusses the challenges and trade-offs of using SQLite in production with Rails, sparking a discussion on the pros and cons of this approach.
Snapshot generated from the HN discussion
Discussion Activity
Very active discussionFirst comment
2h
Peak period
35
0-12h
Avg / period
11.5
Comment distribution69 data points
Loading chart...
Based on 69 loaded comments
Key moments
- 01Story posted
Sep 11, 2025 at 2:58 PM EDT
4 months ago
Step 01 - 02First comment
Sep 11, 2025 at 4:47 PM EDT
2h after posting
Step 02 - 03Peak activity
35 comments in 0-12h
Hottest window of the conversation
Step 03 - 04Latest activity
Sep 16, 2025 at 3:14 PM EDT
4 months ago
Step 04
Generating AI Summary...
Analyzing up to 500 comments to identify key contributors and discussion patterns
ID: 45214933Type: storyLast synced: 11/20/2025, 4:29:25 PM
Want the full context?
Jump to the original sources
Read the primary article or dive into the live Hacker News thread when you're ready.
It had the fascinating property of being a full multi-writer SQL engine when using Page Level Conflict Checking. Even more fascinating: every single client itself becomes a writer.
It involves fiddling the database header to ensure you can keep track of the read-set from the VFS layer, by convincing SQLite to drop its page cache at the start of every transaction.
Other than that, a MVCC VFS is relatively straightforward. Here's an in-memory experiment if a few hundred lines of Go: https://github.com/ncruces/go-sqlite3/blob/v0.29.0/vfs/mvcc/...
I haven't settled on what's a good storage layer for something like this.
One of the issues I have with the mvsqlite approach (beyond not being able to convince myself that it's correct — I'm sure it is I'm just unsure I could replicate it faithfully) is that I don't think the VFS gets any peak at the BEGIN CONCURRENT syntax, so suddenly all your transactions can fail because of write-write conflicts. It's not opt-in.
> In the dream SQLite plus LiteFS world, you have all the advantages of SQLite and all the advantages of a fully replicated multi-writer database setup. Any individual server can go down without causing any downtime for the application as a whole, and every user has a full copy of the application and all its data, running extremely close to them.
These are some of the problems I’m working on solving with Litebase (alpha), which runs on SQLite and distributed storage. Most SQLite solutions rely on replicating data between nodes, but I’m taking a simpler approach by using distributed storage for durability, availability, and replication, combined with a lightweight consensus system between primary and replica nodes.
https://litebase.com
I’m working on getting a public alpha ready, but you can check out the project so far. I’ll create a thread on Hacker News once things are ready.
Litebase writes data to a tiered storage system (local, network, and object) for easier distribution. Using SQLite's VFS API I've also implemented a different type of storage that allows the primary to write data without full consensus with replicas. It's kind of like a multi-versioned log structured merge tree.
SQLite has simply become "good enough" for production. So now you can take that dev default and simply deploy it for apps of a certain size and category if you want.
I can see the reasoning behind it. Simplicity. But I'm waiting for the horror stories like this one to die down before making a final judgement. It just doesn't _feel_ right to me after doing it differently for 20+ years.
Me, I say "sequeLITE" with the emphasis on the last syllable, but now I'm thinking of switching to "SEQuelite". You'll never catch me pronouncing it "ess-cue-ell" either way dammit!
:)
> But by the Hellenistic period, both the masculine -ίτης (-ítēs) and the feminine -ῖτις (-îtis) became very productive in forming technical terms for products, diseases, minerals and gems (adjectives with elliptic λίθος (líthos, “stone”)), ethnic designations and Biblical tribal names.
The meaning of that is not that -ite is etymologically derived from lithos. It’s trying to say that mineral names like “hematite” (αἱματίτης - literally “blood-red”) are originally adjectives agreeing with an implied noun lithos.
The main benefit over psql of course being that you don’t need to pay for a hosted db like RDS, or have a separate database server.
I’ve found a happy middle ground in simply self-hosting psql and my apps on the same VPS with something like dokploy. Local development is still easy enough, and remote deployment in containers is 1-click with Dokploy, and ends up being simpler to reason about IMO. My take below, if anyone’s interested.
https://nikodunk.com/2025-06-10-diy-serverless-(coreos-+-dok...
There are multiple simple ways of doing SQLite backups https://sqlite.org/lang_vacuum.html#vacuuminto https://sqlite.org/rsync.html - or just lock and copy the file.
If you need to scale enough that it is a concern, then its not a good fit for your use case.
If you need to scale writes.
I'd argue writes scale better wtih sqlite than postgresql.
Also I often need to read-after-write during the same request, using transactions.
And rails apps are often CRUDy.
Each batch item can be a combination of read/write/update that happen in sequence and therefore can give you the same semantics as a traditional transaction/rollback. eg:
- read -> Does the account have enough funds?
- write -> transfer 100$ from this user to another account
This is also much simpler to write than in other databases as you don't have to worry about n+1.
To go with your example, take something like
1) add $100 to this user's account 2) add $100 to the service fees account 3) deduct $101 from the other user's account to cover these
Must all happen or none.
You do have to write your batch items so that they check their own constraints though. I.e check the accounts have funds etc.
I need to bne able to rollback just the queries of a single request.
Ah you're doing request response? sqlite/single writer fits much better with a CQRS still approach, so in my experience you move away from request/response to something push based. That being said even in a request/response model wrapping a request in a transaction is not a great idea. The minute you have anything that takes some time (slow third party) that transaction is going to sit open and cause trouble.
Why jam SQLite in server side environments? Embedded and mobile devices I can see a case for
The problem with making this the default is that people then think it is the recommended way, even if it is not. It is the easiest way to fast get something up and running locally, but it is typically not the best for a production setup. Experienced users will know this, but for beginners to these frameworks, it leads them in the wrong direction.
Been following the Cloudflare features with interest along the way... currently have a pretty good size VPS I've been using.
20 years ago it was MySQL and now it's usually postgres, and these are both still fine. Easier than ever to get setup and going in production. Outside of quite niche needs, I can't understand why one would put up with the pain of using sqlite. Plus it's setting up an even more painful migration later if you do grow.
I think its more about the issue that databases like postgres are too much "magic". With Sqlite you see your database file, its a physical something. You want to move it to another server, its just a copy action, no dump/restore. You want to "upgrade", there is no mess like with postgres.
You want to create client separation, ... its just a file per client.
> although even there I have to be aware of the one writer only limitation.
The writer limit has not been a limit forever. You can easily do 20k writes per second, what is way beyond what most system anyway. You want 100k? A slightly different access pattern and voila. You want 1 million ... There are ways around it. If you put Sqlite vs Postgres for instance, ironically, seen Sqlite winning against postgres despite that single writer.
The whole hands on "we know what file is, where it is, and how to gain access to it" is really amazing. The whole "do not need to think about how to secure it on the network" also helps a lot.
Do i use Sqlite in production? No ... Postgres its extension support just rules. But i see the appeal for many.
Sure, if you don't mind downtime?
The problem with that thought is that in order for sqlite to be as full featured as it is, it also has a lot of "magic". I've also had to dive into the postgres code a few times to understand how it's doing something, and I've generally found it well structured and easy to navigate, and surprisingly terse - there's not that much "magic" really.
Do you have any resources on this? I'm working on a project with sqlite, with multiple threads writing, and want to squeeze out a little more performance.
I love that SQLite lets me run the app locally with no "setup". And so I actually kinda like Turso and D1 for bridging the gap. I can have the best of both worlds: no setup, AND primetime-ready deployment.
https://bunrouter.uptrace.dev/guide/go-zero-downtime-restart...
Thanks to Litestream, it's easier to scale horizontally when using SQLite than with other databases when self-hosting.
How litestream handles master-master or promoting a slave and switching back later isn't clear to me.
https://joshkaramuth.com/blog/django-deployment/
But it doesn't go over multi-instance coordination
The other instances are mostly redundant. That's how sites like Hacker News tend to operate, and why I chose this architecture. It's simple and stupid but doesn't work in scenarios where you have multiple servers in different locations for example. There's LiteFS to solve this problem but I don't have experience with it yet.
Cheers
If I had to start something new, I'd use SQLite until at least high 5 digit queries per second. Maybe more.
Now I’m frowning at people doing all sorts of gymnastics to beat SQLite into shape.
It’s always the same, people without a mind of their own looking at blog posts from cool kids and deciding they have to do it themselves too (not all the adopters of course, but most of them).
What about all the important things you need in a database such as multiuser/remote access and other such things?
What do the "sqlite in production" people do when they need to examine the database/resolve an issue/do development? Also what about the limitations of sqlite in modifying database structure?
The simple answer to your question is you don’t. And if you do need them you don’t use SQLite.
Can you even redeploy a sevice like this without downtime?
Why can't you just use a copy of the database when you need to examine it or resolve issues?
None of these things have anything to do with being a "toy" app or not. They are matters of scale and business requirements. But that's the point isn't it? There are plenty of non-toy systems where none of these requirements apply.
Plenty of very successful business and applications could handle a brief downtime for deployment. Plenty don't need multiple users or remote access, plenty have reasonably small databases.
It is implemented like this:
- The front end uses react to draw a UI.
- It fetches data from a JSON file stored on S3.
- When we get a write request, a lamdba function reads the JSON file, parses it, adds new data, and writes back to S3.
The main selling point for me is that almost all of it is static assets, which are cheap and simple to host, and the tiny bit of "back end" logic is just one nodejs function.
The previous implementation used SQLite and a golang back end, but I eventually got tired of having to maintain a virtual machine to host it.
I ever plan do it with sqlite, loading it at memory during app start and flush data to s3 during runtime but it create more corner cases and logic to handle.
> Feed Your Email is only possible as a service because of SQLite—if I had to maintain three Postgres instances and a couple of web instances and a couple of worker instances, I would have felt like it was too much hassle and cost too much money, and given up.
What's the issue to run MySQL instead and all on one server? You can do it for $5-10 on a Hetzner server.
The SQLite DB file format has been backwards-compatible for decades. Update the client and that’s it—you’re using the newest version.
The real nice thing about SQLite in prod though is how much it simplifies development and deployment. Let's be real, a lot of apps people build are relatively small, basic CRUD apps that see modest usage or are even internal-only, SQLite is perfect for that. And SQLite can scale reasonably well on modern hardware. It follows the premise of Rails quite nicely: it gets you up and running, scaling to the point you're making a decent amount of money and then you can figure out how to scale beyond that, if you need to.
> I was still caught out by some of the ways things are different.
I feel this is the problem with frameworks, they have to keep evolving and growing and even people who have been doing it 20 years can get caught out.
But the architecture of the Web has basically not changed that much. We are collectively shooting ourselves in the foot.
Definitely not just a Rails thing.