优化 SQLite 以用于生产环境：WAL 模式、并发性和 VFS 层

生产环境中的 SQLite：克服仅本地使用的误区

SQLite 是一种高性能的替代方案，可用于读密集型且数据量在几百 GB 以内的应用，取代 PostgreSQL 或 MySQL 等客户端-服务器数据库。通过在同一服务器的应用进程中直接运行 SQLite，开发者消除了网络往返延迟，使读取操作变为内存映射文件操作，执行时间在亚毫秒级。这在单租户边缘部署以及使用高速 NVMe SSD 的环境中尤为有效。

使用写前日志（WAL）实现高并发

要实现读写并发，必须将 SQLite 从默认的回滚日志切换为 写前日志（WAL）模式。在默认模式下，写操作会阻塞读取，读取会阻塞写入。使用 WAL 模式时，SQLite 将新事务追加到单独的 .sqlite-wal 文件中，读取器可以访问主数据库文件，而写入器则向 WAL 追加数据。

实现 WAL 模式

使用以下命令启用 WAL 模式：

PRAGMA journal_mode = WAL;

管理检查点过程

由于 WAL 文件会随时间增长，SQLite 必须定期通过 检查点（checkpoint） 将 WAL 页面合并回主数据库文件。虽然 SQLite 会自动处理，但高写入量可能导致延迟峰值或在读取器始终活跃时导致 WAL 无限增长。为缓解此问题，可在后台线程中使用 PASSIVE 或 RESTART 模式显式管理检查点：

PRAGMA wal_checkpoint(PASSIVE);

解决并发及 SQLITE_BUSY 错误

SQLite 强制单写模型。如果第二个连接在写事务活跃时尝试写入，SQLite 会返回 SQLITE_BUSY 错误。可以通过配置和架构调整来管理此问题。

忙等待超时与锁升级

设置 busy_timeout 可指示 SQLite 在失败前使用指数退避算法重试获取写锁：

PRAGMA busy_timeout = 5000; -- 5 seconds

为防止死锁，对任何涉及写入的操作使用 IMMEDIATE 事务。这会立即获取保留锁，阻止其他连接启动写事务，同时仍允许读取：

BEGIN IMMEDIATE;
-- Write operations
COMMIT;

应用层写入管理

某些生产环境发现 busy_timeout 对高争用场景不足。在这种情况下，在应用层实现单写队列可以有效消除 SQLITE_BUSY 错误，确保一次只有一个写事务在进行。

内存和 I/O 优化

SQLite 默认的内存设置通常对生产环境来说过小。扩展缓存并使用内存映射 I/O 可以显著降低磁盘 I/O。

缓存调优与内存映射

增大 cache_size 以将工作集保留在内存中。负值表示以 KiB 为单位的大小：

PRAGMA cache_size = -64000; -- ~64MB

启用内存映射 I/O（mmap），让操作系统内核管理页面缓存，绕过用户空间的缓冲区拷贝：

PRAGMA mmap_size = 2147483648; -- Map up to 2GB

持久性与虚拟文件系统（VFS）

SQLite 使用虚拟文件系统（VFS）抽象层，这意味着它并不直接写入操作系统文件系统。这样可以自定义 VFS 层，实现云原生的持久性和复制。

云复制工具

  • Litestream: 将增量 WAL 帧流式传输到对象存储（例如 AWS S3），用于时间点恢复。
  • LiteFS: 基于 FUSE 的 VFS，将事务实时复制到读取副本，以支持分布式部署。

synchronous = NORMAL 的权衡

使用 PRAGMA synchronous = NORMAL; 通过仅在关键时刻同步到磁盘来降低同步开销。虽然这提升了性能，但也带来了持久性风险：系统崩溃时，最近提交的事务可能会丢失，尽管数据库完整性仍然保持。

生产配置蓝图

对于生产就绪的配置，在打开连接后立即执行以下 pragma：

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA busy_timeout = 5000;
PRAGMA cache_size = -64000;
PRAGMA mmap_size = 1073741824;
PRAGMA foreign_keys = ON;
PRAGMA journal_size_limit = 67108864;
PRAGMA auto_vacuum = INCREMENTAL;

关键考虑因素与局限性

虽然 SQLite 功能强大，但相较于 PostgreSQL 等客户端-服务器数据库，它有一些特定的局限性：

  • Schema Migrations（模式迁移）: SQLite 对 ALTER COLUMN 的支持有限。更改列定义通常需要手动更新底层模式或重新创建表。
  • Operational Tooling（运维工具）: 与运行中的生产数据库交互通常需要直接访问 VPS 的文件，这比通过 DBeaver 等 GUI 工具连接远程服务器更为复杂。
  • Durability Requirements（持久性要求）: 对于要求零数据丢失和零停机时间的客户，使用 LiteFS 或 Litestream 等工具增加的复杂性可能抵消 SQLite 相较于传统托管数据库的简易优势。

Sources