lobste.rs is now running on SQLite

1. 148 lobste.rs is now running on SQLite ☶ 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." * meta authored by thomas0 5 hours ago | 28 comments

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:

1. The SQLite gem supports user defined functions (udfs) and we used it to implement some missing functions in SQLite like regexp, if and stddev so that we wouldn't have to deal with too many sql migration workarounds. 2. SQLite doesn't support unsigned bigints. Previously, the mariadb was using unsigned bigints for certain ids, so we had to switch those to bigints for the migration. 3. Collation in SQLite is rather weak compared to MariaDB. Lobste.rs used utf8mb4\_general\_ci in MariaDB, but used NOCASE in SQLite. The downside of NOCASE is that it only supports ASCII characters, not the full UTF case folding. 4. Use the preferred Contentless-Delete Tables in SQLite for your full text search tables. These are not the default. I'm constantly surprised by the default choices of SQLite.

Rails lessons:

1. The default PRAGMAs in Rails seem to be working for lobsters. 2. You typically don't think about it but database migrations are database specific. I had to move the old migrations out to an old migrations directory so that db:migrate would continue to work.

Lobsters codebase lessons:

1. There is a search parser in the lobsters codebase. 2. I learned about heinous\_inline\_partials which is a hack to speed up rendering. 3. The lobsters testsuite was essential in making sure I could migrate to SQLite without a ton of manual testing.

Overall lessons:

1. I think a key ingredient in making this work was good communication from everyone that participated. I don't think this would have been possible otherwise. 2. Migrating the underlying database without having access to the production database is really hard to get right. This was my first underlying database migration without having access to production. One lesson I'll take away from this is that I'll make sure to have realistic dataset sizes before doing another underlying database migration in the future.

Wishes:

1. 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. 2. I wish creating a production like dataset would be much easier than having to manually write something and waiting a week.

1. Preview 2. 1. 36 pushcx Sysopedited 5 hours ago 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:

1. The person who volunteered to do the work used SQLite. 2. 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. 2. 13 regalialong 4 hours ago > 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, 🎉🎉🎉🎉

1. \~ kwas 4 hours ago 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 3. 5 sumner 4 hours ago I'm curious, what is the backup strategy for the SQLite database? Are you using something like litestream or something different?

1. \~ thomas0 4 hours ago There's a nightly job which calls restic.

1. \~ bakkot 1 hour ago 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.)

1. \~ thomas0 1 hour ago I didn't know about `sqlite3_rsync` and the default `.backup` command worked well enough for our purposes when we tested it.

1. \~ yawaramin 52 minutes ago See also `vacuum into file.db`. It compacts and deletes unused space in the database copy (however it takes a 'bit' longer and is a blocking operation). 4. 5 simonwedited 1 hour ago 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?

1. 7 thomas0 1 hour ago 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. 5. \~ vaguelytagged 4 hours ago Out of curiosity how does lobste.rs do updates

1. \~ thomas0 4 hours ago 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?

1. \~ vaguelytagged 4 hours ago 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.

1. \~ thomas0 4 hours ago 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%5Fjournal%5Fmode>

1. \~ edwardloveall 3 hours ago 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). 2. \~ vaguelytagged 4 hours ago awesome, thanks! 3. \~ tomas 3 hours ago What if you need to move between VMs? Is the sqlite folder on a network volume so that it can be re-attached? (That would still require a couple of seconds between shutdown and startup).

1. \~ thomas0 2 hours ago It's just a file, so you could `scp` it. Note that lobsters runs on a single vm. 2. \~ yawaramin 44 minutes ago 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. 6. \~ lep 4 hours ago 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.

1. \~ thomas0 4 hours ago I didn't realize SQLite had a built-in function for `if`, thanks for letting me know! 7. \~ pl 4 hours ago Bravo! Feels lobste.rs is one more step towards staying around for many more years. 8. \~ akavel 3 hours ago 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...

1. 5 zie 2 hours ago @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:

* <https://fractaledmind.github.io/2024/04/15/sqlite-on-rails-the-how-and-why-of-optimal-performance/> * <https://kerkour.com/sqlite-for-servers> * <https://www.sqlite.org/lang%5Ftransaction.html#immediate> See the 2nd link around SQLITE\_BUSY and the 3rd link for more details. 2. \~ bitshift 3 hours ago 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.

1. \~ gabeioedited 2 hours ago > 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/> 2. \~ mhkohne 2 hours ago 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. 9. \~ thesnarky1 2 hours ago 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.