← #802

关于运维 SQLite，我学到的几件事

查看原文 HN 讨论

文章摘要

Julia Evans 分享了在一个 Django 网站上运行 SQLite 的经验。她的基调很诚实：SQLite 对小站点来说完全够用，但它「毕竟是一个数据库」，带着一整套她最初没充分意识到的运维复杂度。文章的价值不在于给出权威结论，而在于记录一个聪明的普通使用者遇到问题、动手排查、并坦诚说明哪些地方自己还没弄明白的完整过程。

最有传播力的发现是 ANALYZE 命令。她遇到一个全文检索查询，在一张只有 4000 行的表上竟然要跑 5 秒；跑了一次 ANALYZE 之后，执行时间降到 0.05 秒。ANALYZE 会生成统计信息，让查询计划器能做出更好的优化决策。她猜测最初的问题涉及某种意外的平方级复杂度行为，但坦言没有深究。

第二个发现是并发限制带来的真实故障。数据库清理操作出了问题：当 DELETE 语句超过 5 秒超时后，并发尝试写入的 worker 会崩溃，有时甚至导致虚拟机关闭。她采用的应对方式是「把这些清理操作拆成小批次做」，避免长时间持锁。她也承认这个限制正好说明了为什么有人会更愿意用 Postgres 这类支持多个并发写入者的「真正的」数据库。

运维实践方面，她描述了两套备份方案：一是用 Restic，先用 VACUUM INTO 做压实，再压缩上传到 S3；二是最近改用 Litestream 做增量备份，以减少备份进程被 OOM killer 杀掉的问题。她也提到备份监控用了「死人开关」（dead man’s switch）的思路。配置上，她一开始就按各种博客的推荐启用了 WAL 模式（预写日志），但承认没有深入研究其中细节。

其他零散观察包括：在她这个约一万行的小库上，Django ORM 查询还没到需要做性能监控的程度；当表之间不需要事务时，多个独立的 SQLite 文件可以替代单库架构；她的另一个项目「Mess with DNS」从 Postgres 迁到 SQLite 之后已经稳定运行了四年。

HN 评论精华

• simonw：针对文中「备份到 AWS 一直很痛苦，因为在 AWS 控制台里生成凭证很烦」这句话，他说自己几年前也被烦透了，于是专门写了个工具解决这一个问题——uvx s3-credentials create my-existing-s3-bucket 会吐出仅限该 bucket 的读写凭证，还可以加 --read-only、--write-only 进一步收紧，甚至用 --prefix foo/bar 限定只能读写该前缀下的 key。至于文中说「也许某天会换到别的 S3 兼容服务」，他推荐 Restic 配 Cloudflare R2，用起来很好。被问到什么时候会需要「只写」凭证时，他答：用于日志场景，特别是在你不希望凭证泄露后导致已记录文件被删除的环境里。

• striking：想再拖延一下「学会读查询计划」这件事的话，SQLite 的 .expert 模式可以帮你——把 SELECT 喂给它，它会直接推荐该建什么索引，并展示用上索引后的计划。他还对文中「分批清理」那段给出了一个很有意思的反转：那些「『真正的』数据库」的建议其实同样是分批做清理操作，它们只是让你在数据量小的时候更不容易察觉自己在做低效的事。「你比自己以为的更正确。」DANmode 追问这是不是在把它当优点讲，striking 回答说这只是事实如此：有时你希望能写出显而易见的查询而数据库别来挡你，有时你又希望尽早知道自己做的事在指数级负载下撑不住——他现在这个职业阶段更偏好后者，但前者在他心里永远有一席之地。

• simonw 和 cogman10 从另一个角度佐证了这一点：simonw 说他用过采用行级复制的大型 MySQL 库，影响上百万行的 UPDATE 或 DELETE 必须分批执行，否则一条 SQL 就会导致上百万行的更新同时被推给所有副本。cogman10 补充说，任何做过大量数据库工作的人都明白大批量更新必须分批，否则性能会被打爆——数据量到大约 100 万行时批处理就成了必需品。Groxx 说得更绝：他因为 percona-toolkit 的自动机制太激进而自己写了批处理器，「每个数据库最终都需要这个，连 Cassandra 这种 NoSQL 宠儿也一样」。bananamogul 补充了 Oracle 上的另一个副作用：删除一千万行会写出一千万行的 undo，可能把预留给归档日志的磁盘空间挤爆；对于需要定期清理的大库，他的经验是分区最好用——删掉最旧的分区几乎是瞬时且无痛的。

• noxer 给出了针对 DELETE 问题的一组具体解法：分批删；批次之间加延迟；删除前先用 SELECT 预加载 rowid（SELECT 不阻塞）。另外，如果数据是按主键顺序追加的，文件中的物理布局大概也是这个顺序，按此顺序或逆序删除可能更快（取决于存储介质等因素）。zbentley 说 rowid 预加载是一项极其有效的技巧，而且远不止对 SQLite 有效——他在超大规模的 Aurora MySQL / Postgres 集群上也用过，因为可以把 SELECT 发到副本上，而删除的全部意义就在于行过滤造成的索引内存压力正在给主库的 CPU 和缓冲缓存带来巨大负担。

• masklinn 回答了文中「以及大概还有其他东西？」的疑问：ANALYZE 生成的是索引取值分布上的各种统计视图，让计划器能估算索引的选择性有多好。sqlite_stat1 只给出平均值（索引中的记录数、每个取值的平均记录数），而启用后 sqlite_stat4 会存储直方图数据。

• kevincox 解释了「死人开关」的含义：即在某件事没有发生时触发告警（名字来源于需要活着的操作员一直握住的那种开关）。所以这里是指，如果在配置的时间窗口内没有成功备份，监控就会告警——这与「备份任务失败时告警」相对，后者在任务从未运行、永久挂起、或以不触发监控的方式崩溃时就失效了。他随后提出了一个更根本的质疑：「不过我看不出这一切怎么解决了『没有测试备份』的问题——你完全可能备份任务定期成功，但被备份的东西依然不可用。」sroussey 立刻贡献了一个惨痛案例：他们曾雇了个 DBA 重做备份脚本，此人对「用真实恢复来测试备份」这个想法非常反感，显然从没在真实数据库上做过（只在自己的小样本上试）。结果发现那些「每日」备份要跑 30 小时，然后在「备份不会跑这么久」的假设下互相覆盖。「当然，我们是以艰难的方式发现的。」spikk 提出了对应的改进：备份任务应该定期恢复到一个临时库并跑 PRAGMA integrity_check。

• Kalanos：SQLite 很棒，但它只适合本地系统，一旦你需要通过网络连接、或要稳健处理并发请求，你就需要 Postgres 之类的东西。gabeio 反驳说这个说法已经不再准确了，很大程度取决于你怎么组织表和文件——如果对数据库分片，多台机器可以各自作为自己分片的写入者；也可以把读写请求分开，让只读机器随意伸缩。inigyou 则站在另一边：「到这个程度你其实是在用 SQLite 当后备存储自己造一个网络数据库，真该重新考虑这是否比用一个本来就为此设计的东西更容易。」他还引用了 SQLite 作者的立场：SQLite 的定位是与 fopen 竞争，而不是与 Postgres 竞争。dukeyukey 补充说官方也讲过公道话：「一般来说，任何每天不到 10 万次访问的站点用 SQLite 都应该没问题。」allknowingfrog 提了个更根本的质疑：这真是 SQLite 作者自己的主张吗？「我不认为除此之外还有谁能决定一个东西是为什么而生的。」

• tomjen3：用 WAL（这本该是默认值，或者至少该解释得更清楚），你就能有一个写入者、多个读取者。而且除非必须，不要迁到网络上——每一个请求都会因为把本地读取换成网络连接而大幅变慢。Kalanos 说他试过 WAL，但正如对方所说，多个写入时它会冻住（是创建记录的时候，不是更新），他觉得自己需要的其实是一个写入队列。tomjen3 回应说 SQLite 在你使用事务时就自带这个：一个事务关闭时会解锁其他事务；如果指定事务类型为 IMMEDIATE，它会尝试获取排他写锁并在默认 30 秒后超时。他反问对方的写入模式是什么、事务开着多久——即便一次只能写一个，事务理想情况下应该只开很短时间，那么以每秒写入次数衡量的吞吐量仍然会很高；如果中间夹着某个网络调用，SQLite 就不适合，确实该换数据库。

• second_route：「database is locked 也坑过我，把 busy_timeout 设成一个非零值解决了其中大部分问题。」他还提到自己因为 .dump 有一次把他锁在外面而改用了 .backup。

• andrewaylett 分享了自己的备份脚本思路：用 sqlite3 -readonly 加 .dump 管道到 zstd（带 --rsyncable），先写 .part 再 mv 成最终文件。他说这在写入者使用 WAL 时不会阻塞写入，得到的 dump 压缩率好且便于同步——他的 Home Assistant 库有 1.8GB，dump 压缩后是 286MB，而且他估计其中 90% 每天都是不变的。对他来说「便于同步的输出」才是真正重要的部分，因为这意味着他可以让 borg 指向这个 zstd 文件、只需存储变化的部分。formerly_proven 补充说 VACUUM INTO、.backup（走 backup API）、sqlite3_rsync 和 litestream 也都不阻塞写入。

• holgerschurig 是本帖最严厉的批评者：他摘出文中「我没兴趣进一步调查」「我最好的猜测是」「以及大概还有其他东西？」「也许有一堆 Python 代码在事务里跑」这几句，认为「这篇文章基本没有实质内容，作者根本没花力气去学、去查证，然后就在乱猜，有时还猜错了」。他类比说，作为 Debian 用户，搜 Linux 相关问题时如果跳出 Ubuntu 论坛他连都不点开了，但会打开 Arch Wiki——尽管 Arch 和 Debian 差得远，因为那上面是懂行的人写的。

• the_gastropod 的回应精准：「不要把 Julia 的谦逊和平易近人的写作风格误认为『不懂行』。她是一位知识极其扎实、干这行很久的程序员，一直在努力让技术话题对新人不那么可怕。」simonw 展开得更完整：Julia Evans 是个极其博学的人，也是最擅长把技术去神秘化、帮别人理解「解决问题实际是什么样子」的人之一。这篇文章从没假装是世界级专家对 SQLite 的看法——线索就在标题里，「学到关于运维 SQLite 的几件事」，从一开头就设定好了预期。她全部作品贯穿的更大讯息是：这些事你也能做，这里有一些简单的做法示范如何摸清问题、积累知识；你不必什么都懂，而且你当然不必装作什么都懂。把你目前搞明白的东西尽可能清楚地分享出来，本身就是一种美德。maccard 从从业者角度补充：这篇文章的好处正在于它呈现了一个「聪明使用者」的真实体验——他昨天工作中用了两种编程语言、两套构建系统、一个云服务商、一个密钥管理器、一套复杂的客户端-服务端通信框架，加上版本控制、编辑器和 CI 工具，「如果我对暴露给我的每一根线头都深挖下去，我永远做不完任何事」。rollulus 给了一个时代性的评语：「我意识到在 LLM 时代，我更加珍惜 Julia 的写作了——真实的探索，是对那些过度自信、无所不知的生成垃圾文章的解药。」

• stevoski（自称数据库出身）：他读这篇文章很难受，因为他只想找出问题然后去解决。「一张只有 1 万行的表？连全表扫描都应该极快。而且是 SQLite——我以为它是进程内运行的，即便不是，肯定也在同一台物理服务器上，那就更快了。当然，我脑子里的那句魔法咒语是 create index。」他还高度怀疑「删除慢」的问题是许多 ORM 用户都会遇到的经典 N+1 问题，直到他们更了解底层的数据库交互为止。arlattimore 同样猜测：不知道 ORM 的删除之所以慢，是不是因为在列表上循环对每个对象调 delete，而不是用接受 ID 列表的批量删除方法。

• rtpg 提了一个很好的职业建议：在数据库上比当前舒适区、比当前工作要求钻得更深一点，始终是一条很好的升级路径。他见过很多 Web 开发者对数据库工具有心理障碍（他坦承自己对 K8s 之类的运维话题也有类似障碍），而且不问那么多问题也能在职业上走得挺远。但真的去搞清楚你的 SQL 是怎么变成从磁盘读写数据的，对于「凭直觉知道什么做法可能不错」非常有帮助，理解你数据库的锁机制（或它的缺失）也是——把这些弄明白，下次你在 Postgres 里发现一个「简单的 COUNT」怎么都跑不快时，惊讶程度就会低很多。

• luciana1u 给了 SQLite 一句很漂亮的评价：「SQLite 最棒的地方在于，它是少数几个『读它的文档会让你成为更好的工程师，而不是只让你更困惑』的软件之一。」

• deterministic 补充了一条生产环境注意事项：SQLite 在处理非常大的 BLOB（100MB 以上）时会变得很慢。他最后不得不把 BLOB 存到外部、在 SQLite 库里只放引用——当然这不理想（BLOB 不再受事务保护），但配合哈希和校验来检测与处理无效 BLOB，实践中工作良好。

• src73 提供了一个额外的 ANALYZE 成功案例：他在自己自建的 MediaWiki 上跑了一下，搜索从「秒级」变成了「毫秒级」。