優化 SQLite 於生產環境：WAL 模式、併發性與 VFS 層

SQLite 在生產環境：克服僅限本機的迷思

SQLite 是一個高效能的替代方案，可取代 PostgreSQL 或 MySQL 等客戶端-伺服器資料庫，適用於資料量在數百 GB 以內、以讀取為主的應用程式。將 SQLite 直接於同一伺服器的應用程式行程中執行，開發者即可消除網路往返延遲，將讀取轉為記憶體映射檔案操作，執行時間低於毫秒。此方式對單租戶邊緣部署以及使用高速 NVMe SSD 的環境特別有效。

高併發與寫前日誌（WAL）

為了讓讀寫同時進行，必須將 SQLite 從預設的回滾日誌切換為 寫前日誌（WAL）模式。在預設模式下，寫入會阻塞讀取，讀取會阻塞寫入。使用 WAL 模式時，SQLite 會將新交易附加至獨立的 .sqlite-wal 檔案，使讀取者仍能存取主資料庫檔案，而寫入者則寫入 WAL。

實作 WAL 模式

使用以下指令啟用 WAL 模式：

PRAGMA journal_mode = WAL;

管理 Checkpoint（檢查點）流程

由於 WAL 檔案會隨時間增長，SQLite 必須定期透過 checkpoint（檢查點）將 WAL 頁面合併回主資料庫檔案。雖然 SQLite 會自動處理此工作，但高寫入量若讀取者持續活躍，可能導致延遲尖峰或 WAL 無止境成長。為減輕此問題，可在背景執行緒中使用 PASSIVE 或 RESTART 模式明確管理 checkpoint：

PRAGMA wal_checkpoint(PASSIVE);

解決併發與 SQLITE_BUSY 錯誤

SQLite 採用單寫入者模型。若第二個連線在寫入交易仍在進行時嘗試寫入，SQLite 會回傳 SQLITE_BUSY 錯誤。可透過設定與架構調整來管理此情況。

Busy Timeout（忙碌逾時）與鎖升級

設定 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