lobste.rs is now running on SQLite
191 points by thomas0
191 points by thomas0
This past Saturday, @pushcx and I deployed the SQLite pull request to production. We were waiting till this morning to see how it would react to the Monday traffic spike before making this post. Needless to say, SQLite seems to have passed with flying colors: cpu usage is down, memory usage is down, site seems to be snappier at least for me, 1/2 the vps cost once mariadb vps is taken down, and finally "We're having a quiet Monday.". Finally #539 Migrate to SQLite was closed this morning.
Let us know if you have any questions about the migration.
Background Story:
I got involved with this migration because back in 2019 I stumbled upon #539 and because I had lots of experience working with, managing and migrating largish databases, I left a comment suggesting MySQL as an alternative, because of the compatibility between MariaDB and MySQL. At that time I wasn't planning on getting involved since there were already conversations in place to migrate to PostgreSQL.
Fast forward to 2025, Rahul left a comment mentioning K1's acquisition of MariaDB. A discussion around the details of migrating to postgresql proceeded. Then in February, Rahul asked "Can lobsters run on sqlite? which included a very detailed post around SQLite.
I officially showed interest in taking on this project in June 2025. I think this somehow got mentioned in lobsters office hours but it has been so long since then that I don't rememeber for certain.
In August 2025 I opened my first pull request attempt when I got busy and couldn't attend to the PR. Github closed it as stale and I couldn't reopen it so I opened another PR. The second PR attempt included some performance testing, a database x to database y script (since none of the existing mariadb/mysql to sqlite scripts satisfied me), debugging and thinking around data integrity.
Then came the first deploy on Feb 21st. @pushcx and I got on a call, came up with a checklist for the deployment. Everything went right up until the deployment of the PR. Once deployed the site was in readonly mode, but just the readonly traffic was spiking all the cpus to 100%. We couldn't figure out what the problem was so we decided to revert. I didn't feel great after that first failed deploy since I knew that performance could be a problem due to not having access to the production database.
Two days after the failed deploy I opened the 3rd and final pr attempt. I fixed some minor issues with search that were discovered during the failed deploy, created a bulk data creation script which took a week to get half of lobsters' data set size created locally, and committed the three changes that fixed the performance issues during the first deploy: 1, 2, 3. The performance issues boiled down to SQLite doing full table scans on the largest tables in the database for 2 of queries and the 3rd one solved an n+1 issue. During the morning of the second deployment, I also added a slow query log just in case there were more performance issues during the deployment.
Then came the second deploy on July 11th. @pushcx and I got on a morning call and came up with a deployment and revert checklist. Everything was going smoothly and then @pushcx merged and deployed the PR. Once deployed the site was still live and the cpu/memory usage was still good. This was a big relief for me. We monitored site metrics and irc for people mentioning issues, which a couple did and those were promptly fixed: 1, 2. Overall the site seemed to work so we wrapped up the call and waited till Monday when the traffic spikes.
Monday came and the site is stil happy so we're calling this a win and moving on.
SQLite lessons:
Rails lessons:
Lobsters codebase lessons:
Overall lessons:
Wishes:
My huge thanks to @thomas0 who took on this big project, was thoughtful and thorough in all his work, and who was resilient to the surprises we had along the way. Thanks to @355E3B who helped with planning and ops. Thanks also to the folks who contributed to #539 and its related issues, and to #lobsters who turned our read-only hours into a party. I really appreciate that everyone had healthy expectations about the partial outages and even had fun with the situation.
I've heard "why not PostgreSQL?" a few times this week. It was even our original plan in #539! Well, it was a pragmatic choice in two different ways:
The person who volunteered to do the work used SQLite.
I don't want to use solutions that are bigger and more complex than our likely needs. Postgresql is my default for projects, but it does have the added complexity of being a separate service to run, tune, and maintain.
As a pleasant surprise both our CPU and RAM usage dropped (I expected an increase and steady, respectively). There's a chart with more details at the end of #539.
I'm constantly surprised by the default choices of SQLite.
Yeah, SQLite feels like something that would benefit from a greenfield mode with better defaults like WAL, foreign keys, synchronous normal... I'm sure others have suggestions / already wrote about other things too
C'est la vie I suppose.
Either way, 🎉🎉🎉🎉
IIRC there was also something about scheduling/format of on-disc changes that really benefitted HDDs while being really substandard for NVMes. But it's only a hazy recollection
This is a very impressive upgrade! Really love how much detail there is in the PR + issue + Gist.
Are there any noticeable functionality changes as a result of this, e.g. to site search?
How big is the SQLite file on disk now?
The only thing I can think of is that we're using SQLite's ranking which may differ from the way MariaDB returned the results. There are tests around searching, but little testing around rankings, so my suspicion is that there is some functional changes there but I have no evidence.
IIRC it was mentioned somewhere that the SQLite file is 3.8g.
I wish we could say in a test, "Fail if you encounter any full table scans". Which would have caught the perf issues we experienced during the first deploy.
This is possible with sqlite!
You can see this e.g. in the testing approach in the Axiom object database/ORM, which is built on top of SQLite. When you run a SQLite query, you can measure how many SQLite bytecode instructions were run, which is far more consistent than measuring run time. This is exposed to Python, at least.
Axiom uses this functionality to test that queries’ performance is behaving as expected. For example, you can write a test that checks if the query is efficient or not, by running the same query on multiple table sizes. If the query is inefficient and results in a linear scan, the number of bytecodes executed will grow linearly with the size of the table. But if the query is able to use indexes, the number of bytecodes executed will not be linear, it should be a pretty small number regardless of the size of the database table.
(Mostly copy/pasted from https://pythonspeed.com/articles/testing-compiler-optimizations/ where I originally wrote this.)
Github closed it as stale and I couldn't reopen it so I opened another PR.
Ugh. Kill such stale bots, with prejudice. Although they may have value on work trackers (dubious, but a legitimate argument can exist), they emphatically have no place on bug trackers or pull request queues on public repositories/projects like this. They achieve nothing useful (there’s nothing wrong with leaving things open indefinitely or for manual closing) and cause definite harm. They’re intensely hostile to contributors, particularly.
This stale bot should never have been enabled in the first place; I recommend that it now be disabled.
I'm curious, what is the backup strategy for the SQLite database? Are you using something like litestream or something different?
There's a nightly job which calls restic.
Since I've just run in to this question myself: any reason you're doing a full .backup every night rather than using sqlite3_rsync to do an incremental update of the database-backups/primary.sqlite3 file?
(I'm asking literally, not making a suggestion; I have very little relevant experience.)
Just a word of warning I experimented very lightly with sqlite3_rsync and ended up with corrupted destination databases repeatedly. It also felt very unpolished in many ways (inaccurate docs, poor escaping of SSH commands).
I wouldn't trust it in prod (or at all really).
Out of curiosity how does lobste.rs do updates
Using the update statement. Not sure if that answers your question. If not could you elaborate on what kind of updates you're thinking of?
Oops that’s on me for being unspecific. More of how does actual updates to the site work.
Let’s say you want to change logic on the backend. With something like Maria people usually spin up 2 servers then switch over the traffic with a reverse proxy.
So with the SQLite architecture how does one do zero downtime updates.
For the rails backend, hatchbox handles zero downtime deploys. I don't know the details of it, but maybe @pushcx knows more?
For SQLite, it's just a local set of files. We use the rails default journal mode set to WAL which allows concurrently many readers and a single writer to exist at a time. You can read more about WAL mode at https://sqlite.org/pragma.html#pragma_journal_mode
Hatchbox uses Capistrano which is older, but still widely used to deploy rails apps. On a high-level it creates a new directory, pulls down the new code, runs migrations, then updates a symlink to point to the new version. Any failure means the symlink doesn't update (but you still have to deal with other side effects).
To answer the question in more general terms, systemd socket socket activation can achieve zero-downtime deploys because systemd holds the connection socket on behalf of your service and keep queuing connections there during the brief time your service is restarting. I've never used it myself but it's a well-known technique.
Very cool. I followed this from the sidelines. I don't have much to add except that sqlite does have an iif-function, but having custom functions living in the same space is cool in itself.
Sorry for a naive question - I always assumed that SQLite doesn't allow the same level of concurrency as PostgreSQL/MySQL/... I admit my understanding of its concurrency model is rather vague and unclear - even just now I tried to read to understand it better, and I can only say I'm still confused. Based on the Overview section, should I understand that SQLite nowadays can be multiprocess - only the requirement is, lobste.rs has to run on a single server machine, is that so? And then through some magic ("share a small amount of memory" - so, mmap?), the processes get internally invisibly synced by SQLite's library, so that there's at most one write done concurrently, but multiple reads are allowed, yes? Also, are the writes blocked in some kind of queue waiting for their turn? I'm fine if you don't care to reply, or direct me to some RTFM, though I'd be grateful if it was aimed at someone with little knowledge of DB internals jargon and of "basic stuff" in that area...
@bitshift did well.
Also, are the writes blocked in some kind of queue waiting for their turn?
Yes-ish. It does have one, but the default situation is pretty terrible, but you can make it sane. Here is my current make SQLite sane config along with sources for more info.
"PRAGMA foreign_keys=ON;"
"PRAGMA journal_mode = WAL;"
"PRAGMA synchronous = NORMAL;"
"PRAGMA busy_timeout = 5000;"
"PRAGMA temp_store = MEMORY;"
"PRAGMA mmap_size = 134217728;"
"PRAGMA journal_size_limit = 67108864;"
"PRAGMA cache_size = 2000;"
also turn on immediate mode so the busy_timeout works.
Sources:
See the 2nd link around SQLITE_BUSY and the 3rd link for more details.
The SQLite FAQ might answer some of your questions!
The quick summary is you can have multiple readers (even across multiple processes), but only one writer at a time. SQLite uses filesystem locks to enforce this. It doesn't say a whole lot about how writes are queued, but it sounds like there's some kind of sleep-and-retry behavior—so not an explicit shared queue in memory or anything.
I'm not well versed enough to say if using SQLite across multiple machines is impossible, but naive approaches like sharing the file via NFS are explicitly warned against. So "a single server machine" seems like a reasonable first approximation at least. Fortunately a single machine can go surprisingly far these days.
I'm not well versed enough to say if using SQLite across multiple machines is impossible
Mostly depends on how you layout your tables & files. If you shard the databases then multiple machines can act as writers for their shard. You can also split read requests from write requests and have read only machines scale up/down as much as you’d like. You can use multiple files in a query (there is a limit there).
So for example you can split the user table based on the first letter of the username and then depending on the rest of the database either a database file per user or per customer (organization). Of course more of everything is manual but it’s not as hard as you’d expect if you build for it.
https://rivet.dev/blog/2025-02-16-sqlite-on-the-server-is-misunderstood/
NFS is no good because of the file locking thing, but I have used it in a situation with Windows machines and Samba shares - those support file locking and as long as you don't have too many writers, you can generally get away with it just fine.
Congrats! I am a huge fan of SQLite and fully support not introducing unnecessary complexity and scaling where it isn't needed. Great work and thank you for the lovely breadcrumbs all the way through this write up to follow along.
Search seems to be kinda...broken. Eg https://lobste.rs/search?q=tailscale&what=stories&order=newest
Many (most?) of the results have no mention of the word tailscale...
I think that's working: first link is to GitHub's Tailscale org, second talks explicitly about Tailscale in the article, third is by its CEO (and says so prominently). Lobsters uses story text when searching stories, which has had this type of result for awhile. You can always use title: if that's specifically what you want.