alt Hacker NewsLobste.rs is now running on SQLite
Comments
It's been fairly unstable recently, pages sometimes render for several seconds which I've never seen under MariaDB. Used to be instantaneous, always.
Sometimes (maybe 5% or less) the request won't render at all, and you get a browser error page.
Today they ran into this bug, lost a bunch of voting data, and went into read-only mode for several hours:
https://github.com/rails/rails/pull/57128
I wonder how much of this is usual bugs which crop up during major database migrations, and how much is caused by choice of SQLite.
Some of the cited reasons for moving off MariaDB [1] seem misguided, in my opinion. Especially the part about "K1 are very enterprise-focused, so the database is likely to focus its work on features that are not relevant to us. There's increased risk they drop the free/open source version we use"
K1 acquired the commercial entity behind MariaDB Enterprise, but that's separate from the non-profit MariaDB Foundation. And there's literally zero risk of the MariaDB server suddenly going closed-source; as a fork of MySQL (which is GPL), this is not even legally possible!
[1] https://github.com/lobsters/lobsters/issues/539#issuecomment...
Some observations:
1. Stats are used by "Cost-Based Optimizers."
I think this is the most appropriate wiki:
https://en.wikipedia.org/wiki/Query_optimization
On my Oracle databases, I run the dbms_stats.gather_database_stats package procedure once a week. SQL Server also has stats for sure, and I would be surprised by a relational database that did not.
SQLite will quietly gather stats with each database connection. Running an explicit analyze devotes full attention to an otherwise piecemeal group effort.
2. I don't think rows are physically removed when deleted, instead they are marked. Removal will definitely happen at vacuum.
3. There is a backup command in the CLI that will make a transaction consistent copy of the database file, and there are lower-level C functions behind it that can be used with object code.
4. It is important to remember the many restrictions of WAL mode, including the impact on ATTACHing multiple database files: ACID consistency is lost.
https://sqlite.org/lang_attach.html
'Transactions involving multiple attached databases are atomic, assuming that the main database is not ":memory:" and the journal_mode is not WAL.'
I would visit lobste.rs more if it filtered out stories that are on Hacker News.
I'm not interested in a rehash of Hacker News but I am interested in more tech news.
Anyhow on topic: I find it puzzling the whole "use sqlite in production!" thing.
- you're stuck on a single machine now.
- you don't have remote database access.
- you have to live with the inflexibility of sqlite's schema change capability.
- all writes go through a single writer.
- fewer data types
- fewer backup/security/high availability options
- foreign keys and strict disabled by default.
- you're single core in a multi core world.
It's not a terrible decision but when you can choose Postgres, why would you go with sqlite?
Ironically, this post resurfaced on HN with my comment here shown as if made 15 minutes ago, which I in reality did last week?
Does HN still use simple files for storing its data?
The site is performing poorly for me. For example, the login page took 9 seconds to load, same with the home page.
Wow, lobsters seems refreshing! Invite only to contribute must be why they can run on sqlite so effectively. I've run into so many issues with WAL (real time market data) unless I'm painfully deliberate with reads and writes. Don't feel like jumping on the "ohhh invite me" bandwagon, but definitely subscribing to their feed.
Moving from MariaDB to SQLite and not Postgres for a web application of this scale is insane. I wonder what went into the discussion behind this.
Although the site is very fast for me as of this writing, it looks like they're running into an issue:
https://lobste.rs/s/ko1ji1/lobste_rs_is_now_running_on_sqlit...
I hope they get it sorted soon, I'm rooting for sqlite.
Software “engineering”. Thank goodness bridges aren’t built with this level of rigor.
In any case I will take an invite if one has one because the community is solid.
No intention to antagonize but... why not postgre? Even my side projects run on postgresql with no overhead at all
Another SQLite user here. My site gets constantly hammered by crawlers, and SQLite just keeps going on hit miss. Anyway, the real magic is on the ram and the ssd perf.
sqlite3_rsync exists:
... and is installed and available:
# sqlite3_rsync -vv ./test.db [email protected]:test.db
Pretty cool. I do default to SQLite these days simply because I can just put it on the host, set up a PVC to it, and run the app in Kube pointing at a persistent SQLite. Absent some other need (it's a fork of psql- or mysql-based software) I prefer using it for simplicity.
I do have to say that I've found lobste.rs generally a pretty slow site to render and navigate and I wonder whether that's the Rails backend, or underpowered hardware, or lack of caching. It's more feature-ful than a HN or Reddit though with much lower usage so I wonder what it is.
May I have an invite, if anyone has one to spare?
Everytime Lobste.rs get posted, I get to think should I dive in. This time maybe. Could somebody be kind enough to give me an invite. Email in my profile. Thanks.
I hope it's profitable, because this feels like engineering for engineering sake and not actually improving the product in a way that draws customers.
I'd appreciate a lobste.rs invite if anyone has one. Email is in my profile. Thanks!
sqlite for a site that debates database migrations is like a chef who eats microwave dinners at home. makes perfect sense and somehow still feels like a betrayal.
Do you feel like lobste.rs is a better version of HackerNews?
Nice to see Lobsters is made with ruby
DHH has made a big deal about using SQLite in rails
I think HN is better. Lobste.rs has too much monocultural pressure.
That's not necessarily a bad thing, but honestly, as a foreigner, I have limited English vocabulary, so I end up getting stressed out over being judged as AI. At least here, the admin DEN kindly understood my situation and let me participate after getting permission. But when I look at subreddits and other communities, the stronger the monocultural management, the more fan-like sentiment tends to grow, making it harder for minority voices to speak up.
I'm in favor of LLMs (since being for or against LLMs inevitably depends on regional context—I personally find LLM translation incredibly convenient and helpful), but there are moments when I find it hard to argue against blanket opposition or certain topics. I thought about why that is, and I think it's because Lobste.rs has high-quality posts, but since it's inviet only, the cultural barrier to entry is high, and judgment itself is part of the community identity. It's more about signals like 'are you the same kind of person as us' than logical counterarguments. The community is small, so the same core users appear across many topics, and the reputation built there follows you around, making it closed off to minority opinions.
HN, on the other hand, isn't really a single community but a mix of many groups—startup founders, big-tech engineers, and independent developers like me. Depending on who happens to be reading at that time, the comment sentiment often shifts. And since there are so many readers, even if I say something on one thread, it's unlikely anyone will recognize me in the next.
So Lobste.rs feels like a hard place for an outsider to fit into.
Oh wow. Off-topic but quite refreshing to see a site not absolutely plastered in AI posts
[flagged]
[dead]
[dead]
[flagged]
[flagged]
They use WAL in SQLite. If I continuously perform reads/writes so that they overlap with no gaps, I can make their VM go down because SQLite will not have time to initiate a checkpoint to trim the WAL file. SQLite waits for a time window without any active reads/writes before starting a WAL checkpoint. If there isn't one, the WAL will grow indefinitely, eating up all the disk space on the VM.
It's in SQLite's documentation, and almost no one switching to Sqlite seems to be aware of it because no one discusses it in blog posts like these. I guess most projects switching to SQLite have very low traffic and no malicious users (yet)