Rails on SQLite: new ways to cause outages
andre.arko.net187 points by ingve 2 days ago
187 points by ingve 2 days ago
A bit off topic but:
The reason for the "lite" in the name is that it doesn’t run a separate process, it doesn’t listen on a port or a socket, and you can’t connect to it.
The suffix "ite" is derived from the Greek word lithos (from its adjectival form -ites), meaning rock or stone [0]
From the horse's mouth (Hipp's): "I wrote SQLite, and I think it should be pronounced "S-Q-L-ite". Like a mineral. But I'm cool with y'all pronouncing it any way you want. :-)"
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!
:)
The Stack Exchange link is incorrect about -ite being etymologically derived from lithos, as one of the commenters there noted. Maybe a misunderstanding of this wiktionary note or similar:
> 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.
Very well written and reasoned article. I’ve struggled with a lot of the same issues with SQLite prod deployments. They appear simple, but then after you’ve ensured your file is on non-ephemeral storage, sorted out backups, and thought about vertical scaling or having separate dbs for jobs and models, a lot of the benefits over psql disappear IMO.
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...
Sqlite is a bad fit for anything where ephemeral storage is the default. On the other hand is you use a simple VPS there is no problem.
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 enough that it is a concern, then its not a good fit for your use case.
If you need to scale writes.
You can hit 40000-80000+writes/s with sqlite on a 10$ VPS just by batching transactions (i.e wrapping all inserts/updates in a single transaction every 100ms). This is easy to do at the application level, then you also avoid BUSY/LOCK.
I'd argue writes scale better wtih sqlite than postgresql.
I love SQLite but how would batching work in CRUD apps where you need to rollback a dozen SQL inserts/updates in case of error in a request?
Also I often need to read-after-write during the same request, using transactions.
And rails apps are often CRUDy.
With a single writer (as it the case with sqlite). You don't need transactions and rollbacks. As all writes happen in sequence.
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.
I definitely want transactions and rollbacks even if writes happen in sequence.
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.
The batch is still atomic (as it's wrapped in a database transaction). So you batch items will never partially happen (say in the case of a crash).
You do have to write your batch items so that they check their own constraints though. I.e check the accounts have funds etc.
But then rolling back the entire batch would potentially rollback inserts/updates/deletes from multiple independent requests.
I need to bne able to rollback just the queries of a single request.