lobste.rs is now running on SQLite

lobste.rs · 2026-07-15 · by Long Nguyen ·generic
98 comments

lobste.rs is now running on SQLite

1. 416 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 43 hours ago | 98 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. 114 pushcx Sysopedited 43 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. 53 chrismorganedited 37 hours ago > 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.

1. 21 pushcx 24 hours ago I installed the stalebot because, over years, we've had a lot of people open and abandon PRs. I ask for changes, sometimes even very minor ones like a typo fix, and they never return. I can't force anyone to complete a contribution, and I find it really tiring to try to write non-judgemental pings for updates that also encourage people to pick things back up, or to finally say that I'm closing their PR. Similarly, Lobsters has a number of restrictions on new users that cause very little emotional reaction when an automated system enforces them but carry emotional complexity when they're a message from a mod. The primary problem is that I had a bunch of personal stuff happening this year that dragged me away from maintenance. It was very difficult to schedule a weekend that had both a 4-6 hour window for the primary migration and was also open enough to feel confident that we'd be sure to handle the inevitable follow-up work of difficult-to-predict severity. Then the secondary problem here was a GitHub limitation/misdesign. Because the first PR #1871 was merged, Thomas couldn't reopen it. I think maybe it included the complexity that he pushed a commit to his branch before he went to try to reopen it. I think GitHub would've allowed a committer to reopen it, but that wasn't clear to Thomas at the time, so he pushed a new branch.

1. \~ abnercoimbre 22 hours ago Huge appreciation for including the people-oriented realities in your explanation. Technical work never occurs in a vacuum! 2. \~ ngoldbaum 27 hours ago Why is it so terrible to open a new PR and link to the old one?

1. 9 pushcx 25 hours ago The GitHub PR UI is clunky, how you have to endlessly scroll up and down looking for new replies, figuring out what "resolved" conversations were, rebuilding the timeline of changes in your head. Having to do that on two PRs at once that to follow a long-running change multiplies the difficulty. 3. 45 regalialong 42 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. 5 kwas 42 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 2. \~ zipy124 30 hours ago I believe the reason for the defaults is that they maintain strict backwards compatibility, thus defaults are effectively frozen.

1. 30 mort 27 hours ago That's where the "greenfield mode" comes in: use the old defaults by default, but have a simple way to change the defaults to ones which make more sense today. Instead of 100 different pragmas and per-table attributes, we could have a `pragma edition = 2026` which changes the defaults.

1. 11 thomas0edited 26 hours ago This is a really good idea. I had to read a lot to figure out what PRAGMA did what, luckily rails sets a lot of these, but it felt a little bit overwhelming. For example we didn't implement strict tables mainly because the default worked, but I would say strict mode tables should be the default now.

1. 6 chrismorgan 24 hours ago > I would say strict mode tables should be the default now. The developer disagrees with you, saying flexible typing is a feature. I disagree with you for a different reason: strict mode completely prevents something better at the application layer. Things like the Rust sqlx crate (and I know of examples in other languages) will map type names to appropriate types, handling serialisation and deserialisation automatically. For example, `BOOLEAN`/`bool` or `UUID`/`uuid::Uuid`. By restricting the column type names, strict mode thwarts this application-level type safety. And general schema self-descriptiveness so that people become much more likely to misuse a column, I think. If there was even simple `CREATE TYPE` aliasing (letting you control affinity, currently a hazard in using custom types), I'd be all for strict mode, but without it I will actively discourage it. 3. 1 kevincox 37 hours ago > like WAL I think you mean WAL2.

1. \~ ngrilly 32 hours ago He probably meant the WAL that is not enabled by default (not WAL2 that lives in a branch).

1. \~ regalialong 22 hours ago Realized I should probably have put pronouns into my bio... But yeah, in the place where SQLite is used most (as embedded database), WAL would be a good default, you could still disable WAL it if it's not useful. I didn't even know WAL2 was a thing 4. 35 itamarst 37 hours ago > 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.)

1. 10 ankhers 25 hours ago Just to make this extra clear, your table needs to be sufficiently large in order for the database to actually use an index. If you have a table with only a handful of records, which is common in unit tests, you will always have a full table scan because it will actually be faster to just run through a few records in memory instead of jumping around between the index and main table data. You also need sufficiently varied information. E.g., if you have a `deleted_at` column and most things are not deleted, the index may not actually be used. I have done this kind failed CI testing with poorly performing queries before, with the exception of using postgresql instead of sqlite. I also took advantage of using `EXPLAIN` instead. Note that my db knowledge comes from postgresql, it is possible that sqlite acts completely differently. 5. 21 simonwedited 40 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?

1. 23 thomas0 39 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.

1. 5 kwas 28 hours ago One bug is that <https://lobste.rs/~usr/standing> doesn't work since the migration.

1. \~ thomas0 27 hours ago Please report any bugs you see to <https://github.com/lobsters/lobsters/issues> 2. \~ Halkcyon 24 hours ago > IIRC it was mentioned somewhere that the SQLite file is 3.8g. Wow, that is surprisingly tiny! Is there any archiving / old data retrieval systems in place? Or is that the complete state of the site?

1. \~ thomas0 24 hours ago I don't think there's an archival system in place. That's just the size of the primary db. There are other dbs that help the site, like caching, background jobs, and recently @pushcx split rack-attack 2. \~ pushcxedited 20 hours ago Old jobs eventually get flushed out but most everything is soft-deleted with `deleted_at` booleans. Private messages get deleted when both users have deleted them, that's it off the top of my head. (Edit: I remembered that when you unvote/unflag we delete that row.) There's some high-level stats in our recent birthday post. The cache is 1.1G and the queue is 218M. rack\_attack is 555M but it hasn't yet been running 8 days to hit its full retention period, and it swings significantly when multiple scrapers with hundreds of thousands of IPs hit the site. 3. \~ pushcx 20 hours ago FWIW, the ratio of newest:relevance searches is over 2:1. I made it the default because I'm always in there going "where was that great comment I read just the other day?" but it doesn't hurt that it's computationally cheaper. 6. 12 sumner 42 hours ago I'm curious, what is the backup strategy for the SQLite database? Are you using something like litestream or something different?

1. 13 thomas0 42 hours ago There's a nightly job which calls restic.

1. 5 bakkot 40 hours 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. 12 kevincox 37 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).

1. \~ lep 34 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. 2. 6 thomas0 40 hours ago I didn't know about `sqlite3_rsync` and the default `.backup` command worked well enough for our purposes when we tested it.

1. 3 yawaramin 39 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). 2. \~ xavdid 34 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.

1. 8 robinheghan 32 hours ago Check out litestream. It’s live-database replication for SQLite 2. 5 pushcx 24 hours ago Yeah. We could schedule the job more often but this felt like a reasonable compromise. Litestream and more frequent incremental backups would be great follow-up work, I'll file a feature request.

1. \~ atmosx 18 hours ago What’s the size of the raw SQLite file?

1. \~ simonw 16 hours ago \~3.8GB, see comments here and here.

1. \~ atmosx 10 hours ago Ty 3. \~ thomas0 27 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? Yes that's what I believe would happen. This was true with the MariaDB setup as well. 7. 9 akavel 42 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. 18 zie 40 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.

1. 5 mortedited 26 hours ago If only there was a pragma to make all tables strict T\_T

1. \~ zie 25 hours ago I dunno if I'd turn that on. With liberal use of CHECK() you get the same thing, and then it's very clear what you are forcing as valid values in the column, and it gets documented where its easily read. Perhaps if there was like a mode command like `.edition = 2026` that set better defaults and included strict mode. I can't remember if strict mode still lets you set your own column types? I find great utility in setting my own column data types in SQLite. If strict mode doesn't let you, I'd almost certainly never turn it on.

1. \~ mortedited 25 hours ago Is your concern with regard to CHECK() in strict tables that you're losing some performance since you perform both your custom CHECK() constraint and the automatic type check? Or what would be the issue otherwise with keeping both your liberal use of CHECK() and automatic type checks? What do you mean by "your own column types"? In both strict and non-strict mode, the set of types SQLite knows about are integers, reals, text, blob and null, no? Do you mean how you can set the column type to e.g `DATETIME` or `BOOLEAN` or `BOBMARLEY` and SQLite will default to `NUMERIC` affinity because it doesn't recognize it? Strict mode does indeed disable that. Though you may want to ask yourself if automatic conversion from text to integer or double is the behavior you intended for your `id UUID PRIMARY KEY` column.

1. \~ zie 24 hours ago Around CHECK() vs STRICT, I'm not against STRICT, but I'm for encouraging good habits.

* CHECK() gives you more flexibility around what is allowed * When sharing your file with other(s), the chances of them understanding a CHECK() is also much higher * It transfers well against other databases. As far as I know STRICT is a SQLite only thing. Around performance, I honestly didn't think about it. If SQLite isn't fast enough for you, the very minor bump(unmeasured) is probably not going to make any difference in if you can use it for your use case or not. > Do you mean how you can set the column type to e.g DATETIME or BOOLEAN or BOBMARLEY and SQLite will default to NUMERIC affinity because it doesn't recognize it? Strict mode does indeed disable that. Yes, this. I use this all the time, since I can define how to bring that type into my language, so I can set a complex data type, name it something and let me define the conversion to/from SQLite at the DB connection layer, which is really nice. > Though you may want to ask yourself if automatic conversion from text to integer or double is the behavior you intended for your id UUID PRIMARY KEY column. Agreed, though I think UUID's as primary keys, especially in SQLite is probably not the best default choice. Sure it makes sense sometimes, but it's likely over-used a lot.

1. \~ mort 23 hours ago Oh I understand the utility now! So you would do something like: ``` CREATE TABLE blah ( id INTEGER PRIMARY KEY, created_at DATETIME, properties JSON ); ``` And then you'd have a connector which automatically knows to parse/serialize `created_at` as a `Date` object and `properties` as a JSON value. That seems extremely useful. The type affinity and type conversion part of non-strict SQLite is kind of a downside, and you may accidentally have a custom type which contains "REAL", "FLOA", "DOUB", "CHAR", "CLOB" or "TEXT" and accidentally end up with a column which behaves subtly different from all the others. I would prefer a solution where you can define type aliases and have strict tables which only accept columns of pre-defined types. But since SQLite doesn't have that at the moment, I understand that just restricting the types you can use is a step backwards.

1. \~ zieedited 23 hours ago Exactly. It's amazingly useful! As an example here is the Python documentation around it: <https://docs.python.org/3/library/sqlite3.html#sqlite3-adapters> You can basically do the same thing in PostgreSQL, but it's done differently. There you teach PG about the type and then you have to teach your DB language driver about the type and conversion stuff as well. It's a little more annoying than the SQLite way of doing it. The upside, it's a native DB type now, so all the magic that PG gives you, you also get for your custom data types. I don't know about other database systems, I don't really use anything else. I think Oracle kinda lets you do something like this, but if I remember it's such a PITA that it's never worth doing. Though it's been a long time since I've done DBA stuff with Oracle, so I could be wrong/out of date. Though you missed all the CHECK()'s in your example CREATE TABLE :) haha

1. \~ mort 22 hours ago Is it okay if I cite you and link to our comment in a blog post I'm writing? I got inspired by this thread to write up something about things that annoy me about SQLite: <https://mort.coffee/home/sqlite-editions/>. It's an unpublished draft so far. I've cited your comment regarding custom types.

1. \~ zieedited 18 hours ago Of course, this is all public anyway. Go for it. I think the killer feature, that you didn't mention here is the custom type name along with check(). That way you can ensure at the DB level that your column only contains the stuff you consider valid. 2. \~ akavel 3 minutes ago thank you so much, the links look awesome!! 2. 10 bitshift 41 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. 5 gabeioedited 40 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/>

1. 5 zie 24 hours ago When holding customer/user data on your machines, I'm a huge fan of having a meta DB and then a SQLite db per customer. That way when the customer wants a copy of their data, it's a `.backup` and file transfer away. When they decide they don't want to be a customer anymore, a simple file delete makes it all go away. 2. 3 mhkohne 40 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. 8. 9 thesnarky1 40 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.

1. \~ Halkcyon 23 hours ago I think you're doing a lot of hand-waiving with "unnecessary complexity" because DBM Systems exist for a reason. Now with sqlite, you are responsible for the backups and concurrency and reliability/resiliency and sharding and everything else.

1. 7 mort 19 hours ago You're actually responsible for the backups yourself when using postgres too. I'm not sure what you mean by "reliability/resiliency", sqlite handles interaction with the database file to avoid database corruption and data loss. 2. \~ thesnarky1 15 hours ago For sure, there are some good features in there and some very important ones. But I've seen massively scalable, enterprise-class databases deployed for... a 14,000 row table that changes maybe daily. I can handle those backups, sharding isn't needed, and I could send a copy to everyone's desktop directly in the browser if we needed better reliability.

1. \~ 0x2ba22e11 2 hours ago I once saw some people deploy an Azure Data Lake for some data that was not big data. It was not medium data. It was very, very small data. It fit into L1. 9. 6 laurentbroy 26 hours ago Subjectively, the site feel really snappy this morning. SQLite seems to be handling the load with flying color.

1. \~ thomas0 25 hours ago I noticed this as well after the deploy! 2. \~ orgnizedmess 23 hours ago I tried searching since that used to feel a bit laggy earlier, and it is snappier too! 10. 5 lep 42 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. 3 thomas0 42 hours ago I didn't realize SQLite had a built-in function for `if`, thanks for letting me know! 11. \~ pitr 32 hours ago @pushcx curious, does it mean that sister sites have to migrate as well to keep up with upstream?

1. \~ pushcx 24 hours ago Yes. There are multiple places where we hit the limits of ActiveRecord's expressiveness and fall back to raw SQL. I've avoided committing to supporting multiple databases in the codebase because I see it as significant, risky complexity with an increased maintenance burden (like a CI matrix) without a benefit to this site. Knowing I was giving sister sites a potentially quite big chore was part of the value of publishing the migration checklist. I call this out in the readme to set expectations, and I try to bring it up with everyone who talks to me about reusing the codebase: > You are free to use this code to start your own sister sitebecause the code is available under a permissive license (3-clause BSD). We welcome bug reports and code contributions that help use improve lobste.rs. As a volunteer project we're reluctant to take on work that's not useful to our site, so please understand if we don't want to adopt your custom feature. 12. 4 vaguelytagged 42 hours ago Out of curiosity how does lobste.rs do updates

1. 4 thomas0 42 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. 2 vaguelytagged 42 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. 6 thomas0 42 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. 3 edwardloveall 41 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. 2 tomas 41 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. 3 thomas0 40 hours ago It's just a file, so you could `scp` it. Note that lobsters runs on a single vm. 3. \~ ngrilly 32 hours ago What happens during database migrations? I suppose writes are blocked? How long does it usually take?

1. \~ thomas0 27 hours ago Migrations will take a write lock, which blocks other writers due to SQLite's 1 writer implementation. I would suspect that migrations run pretty fast, but it depends on the migration. 4. 1 vaguelytagged 42 hours ago awesome, thanks! 2. 4 yawaramin 38 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. 13. \~ kbd 23 hours ago What's the architecture of using sqlite in production? Do you use it in-process as a library or as a server process you connect to? How are backups/failover handled? 14. 4 pl 42 hours ago Bravo! Feels lobste.rs is one more step towards staying around for many more years. 15. \~ mxey 7 hours ago > 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. Probably not that important for an English-speaking website, but TIL about this limitation in SQLite. 16. \~ Relax 26 hours ago A week to generate test data? What's the story behind that? I can't imagine that being either CPU or IO limited.

1. \~ thomas0edited 25 hours ago I did have an initial bulk script which bypassed rails models and that completed really quickly (I can't recall how long exactly but it was less than a day). The problem was that bypassing the rails models (which includes hooks) missed some columns and other things I wanted to have in the database. So I went through the rails models and it took around a week to generate half of the data I wanted. I actually cancelled the job after a week since I thought the dataset size was "good enough". Ruby / Rails / Faker (gem used to generate data) is kinda slow if you're generating one thing at a time.

1. \~ Relax 25 hours ago Ah that makes sense, thanks! 2. \~ pushcx 24 hours ago To add to this, we have a fake\_data task for generating... well, best I can claim is "somewhat" realistic data for developers. It beats standing up a dev instance with one tag, one story, and one comment, contributors can immediately see something that looks a little filled in to see how their potential change will affect things. If I had a magic wand the fake\_data task would be produced from some kind of safely sanitized prod data and scalable so if someone needed more than the current couple dozen stories they could generate data to reach some percentage of prod. This would be really useful for performance testing.

1. \~ belthesar 23 hours ago I wonder if this makes sense as a "post-deploy" step when a change would introduce a migration? Farming that out to a local machine that can chew on it to its heart's content, and then making the test DB available upon request would make access to said data set quick and easy, and real. You'd lose the developer agency to define "how real" the dataset needs to be in this case, but I suppose they could also use the same post-deploy process to generate their own data, should they want it.

1. \~ pushcx 22 hours ago Seems like a solid strategy, and I'm happy to run such a thing on my machine. My primary concern is the "safely" in "safely sanitized", so that when we make changes or additions to the database fields there's some mechanism that forces us to consider how they're sanitized. Rather than something like "every time you create a database migration you always have to remember to also edit this santization step over in a separate file". And then, yes, definitely let logged-in users grab a zip and maybe something like mods can generate links for non-user contributors. (We've had a number of people who've contributed PRs to get invitations.) Is this something you'd like to work on? Even just writing up a design for feature in the issue tracker would be a help. 17. 3 yawaramin 37 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`...

1. 10 gecko 36 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. 2. 3 gnyeki 36 hours ago The content seems to contain it even if the title or the URL doesn’t. 18. \~ iamnearlythere 30 hours 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.

1. \~ thomas0 27 hours ago No, but this was one of those things that just worked by default so we didn't change it. I think strict mode tables are a good idea though.

1. \~ skatkov 26 hours ago Rails does quite a lot of sqlite tweaking without you knowing it. I assume, that it's already enabled. You can run following: ``` ActiveRecord::Railtie.config.active_record.sqlite3_adapter_strict_strings_by_default #=> true ```

1. \~ pushcx 24 hours ago I verified, it's `true` in prod. 19. \~ skatkov 25 hours ago Is there a need for this heinous\_inline\_partials hack if rails now supports collection renderer? it's not as fast as inline template, but does it matter as much?

1. \~ pushcxedited 24 hours ago The comments are a tree, not a flat list, so they are interdependent for the nesting. Each comment would have to recursively render its children as collections, and that performs worse than `heinous_inline_partial` that can work over them inline, looping the collection once and making no `render` calls. The use in story lists was a bonus, as long as we had the feature and we have such a stark breakdown in traffic (like 60% is the single-story view and then 30% is story lists) it was worthwhile. 20. \~ 0x2ba22e11 2 hours ago Oh wow congratulations. 21. \~ animesh 22 hours ago The first time I read of this effort, I was skeptic. But now I am so happy that was done. Thanks to all involved. This effort and the whole thread is a case study for SQLite usage in the wild for a successful site. Once I chose sqlite for an ambitious project that eventually failed for non-technology reasons. Reading this makes me feel vindicated with my choice of data storage for that project. You can't tell from my writing this, but I have a ear-to-ear smile now. And the Restic usage is just a bonus. Oh, so much gold in this thread. 22. \~ roflmaoqwerty 16 hours ago Is it safe to assume that lobsters now subscribes to the Sqlite code of ethics? :)

lobste.rs · 46 images · 219 links

Comments (0)

No comments yet