在生产环境中运行 SQLite：性能调优与备份策略

在生产环境中使用 SQLite 运行中小型网站是一个可行的架构选择，但需要了解数据库内部机制，以避免性能下降和可用性问题。关键的运维要求包括通过 ANALYZE 定期更新统计信息、在批量删除期间管理写入锁，以及实施非阻塞备份策略。

使用 ANALYZE 优化查询性能

运行 ANALYZE 命令对于 SQLite 查询规划器做出高效的执行选择至关重要。如果没有最新的统计信息，查询规划器可能会选择次优的计划，导致严重的性能退化。

在一个记录在案的案例中，对一个仅有 4,000 行数据的表进行全文搜索查询（使用 FTS5）耗时 5 秒。运行 ANALYZE 后，执行时间降至约 0.05 秒。

ANALYZE 会生成关于索引和表中值分布的统计信息。这些统计信息存储在内部表中（例如 sqlite_stat1 和 sqlite_stat4）。

对索引的值分布进行各种统计视图，以便规划器可以估计索引的效用（选择性）。sqlite_stat1 仅提供平均值……如果启用，sqlite_stat4 会存储直方图数据。

如果长时间运行的写入操作阻塞了其他工作线程，SQLite 的单写入者模型可能会导致应用程序崩溃或超时。这在涉及大量 DELETE 语句的数据库清理任务中尤为明显。

当批量删除操作超过配置的数据库超时时间（例如 5 秒）时，其他尝试写入数据库的工作线程将会超时，并可能导致应用程序或 VM 崩溃。

为了在清理期间保持可用性，建议采用以下策略：

分批处理： 将删除操作分成小批次进行，而不是使用单个大型事务，以确保没有单个查询超过超时阈值。
预加载 RowIDs： 首先使用 SELECT 语句识别要删除的行，因为在 WAL (Write-Ahead Logging) 模式下，读取操作不会阻塞写入者。
顺序删除： 根据存储介质的不同，按添加顺序（或反向顺序）删除行可以提高性能。
直接使用 CLI 执行： 使用 SQLite Command Line Interface (CLI) 进行清理操作，以绕过应用程序级 ORM 或在事务中运行的 Python 代码可能带来的潜在开销。

可靠的 SQLite 备份需要使用不会长时间锁定数据库且能高效存储在远程对象存储中的方法。

一种方法是使用 VACUUM INTO 命令将数据库的一致性快照创建到临时文件中，然后通过 Restic 等工具将其压缩并上传到兼容 S3 的存储中。

Litestream 通过将 WAL (Write-Ahead Log) 复制到 S3 来提供增量备份。这通常比全量快照更节省资源，并能避免有时与压缩大型数据库文件相关的内存溢出 (OOM) 问题。

使用 .dump 结合 zstd 等压缩工具可以创建高度可压缩、对 rsync 友好的备份，并且在启用 WAL 模式时不会阻塞写入者。

虽然 SQLite 在许多用例中都非常高效，但某些架构模式可以进一步优化其使用：

数据库拆分： 对于具有独立数据领域的项目，将表拆分为多个独立的数据库文件可以减少冲突并简化管理。
WAL 模式： 启用 Write-Ahead Logging (WAL) 是大多数生产环境 Web 应用的前提条件，因为它允许多个读取者和单个写入者并发操作。
迁移至客户端-服务器数据库： 当需求演变为需要基于网络的访问或高并发同时写入时，迁移到 PostgreSQL 等系统是标准的演进路径。