Stephen Margheim writes about running SQLite in production with Rails, including performance tuning, concurrency, backups and Litestream.
HN commenters recommend his SQLite on Rails series as where they learned most of what they know, though one finds it works around SQLite's write limits.
Missing from this, but very important: you can connect to the same SQLite database file multiple times inside the same process (e.g. in different threads) but you can also connect to the same SQLite database file from multiple different processes on the same machine (with the same filesystem - NFS will lead to all kinds of nasty problems).
Easy way to prove this to yourself: run two different terminals, run "sqlite3 data.db" in each one, run create table / insert / update queries in one and watch the results become visible to selects in the other.
Changes only become visible to other connections after they have been committed.
In default mode any writes will hold an exclusive lock and will cause any reads to be blocked waiting for that lock. In WAL mode reads can continue even while writes are taking place.
Writes can only run one at a time, but can queue waiting for each other to finish - and since most writes take microseconds to run this is very rarely a problem.
The most complicated thing to understand is the need to use BEGIN IMMEDIATE TRANSACTION in concurrent environments to avoid the occasional SQLITE_BUSY exception. The best explanation I've seen of this is here: https://fractaledmind.github.io/2024/04/15/sqlite-on-rails-the-how-and-why-of-optimal-performance/ - search for SQLITE_BUSY.
another article praising sqlite in production and working around a known sqlite limitation: concurrent writes. As they link in the article:
> But, my favorite feature of the gem is its improved concurrency support.
> [..] https://fractaledmind.github.io/2023/12/11/sqlite-on-rails-improving-concurrency/
Really. At the point you are experiencing database locked in your productive app that uses sqlite as backend, i would strongly suggest to use another database backend that was designed with concurrent writes in mind.
Nothing against sqlite in production, its nice, as long as your workload meets its feature set.
There are benefits to having a single writer connection (and multiple readers).
One is that you can get fairer and more efficient locking. The (interruptible) file lock APIs that SQLite uses lead to a lot of sleeping and retrying that wastes time, and is not very fair.
https://fractaledmind.github.io/2024/07/19/sqlite-in-ruby-backoff-busy-handler-problems/
Another some APIs, like the backup API, work better if they can track writes, and (e.g.) backing up from the single writer connection helps there.
I went through this presentation looking for "how do you do backups" and it glosses over it. But the author blogged about that separately [1].
It seems he uses Litestream with DigitalOcean Spaces for this. Looks like they start at $5 per month for 250 GB [2]. Would that be the best way for a hobbyist to get started?
[1] https://fractaledmind.github.io/2023/09/09/enhancing-rails-sqlite-setting-up-litestream/ [2] https://www.digitalocean.com/pricing/spaces-object-storage
I don't know about 1TB data but I think it was used in some missile system so I would say yes for mission critical data, not sure if we think about mission critical in the same terms.
Edit: by pure chance I hit that just a moment ago: https://fractaledmind.github.io/2024/10/16/sqlite-supercharges-rails/
Stephen has a whole series of blog posts on SQLite (in Rails) that I highly recommend. I've learned most of what I know about SQLite from him!
https://fractaledmind.github.io/2024/04/15/sqlite-on-rails-the-how-and-why-of-optimal-performance/
Also, hello Lee! I miss being in a Slack with you!