lobste.rs is now running on SQLite

lobste.rs 现已迁移至 SQLite

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.

上周六，我和 @pushcx 将 SQLite 的合并请求（PR）部署到了生产环境。我们一直等到今天早上，观察了周一流量高峰下的表现后才发布这篇博文。毋庸置疑，SQLite 的表现非常出色：CPU 使用率下降了，内存占用减少了，网站响应速度似乎也更快了（至少对我而言是这样），在关停 MariaDB VPS 后，VPS 成本也降低了一半，最重要的是，我们度过了一个“平静的周一”。最终，#539 “迁移至 SQLite” 的议题已于今早关闭。如果您对此次迁移有任何疑问，欢迎随时提出。

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.

背景故事：我参与这次迁移是因为 2019 年我偶然发现了 #539 议题。由于我在处理、管理和迁移大型数据库方面经验丰富，我当时留言建议使用 MySQL 作为替代方案，因为 MariaDB 和 MySQL 之间具有兼容性。那时我并没有打算深入参与，因为当时已经有关于迁移到 PostgreSQL 的讨论了。时间来到 2025 年，Rahul 留言提到了 K1 收购 MariaDB 的消息，随后关于迁移到 PostgreSQL 的细节讨论再次展开。接着在二月份，Rahul 问道：“Lobsters 能运行在 SQLite 上吗？”，并附带了一篇关于 SQLite 的详细文章。我于 2025 年 6 月正式表示愿意承担这个项目。

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.

我想这事儿可能在 Lobsters 的办公时间（office hours）里提到过，但时间太久了，我记不太清了。2025 年 8 月，我提交了第一个 PR，但后来因为太忙没能跟进。GitHub 将其标记为过期并关闭了，由于无法重新打开，我只好提交了第二个 PR。第二次尝试包含了性能测试、数据库迁移脚本（因为现有的 MariaDB/MySQL 转 SQLite 脚本都无法满足我的需求）、调试以及对数据完整性的思考。

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.

第一次部署是在 2 月 21 日。我和 @pushcx 通了电话，制定了部署检查清单。在部署 PR 之前一切都很顺利。部署完成后，网站进入了只读模式，但仅仅是只读流量就让所有 CPU 的占用率飙升到了 100%。我们找不出问题所在，于是决定回滚。第一次部署失败后我感觉很糟糕，因为我知道由于无法访问生产数据库，性能可能会成为一个问题。

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.

部署失败两天后，我提交了第三个也是最后一个 PR。我修复了在失败部署中发现的一些搜索小问题，编写了一个批量数据创建脚本（花了一周时间在本地生成了 Lobsters 一半的数据集大小），并提交了三个修复第一次部署性能问题的更改：1、2、3。性能问题归根结底是 SQLite 对数据库中最大的表进行了全表扫描（涉及两个查询），第三个更改则解决了一个 N+1 问题。在第二次部署的当天早上，我还添加了慢查询日志，以防部署过程中出现更多性能问题。

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.

第二次部署是在 7 月 11 日。我和 @pushcx 在早上通了电话，制定了部署和回滚清单。一切进展顺利，随后 @pushcx 合并并部署了 PR。部署完成后，网站依然在线，CPU 和内存使用率表现良好。这让我松了一口气。我们监控了网站指标和 IRC 频道，查看是否有用户反馈问题，确实有几位用户反馈了问题，我们也迅速进行了修复：1、2。总的来说，网站运行正常，于是我们结束了通话，等待周一的流量高峰。周一到了，网站依然运行良好，所以我们认为这次迁移取得了成功，并继续前进。

SQLite lessons:

SQLite 经验教训：

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.
SQLite gem 支持用户自定义函数（UDFs），我们利用它实现了 SQLite 中缺失的一些函数（如 regexp、if 和 stddev），这样就不必处理太多的 SQL 迁移变通方案。
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.
SQLite 不支持无符号大整数（unsigned bigints）。此前 MariaDB 在某些 ID 上使用了无符号大整数，因此在迁移时我们必须将其转换为大整数（bigints）。
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.
与 MariaDB 相比，SQLite 的排序规则（Collation）较弱。Lobste.rs 在 MariaDB 中使用了 utf8mb4_general_ci，但在 SQLite 中使用了 NOCASE。NOCASE 的缺点是它仅支持 ASCII 字符，不支持完整的 UTF 大小写转换。
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.
在 SQLite 中，全文搜索表应使用推荐的 Contentless-Delete 表，这不是默认设置。SQLite 的默认选项总是让我感到惊讶。

Rails lessons:

Rails 经验教训：

The default PRAGMAs in Rails seem to be working for lobsters.
Rails 中的默认 PRAGMA 设置似乎适用于 Lobsters。
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.
你通常不会想到这一点，但数据库迁移是特定于数据库的。我不得不将旧的迁移文件移到一个专门的目录中，以便 db:migrate 能继续正常工作。

Lobste.rs codebase lessons:

Lobste.rs 代码库经验教训：

There is a search parser in the lobsters codebase. I learned about heinous_inline_partials which is a hack to speed up rendering.
Lobsters 代码库中有一个搜索解析器。我了解到了 heinous_inline_partials，这是一种用于加速渲染的黑客手段。
The lobsters testsuite was essential in making sure I could migrate to SQLite without a ton of manual testing.
Lobsters 的测试套件对于确保我能在无需大量手动测试的情况下迁移到 SQLite 至关重要。

Overall lessons:

总体经验教训：

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.
我认为成功的关键在于所有参与者之间良好的沟通。否则，我认为这是不可能实现的。
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:

愿望：

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.
我希望能在测试中设置：“如果遇到任何全表扫描则失败”。这样就能捕捉到我们在第一次部署时遇到的性能问题。
I wish creating a production like dataset would be much easier than having to manually write something and waiting a week.
我希望创建类似生产环境的数据集能比现在更简单，而不是必须手动编写脚本并等待一周。