System.Xml.XmlElement

Hello!

你好！

I’ve been working on a Django site recently, and I decided to use SQLite as the database.

我最近一直在开发 Django 网站，我决定使用 SQLite 作为数据库。

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.

当我开始使用 SQLite 作为网站的数据库时，我读了很多关于在小型网站的生产中使用 SQLite 是完全没问题的博客文章，我认为它完全没问题，但我没有完全意识到 SQLite 仍然是一个数据库，数据库很复杂，而且我对操作数据库了解不多。

So here are a couple of small things I’ve been learning about running SQLite.

以下是我一直在学习的关于运行 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.

这是我使用 SQLite 的第四个网站，我认为这个网站更难，因为借助 Django ORM 的强大功能，我已经让数据库完成了比以前没有 Django 时更多的工作。

I started by turning on WAL mode like all the blog posts said to do and hoping for the best.

我首先像所有博客文章所说的那样打开 WAL 模式，并希望得到最好的结果。

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.

今天我在一个有 4000 行的表上运行一个查询（使用 SQLite 的 FTS5 进行全文搜索），花了 5 秒。

That seemed wrong to me: computers are fast!

这对我来说似乎是错误的：计算机速度很快！

It turned out that what I needed to do was to run ANALYZE !

事实证明我需要做的是运行 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).

问题查询立即从 5 秒缩短到 0.05 秒（或者其他足够小的数字，我不想进一步调查）。

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?

ANALYZE 生成“统计信息”（我猜每个表中的行数？

and presumably other things?) so that the query planner can make better choices.

大概还有其他事情？）以便查询规划器可以做出更好的选择。

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.

有时，我会遇到这样的情况：我不小心在数据库中放入了一堆我不希望出现的行（例如 django-tasks-db 中已完成的任务），并且我想清理它们。

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.

到目前为止，我的方法是小批量执行这些清理操作，这样我就不需要执行运行时间超过 5 秒的数据库查询。

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.

这整个经历让我更加理解为什么有人可能想要使用像 Postgres 这样的“真正的”数据库，尽管它可以同时拥有多个写入器。

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.

也许将来当我需要做这种事情时，我会关闭该网站进行定期维护，但我还没有找到一个工作流程。

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.

到目前为止，我一直在使用 Django 的 ORM 来进行任何我想要的查询，而根本没有关注查询性能，除了 ANALYZE 之外，它大部分都进​​展顺利。

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.

该数据库非常小（可能有 10000 行？），并且我希望它永远保持很小，所以我希望该计划能够继续发挥作用。

I’ve done SQLite backups a couple of ways.

我用几种方法完成了 SQLite 备份。

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.

我认为我实际上没有测试过从备份恢复，但我通常会尝试使用死人开关来监视它们。

way 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/ prune way 2: litestream

方式 1: Restic sqlite3 /data/calendar.db "VACUUM INTO '/tmp/calendar.sqlite'" gzip /tmp/calendar.sqlite # 将备份上传到 S3 # 有时备份会被 OOM 杀死，因此它保持锁定状态，执行解锁 Restic -r s3://s3.amazonaws.com/some_bucket/unlock # 进行备份并删除旧备份 Restic -r s3://s3.amazonaws.com/some_bucket/ 备份 /tmp/calendar.sqlite.gz Restic -r s3://s3.amazonaws.com/some_bucket/ 快照 Restic -r s3://s3.amazonaws.com/some_bucket/ 忘记 -l 1 -H 6 -d 2 -w 2 -m 2 -y 2 Restic -r s3://s3.amazonaws.com/some_bucket/ 修剪方式 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.

我最近开始尝试 Litestream，因为我觉得增量备份可能会更有效：我的 Restic 备份有时会被 OOM 杀死，我有点厌倦了。

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.

litestream copy -config litestream.yml 我在配置文件中设置了retention: 400h，试图保留一定量的数据库历史记录，但我不知道它是否有效。

I’ve been backing up to AWS, which is always a pain because it’s annoying to navigate the AWS console to generate credentials.

我一直在备份到 AWS，这总是很痛苦，因为导航 AWS 控制台来生成凭证很烦人。

Maybe one day I’ll move away to some other S3-compatible alternative.

也许有一天我会转向其他一些与 S3 兼容的替代方案。

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.

我当前的项目只有一个数据库，但我在 Mess with DNS 中使用的一个技巧是将表拆分为三个单独的数据库文件，因为我实际上不需要我的表位于同一个数据库中。

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 已经在 SQLite 上运行了 4 年（自 2022 年以来），这非常棒，我认为从 Postgres 迁移到该项目是一个不错的选择。

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!

我想我第一次在 Web 项目中使用 SQLite 是在 2022 年，直到今天我才知道 ANALYZE 的存在！

I imagine in a year or two I’ll be learning about some other very basic feature.

我想在一两年内我将学习其他一些非常基本的功能。