Learning a few things about running SQLite
Learning a few things about running SQLite
关于运行 SQLite 的一些心得
Hello! I’ve been working on a Django site recently, and I decided to use SQLite as the database. When I was getting started with using SQLite as database for a website I read a bunch of blog posts about how it is totally fine to use SQLite in production for a small site and I think it is totally fine, but what I did not fully appreciate is that SQLite is still a database, databases are complicated, and I do not know a lot about operating databases. So here are a couple of small things I’ve been learning about running SQLite. This is the 4th website I’ve used SQLite for, and I think this one is harder because with the power of the Django ORM I’ve been making the database do more work than I was previously without Django.
你好！最近我一直在开发一个 Django 网站，并决定使用 SQLite 作为数据库。在开始将 SQLite 用于网站数据库时，我读了很多博客文章，它们都说对于小型网站来说，在生产环境中使用 SQLite 完全没问题。我也认为确实如此，但我当时没有充分意识到的是：SQLite 终究是一个数据库，而数据库是很复杂的，我对数据库的运维了解并不多。因此，以下是我在运行 SQLite 过程中学到的一些小经验。这是我第四次在网站中使用 SQLite，我觉得这次更具挑战性，因为借助 Django ORM 的强大功能，我让数据库承担了比以往不使用 Django 时更多的工作。
ANALYZE is apparently important
ANALYZE 命令显然很重要
Today I was running a query (using SQLite’s FTS5 for full-text search) on a table with 4000 rows and it took 5 seconds. That seemed wrong to me: computers are fast! It turned out that what I needed to do was to run ANALYZE! Immediately the problem query went from taking 5 seconds to like 0.05 seconds (or some other number small enough that I didn’t care to investigate further). I still don’t know exactly what went wrong in the query plan, but my best guess is that it was some sort of accidentally quadratic thing. ANALYZE generates “statistics” (I guess about the number of rows in each table? and presumably other things?) so that the query planner can make better choices. Maybe one day I’ll learn to read a query plan.
今天，我在一个拥有 4000 行数据的表上运行查询（使用 SQLite 的 FTS5 进行全文搜索）时，竟然耗时 5 秒。这看起来不对劲：计算机明明很快的！结果发现我需要做的是运行 ANALYZE！那个有问题的查询瞬间从 5 秒缩短到了约 0.05 秒（或者其他小到我懒得去深究的数字）。我仍然不完全清楚查询计划中到底出了什么问题，但我猜测可能是某种意外的二次方复杂度问题。ANALYZE 会生成“统计信息”（我猜是关于每个表中的行数？以及其他一些信息？），以便查询规划器能做出更好的选择。也许有一天我会学会如何阅读查询计划。
cleaning up the database is tricky
清理数据库很棘手
Occasionally I’ve run into situations where I accidentally put a bunch of rows in my database that I don’t want to be there (for example completed tasks from django-tasks-db), and I want to clean them up. What’s happened to me a few times in this case is: I run some kind of command to clean up the rows. The command takes more than 5 seconds, since there are a lot of rows (though I still have some questions about why these DELETE statements are so slow honestly, maybe there’s a bunch of Python code running inside a transaction, I’m not sure). One of the other workers tries to write the database while this is happening, and times out after 5 seconds (I have a timeout of 5 seconds set). The worker crashes because it couldn’t write to the database and the VM shuts down.
偶尔我会遇到这种情况：我不小心在数据库中存入了一堆不需要的行（例如来自 django-tasks-db 的已完成任务），并希望清理它们。在这种情况下，我几次遇到这样的问题：我运行某种清理行的命令，由于行数很多，命令执行时间超过了 5 秒（老实说，我仍然对为什么这些 DELETE 语句如此缓慢存有疑问，也许是因为事务中运行了大量的 Python 代码，我不确定）。当清理正在进行时，其他工作进程尝试写入数据库，结果在 5 秒后超时（我设置了 5 秒的超时时间）。工作进程因为无法写入数据库而崩溃，导致虚拟机也随之关闭。
My approach so far has been to just do these cleanup operations in small batches so that I don’t need to do database queries that take more than 5 seconds to run. This whole experience has given me more of an appreciation for why someone might want to use a “real” database like Postgres which can have more than one writer at the same time though. Maybe in the future I’ll just take the site down for scheduled maintenance instead when I need to do this kind of thing, but I haven’t figured out a workflow for that yet.
到目前为止，我的处理方法是分小批次进行这些清理操作，这样我就不需要执行耗时超过 5 秒的数据库查询了。整个经历让我更加理解为什么有些人会想要使用像 Postgres 这样可以同时支持多个写入者的“真正”数据库。也许将来在需要进行此类操作时，我会选择让网站停机进行计划维护，但我还没想好具体的工作流程。
no notes on performance of ORM queries yet
暂无关于 ORM 查询性能的笔记
So far I’ve been using Django’s ORM to make any query I want without paying any attention at all to query performance and it’s mostly been going okay other than the ANALYZE thing. The database is pretty small (maybe 10000 rows?) and I expect it to stay pretty small forever, so I’m hoping that that plan will keep working.
到目前为止，我一直使用 Django 的 ORM 来执行任何我想要的查询，完全没有关注查询性能，除了 ANALYZE 那次问题外，一切都还算顺利。数据库非常小（可能 10000 行左右？），我预计它会一直保持这么小，所以我希望这个方案能持续有效。
backing up sqlite
备份 SQLite
I’ve done SQLite backups a couple of ways. I don’t think I’ve actually tested restoring from my backups but I do usually try to monitor them with a dead man’s switch.
我尝试过几种备份 SQLite 的方法。虽然我还没真正测试过从备份中恢复，但我通常会尝试使用“死人开关”（dead man’s switch）来监控它们。
way 1: restic 方法 1：restic
sqlite3 /data/calendar.db "VACUUM INTO '/tmp/calendar.sqlite'"
gzip /tmp/calendar.sqlite
# Upload backup to S3
# Sometimes the backup gets OOM killed and so it stays locked, do an unlock
restic -r s3://s3.amazonaws.com/some_bucket/ unlock
# Do the backup & prune old backups
restic -r s3://s3.amazonaws.com/some_bucket/ backup /tmp/calendar.sqlite.gz
restic -r s3://s3.amazonaws.com/some_bucket/ snapshots
restic -r s3://s3.amazonaws.com/some_bucket/ forget -l 1 -H 6 -d 2 -w 2 -m 2 -y 2
restic -r s3://s3.amazonaws.com/some_bucket/ pruneway 2: litestream 方法 2：litestream
I started trying out Litestream recently because I felt like doing incremental backups might be more efficient: my restic backups were sometimes getting OOM killed, and I was a bit tired of it. Basically I just write a config file and run: litestream replicate -config litestream.yml. I set retention: 400h in my config file in an attempt to retain some amount of history of the database but I have no idea if it works. I’ve been backing up to AWS, which is always a pain because it’s annoying to navigate the AWS console to generate credentials. Maybe one day I’ll move away to some other S3-compatible alternative.
最近我开始尝试使用 Litestream，因为我觉得增量备份可能更高效：我的 restic 备份有时会因为内存溢出（OOM）被杀掉，我对此感到有些厌倦。基本上，我只需要写一个配置文件并运行：litestream replicate -config litestream.yml。我在配置文件中设置了 retention: 400h，试图保留一定量的数据库历史记录，但我不知道这是否有效。我一直备份到 AWS，这总是很痛苦，因为在 AWS 控制台中生成凭证非常麻烦。也许有一天我会迁移到其他兼容 S3 的替代方案。
you can use multiple databases
你可以使用多个数据库
My current project only has one database, but one trick I used with Mess with DNS was to split the tables into three separate database files because I didn’t actually need my tables to be in the same db. I think it was helpful. Mess with DNS has been running on SQLite for 4 years now (since 2022) and it’s been great, I think the move from Postgres was a great choice for that project.
我目前的这个项目只有一个数据库，但我之前在“Mess with DNS”项目中用过一个技巧，就是将表拆分到三个独立的数据库文件中，因为我实际上并不需要这些表都在同一个数据库里。我觉得这很有帮助。“Mess with DNS”已经在 SQLite 上运行了 4 年（自 2022 年起），效果非常好。我认为对于那个项目来说，从 Postgres 迁移出来是一个绝佳的选择。
that’s all!
就这些了！
It’s always kind of fun to see how long it takes me to learn sort of basic things about the technologies I’m using. I think I used SQLite for a web project for the first time in 2022 and I only learned that ANALYZE existed today! I imagine in a year or two I’ll be learning about some other very basic feature.
看到自己需要花多长时间才能学会所用技术的一些基础知识，总是挺有趣的。我想我是在 2022 年第一次在 Web 项目中使用 SQLite，而直到今天我才知道 ANALYZE 的存在！我想一两年后，我应该会学到其他非常基础的功能。
some references
一些参考资料
Some blog posts I’ve looked at, other than the official docs: 除了官方文档外，我还参考了一些博客文章：