在生產環境中執行 SQLite：效能調優與備份策略

對於中小型網站而言，在生產環境中使用 SQLite 是一個可行的架構選擇，但這需要了解資料庫內部機制，以避免效能下降和可用性問題。關鍵的營運需求包括透過 ANALYZE 定期更新統計資訊、在大量刪除期間管理寫入鎖定，以及實施非阻塞式備份策略。

使用 ANALYZE 優化查詢效能

執行 ANALYZE 指令對於 SQLite 查詢規劃器（query planner）做出高效的執行選擇至關重要。如果沒有最新的統計資訊，查詢規劃器可能會選擇次佳的計畫，導致嚴重的效能退化。

在一個記錄在案的案例中，對一個僅有 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 來提供增量備份。這通常比完整快照更節省資源，並能避免有時與壓縮大型資料庫檔案相關的 Out-of-Memory (OOM) 問題。

使用 .dump 結合 zstd 等壓縮工具，可以建立高度可壓縮且對 rsync 友善的備份，且在啟用 WAL 模式時不會阻塞寫入者。

雖然 SQLite 在許多使用場景下都非常高效，但某些架構模式可以進一步優化其使用：

資料庫拆分： 對於具有獨立資料領域的專案，將資料表拆分為多個獨立的資料庫檔案可以減少競爭並簡化管理。
WAL 模式： 啟用 Write-Ahead Logging (WAL) 是大多數生產環境網頁應用程式的前置條件，因為它允許多個讀取者與一個寫入者同時運行。
遷移至用戶端-伺服器型資料庫： 當需求演進到需要網路存取或高併發同時寫入時，遷移至 PostgreSQL 等系統是標準的演進路徑。