SQLite in Production: Optimizing WAL Mode, Concurrency, and VFS Layers

SQLite 在生产环境中的应用：优化 WAL 模式、并发处理与 VFS 层

Transitioning SQLite from a local development tool to a production-grade database requires a deep understanding of its internal mechanics. This article explores how to tune WAL mode, manage busy handlers, and leverage custom Virtual File System (VFS) layers to achieve ultra-low latency. 将 SQLite 从本地开发工具转型为生产级数据库，需要对其内部机制有深刻的理解。本文将探讨如何调整 WAL 模式、管理忙碌处理程序（busy handlers）以及利用自定义虚拟文件系统（VFS）层来实现超低延迟。

Demystifying the “Local-Only” Myth of SQLite

揭开 SQLite “仅限本地”的迷思

Historically, SQLite has been relegated to the role of an embedded database for mobile clients, IoT devices, and local development environments. Conventional wisdom dictated that for any serious production-grade web application, a client-server database like PostgreSQL or MySQL was mandatory. However, this assumption overlooks a massive shift in modern hardware architecture. With the ubiquity of high-speed NVMe SSDs, ultra-fast local storage, and the trend toward single-tenant edge deployments, the network roundtrip latency of traditional databases has become the primary bottleneck. 从历史上看，SQLite 一直被定位为移动客户端、物联网设备和本地开发环境的嵌入式数据库。传统观念认为，对于任何严肃的生产级 Web 应用，必须使用 PostgreSQL 或 MySQL 等客户端-服务器架构的数据库。然而，这种假设忽略了现代硬件架构的巨大转变。随着高速 NVMe SSD 的普及、超快本地存储的出现，以及单租户边缘部署趋势的兴起，传统数据库的网络往返延迟已成为主要的性能瓶颈。

By running SQLite directly within the application process on the same server, you eliminate the network overhead entirely. Reads become simple memory-mapped file operations, resulting in sub-millisecond query execution. Yet, running SQLite in production requires a shift in how we configure, tune, and think about database concurrency. Out-of-the-box, SQLite is configured for maximum safety and compatibility, not high-throughput application servers. To unlock its true potential, we must dive deep into its internal mechanisms: Write-Ahead Logging (WAL), locking states, cache management, and custom Virtual File System (VFS) layers. 通过在同一服务器上的应用程序进程内直接运行 SQLite，可以完全消除网络开销。读取操作变成了简单的内存映射文件操作，从而实现亚毫秒级的查询执行。然而，在生产环境中运行 SQLite 需要改变我们配置、调整和思考数据库并发的方式。开箱即用的 SQLite 配置是为了实现最大的安全性和兼容性，而非针对高吞吐量的应用服务器。为了释放其真正的潜力，我们必须深入研究其内部机制：预写式日志（WAL）、锁定状态、缓存管理以及自定义虚拟文件系统（VFS）层。

Deep-Diving into Write-Ahead Logging (WAL) Mode

深入探讨预写式日志（WAL）模式

By default, SQLite uses a rollback journal mechanism. In this mode, before any write operation occurs, the original database page is copied to a separate rollback journal file. If the transaction succeeds, the journal is deleted; if it fails, the database uses the journal to restore the database to its original state. The critical downside of rollback journals is concurrency: writes block reads, and reads block writes. Only one connection can access the database at a time during write operations. To build a highly concurrent application server, you must enable Write-Ahead Logging (WAL) mode. 默认情况下，SQLite 使用回滚日志机制。在这种模式下，在任何写操作发生之前，原始数据库页面会被复制到一个单独的回滚日志文件中。如果事务成功，日志会被删除；如果失败，数据库会利用该日志将数据库恢复到原始状态。回滚日志的关键缺点在于并发性：写操作会阻塞读操作，读操作也会阻塞写操作。在写操作期间，同一时间只能有一个连接访问数据库。要构建高并发的应用服务器，必须启用预写式日志（WAL）模式。

PRAGMA journal_mode = WAL;

In WAL mode, instead of modifying the main database file directly, SQLite appends new transactions to a separate .sqlite-wal file. This shifts the concurrency paradigm completely: Concurrent Reads and Writes: Readers continue to read from the main database file (and unchanged pages in the WAL) while writers append new pages to the end of the WAL file. Readers and writers do not block each other. 在 WAL 模式下，SQLite 不会直接修改主数据库文件，而是将新事务追加到一个单独的 .sqlite-wal 文件中。这彻底改变了并发范式：读写并发。读取者继续从主数据库文件（以及 WAL 中未更改的页面）读取数据，而写入者将新页面追加到 WAL 文件的末尾。读取者和写入者互不阻塞。

Checkpointing Strategies

检查点（Checkpointing）策略

The Checkpointing Process: Over time, the WAL file grows. To prevent it from consuming excessive disk space and slowing down read operations (which must scan the WAL index to find the latest version of a page), SQLite must periodically merge the WAL pages back into the main database file. This is called checkpointing. 检查点进程：随着时间的推移，WAL 文件会不断增长。为了防止其占用过多的磁盘空间并拖慢读取操作（读取操作必须扫描 WAL 索引以查找页面的最新版本），SQLite 必须定期将 WAL 页面合并回主数据库文件。这个过程称为检查点。

SQLite handles checkpointing automatically, but the default behavior can cause latency spikes. There are four checkpointing modes: SQLite 会自动处理检查点，但默认行为可能会导致延迟峰值。共有四种检查点模式：

PASSIVE: Merges as many pages as possible without blocking any readers or writers. If a reader is currently accessing an older page in the WAL, SQLite cannot overwrite that page, so the checkpoint stops early. PASSIVE：在不阻塞任何读取者或写入者的情况下，尽可能多地合并页面。如果读取者当前正在访问 WAL 中的旧页面，SQLite 无法覆盖该页面，因此检查点会提前停止。
FULL: Blocks new write transactions and waits for existing read transactions to complete, ensuring the entire WAL is merged. FULL：阻塞新的写事务并等待现有的读事务完成，确保整个 WAL 被合并。
RESTART: Similar to FULL, but it also resets the WAL file size to zero, ensuring subsequent writes start at the beginning of the file. RESTART：类似于 FULL，但它还会将 WAL 文件大小重置为零，确保后续写入从文件开头开始。
TRUNCATE: Same as RESTART, but it truncates the WAL file on disk to zero bytes. TRUNCATE：与 RESTART 相同，但它会将磁盘上的 WAL 文件截断为零字节。

For production servers with high write volume, relying solely on SQLite’s automatic checkpointing can cause the WAL file to grow indefinitely if there is always an active reader. To prevent this, you should manage checkpointing explicitly in a background thread or process using a PASSIVE or RESTART checkpoint at scheduled intervals: 对于高写入量的生产服务器，如果始终存在活跃的读取者，仅依赖 SQLite 的自动检查点可能会导致 WAL 文件无限增长。为防止这种情况，你应该在后台线程或进程中，通过定期执行 PASSIVE 或 RESTART 检查点来显式管理检查点：

PRAGMA wal_checkpoint(PASSIVE);

To ensure write operations don’t suffer from disk synchronization bottlenecks, pair WAL mode with the following pragma: 为了确保写操作不会受到磁盘同步瓶颈的影响，请将 WAL 模式与以下 pragma 配合使用：

PRAGMA synchronous = NORMAL;

In NORMAL mode, the database engine syncs to disk only at critical moments (e.g., during checkpoints) rather than at every single transaction commit. In WAL mode, this is completely safe from database corruption; even if the server crashes, only the uncommitted transactions in the WAL are lost, but the database integrity remains intact. 在 NORMAL 模式下，数据库引擎仅在关键时刻（例如检查点期间）同步到磁盘，而不是在每次事务提交时都同步。在 WAL 模式下，这对于防止数据库损坏是完全安全的；即使服务器崩溃，也只会丢失 WAL 中未提交的事务，但数据库的完整性保持不变。

Concurrency Architecture: Tackling SQLITE_BUSY

并发架构：应对 SQLITE_BUSY

Although WAL mode allows concurrent reads and writes, SQLite still enforces a single-writer model. Only one transaction can write to the database at any given instant. If a second connection attempts to write while a write transaction is active, SQLite immediately returns an SQLITE_BUSY error. To build a resilient application, your connection pool and transaction logic must be architected to handle this constraint gracefully. 尽管 WAL 模式允许读写并发，但 SQLite 仍然强制执行单写入者模型。在任何给定瞬间，只有一个事务可以写入数据库。如果第二个连接在写事务处于活动状态时尝试写入，SQLite 会立即返回 SQLITE_BUSY 错误。为了构建一个具有弹性的应用程序，你的连接池和事务逻辑必须经过精心设计，以优雅地处理这一约束。

Configure a Busy Timeout: Never run SQLite in production without setting a busy timeout. This instructs SQLite to retry acquiring the write lock internally for a specified duration before raising an SQLITE_BUSY exception.
配置忙碌超时（Busy Timeout）：在生产环境中运行 SQLite 时，务必设置忙碌超时。这会指示 SQLite 在引发 SQLITE_BUSY 异常之前，在内部重试获取写锁一段指定的时间。

PRAGMA busy_timeout = 5000; -- Timeout in milliseconds (5 seconds)

During this window, SQLite will use an exponential backoff algorithm to sleep and retry, which dramatically reduces application-level errors under peak load. 在此期间，SQLite 将使用指数退避算法进行休眠和重试，这大大减少了峰值负载下的应用层错误。

Lock Escalation and Immediate Transactions: SQLite has three transaction modes:
锁升级与立即事务（Immediate Transactions）：SQLite 有三种事务模式：

DEFERRED (Default): The transaction starts without acquiring any locks. It begins as a read transaction and escalates to a write transaction only when a write operation is executed. This can easily lead to deadlocks if two connections start a deferred transaction, read data, and then both try to write. DEFERRED（默认）：事务启动时不获取任何锁。它以读事务开始，仅在执行写操作时才升级为写事务。如果两个连接同时启动延迟事务、读取数据，然后都尝试写入，这很容易导致死锁。
IMMEDIATE: The transaction… IMMEDIATE：事务……