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
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
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 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.
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.
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.
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).
Yeah i had problems with sqlite3_rsync as well in the beginning but it feels like it got more stable with a couple releases. Not that i am neccessarily suggesting to use it for backups.
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...
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.
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.
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.
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.
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).
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).
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.
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.
Did you end up using strict mode for tables? It looks like not (but I’m on mobile and unsure if GitHub’s search can be trusted), but I think I saw some discussion points around it in the PRs.
pushcx | 13 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:
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.
regalialong | 12 hours ago
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, 🎉🎉🎉🎉
kwas | 12 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
zipy124 | 46 minutes ago
I believe the reason for the defaults is that they maintain strict backwards compatibility, thus defaults are effectively frozen.
kevincox | 7 hours ago
I think you mean WAL2.
ngrilly | 2 hours ago
He probably meant the WAL that is not enabled by default (not WAL2 that lives in a branch).
chrismorgan | 7 hours ago
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.
itamarst | 7 hours ago
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.)
simonw | 9 hours 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?
[OP] thomas0 | 9 hours 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.
sumner | 12 hours ago
I'm curious, what is the backup strategy for the SQLite database? Are you using something like litestream or something different?
[OP] thomas0 | 12 hours ago
There's a nightly job which calls restic.
bakkot | 10 hours ago
Since I've just run in to this question myself: any reason you're doing a full
.backupevery night rather than usingsqlite3_rsyncto do an incremental update of thedatabase-backups/primary.sqlite3file?(I'm asking literally, not making a suggestion; I have very little relevant experience.)
kevincox | 7 hours ago
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).
lep | 3 hours ago
Yeah i had problems with sqlite3_rsync as well in the beginning but it feels like it got more stable with a couple releases. Not that i am neccessarily suggesting to use it for backups.
[OP] thomas0 | 9 hours ago
I didn't know about
sqlite3_rsyncand the default.backupcommand worked well enough for our purposes when we tested it.yawaramin | 8 hours 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).xavdid | 4 hours ago
So in worst case scenario where the host exploded in the 23rd hour, would all the comments/posts from the day be lost?
I've been thinking of moving some read-heavy services to sqlite but have been nervous about the potential for data loss.
robinheghan | 2 hours ago
Check out litestream. It’s live-database replication for SQLite
akavel | 11 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...
zie | 10 hours ago
@bitshift did well.
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.
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.
bitshift | 11 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.
gabeio | 10 hours ago
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/
mhkohne | 10 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.
thesnarky1 | 10 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.
vaguelytagged | 12 hours ago
Out of curiosity how does lobste.rs do updates
[OP] thomas0 | 12 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?
vaguelytagged | 12 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.
[OP] thomas0 | 12 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_journal_mode
edwardloveall | 11 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).
ngrilly | 2 hours ago
What happens during database migrations? I suppose writes are blocked? How long does it usually take?
vaguelytagged | 12 hours ago
awesome, thanks!
tomas | 11 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).
[OP] thomas0 | 10 hours ago
It's just a file, so you could
scpit. Note that lobsters runs on a single vm.yawaramin | 8 hours 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.
lep | 12 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.
[OP] thomas0 | 12 hours ago
I didn't realize SQLite had a built-in function for
if, thanks for letting me know!pl | 12 hours ago
Bravo! Feels lobste.rs is one more step towards staying around for many more years.
pitr | 2 hours ago
@pushcx curious, does it mean that sister sites have to migrate as well to keep up with upstream?
yawaramin | 6 hours ago
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...gecko | 6 hours ago
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.gnyeki | 6 hours ago
The content seems to contain it even if the title or the URL doesn’t.
iamnearlythere | 46 minutes ago
Did you end up using strict mode for tables? It looks like not (but I’m on mobile and unsure if GitHub’s search can be trusted), but I think I saw some discussion points around it in the PRs.