jvns：實際跑 SQLite 學到的幾件事

這是 jvns 在 7 月 17 日貼出的一篇筆記，〈Learning about running SQLite in production〉，沒有統一的敘事，比較像是把這幾個月跟 SQLite 打交道時卡住又解開的地方，一條一條記下來的現場紀錄。整篇沒有一個總結式的收尾，每一段反而留著沒解決的疑問：DELETE 為什麼這麼慢、Litestream 的備份到底能不能還原，她都老實承認自己還沒把握。下面把裡面真正卡人的幾道坎——ANALYZE、單一寫入者的鎖、備份，跟兩件順手做對的小事——攤開來看，每一道都對應一個可以直接搬進自己專案的判斷，而不是一句「SQLite 很快」的空話。

這類筆記重要的地方，不在於每一條都是全新發現——ANALYZE、single writer、WAL，都是 SQLite 文件裡寫得到的機制。重要的是她把這些機制跟自己踩到的場景對起來寫：什麼時候會撞到、撞到之後症狀長什麼樣、她試過哪個修法、修法有沒有真的解決問題。這種機制加場景的寫法，比單純背一份 SQLite 調校清單更容易在你自己的專案裡用得上——你不需要先讀懂 SQLite 的原始碼，只需要認得出查詢忽然變慢、worker 開始逾時這些症狀，然後想起這裡曾經有人踩過同一個坑。

查詢突然快了一百倍：ANALYZE 做了什麼

她某天在一張只有 4000 列的資料表上，跑一個用 SQLite FTS5 全文搜尋的查詢，結果花了 5 秒。表不大，查詢也沒有特別複雜，5 秒是那種讓人停下來懷疑哪裡出錯的數字。她做的第一件事不是重寫查詢，是跑一次 ANALYZE——同一個查詢立刻從 5 秒掉到大約 0.05 秒，快了大約一百倍。下面這張圖把這兩個數字直接畫出來，用 log scale 畫的原因很單純：兩個數字差了兩到三個數量級，線性座標會讓其中一根柱子細到看不見。

ANALYZE 做的事情，是替資料表產生統計量，讓 query planner 有依據去挑一個更好的執行計畫。SQLite 的 planner 平常會用預設假設去猜每個 join、每個 index 掃描大概要碰多少列；沒有統計量的時候，這個猜測可能整個偏掉，尤其是 FTS5 這種全文搜尋索引背後的結構，跟一般 B-tree index 的分佈假設不一樣。她自己也說不出精確原因，只給了一個猜測：查詢計畫可能不小心掉進某種 accidentally quadratic 的形狀——資料量沒有變大，但計畫的複雜度卻跟著列數的平方成長，統計量一補上，planner 就繞開了那條路。這個猜測她自己也承認沒有把握，但這正是這篇筆記可信的地方：它沒有把一個沒查證的原因包裝成結論。

這裡的統計量不是什麼神秘的東西，本質上就是每個 index、每個欄位的資料分佈概況——大略多少個 distinct value、每個值平均對應多少列。一般關聯式資料庫的 cost-based query planner，都是靠這種統計量去估算某個 join 順序大概要掃多少列，才能在好幾種可能的執行計畫裡挑一個成本最低的。SQLite 預設不會自動維護這份統計量，除非手動跑 ANALYZE；對一般的 B-tree table，即使沒有統計量，planner 通常還有 index 本身的結構可以參考，誤判機率比較低。FTS5 背後是倒排索引，形狀跟一般 B-tree 差很多，沒有統計量的時候，planner 更容易對某個詞出現在多少列裡做出離譜的估計——這也呼應了她自己的猜測：問題不是資料量大，是計畫選錯了。

她會先跑 ANALYZE 而不是先重寫查詢，順序本身就是一個判斷：重寫查詢的代價是要先看懂查詢在做什麼、要冒著改壞邏輯的風險；跑一次 ANALYZE 幾乎沒有下行風險，最壞情況只是沒有效果。用最低成本的動作先排除最常見的原因，再往更花力氣的方向查，這個順序在任何效能問題上都適用，不是 SQLite 專屬的技巧。

這件事可以直接變成一個檢查習慣：任何一支跑起來比預期慢的查詢，先問一句「這張表跑過 ANALYZE 嗎」，再去想要不要重寫 SQL。ANALYZE 不是一次性設定，資料分佈變了、列數量級變了，都值得重跑一次，尤其是批次匯入大量資料之後——統計量跟著資料一起變舊，跟資料庫版本無關。實務上怎麼知道這張表的統計量舊了：最直接的訊號就是症狀本身，一支邏輯沒變、資料量也沒有暴增的查詢，忽然變慢了一個數量級以上，那就是先去跑 ANALYZE、而不是先去改 SQL 的時機。反過來說，如果查詢本來就因為資料量增加而線性變慢，通常不是統計量的問題，是真的要重新設計 index 或拆查詢。分辨這兩種情況，靠的不是理論，是先養成症狀不對勁就先跑 ANALYZE 這個反射動作，把它當成除錯清單裡最便宜、最先做的一步。

一個寫入者、一把鎖：DELETE 拖太久會發生什麼事

SQLite 一次只允許一個寫入者，這件事平常感覺不到，直到某個背景清理工作出問題。她的系統裡有一支清理用的 DELETE，因為要刪的列數不少，跑起來超過 5 秒——她自己也還留著疑問，猜測可能是 transaction 裡包了一段 Python 邏輯，但沒有把握。真正造成麻煩的不是 DELETE 本身，是它跑的時候，另一個 worker 嘗試寫入同一個資料庫：那個 worker 會在她設定的 5 秒 timeout 之後直接失敗。下面這張圖把這段時間軸畫出來——上面那條 lane 是正在跑的 DELETE，下面那條是等著寫入卻被鎖住的 worker，兩者在 5 秒那條線交會。

她的解法不是把 timeout 調大，是把清理工作拆成更多次、更小的批次，讓每一次資料庫操作都留在 5 秒預算之內。下面這個滑桿把這個判斷變成可以自己拉一次的東西：拖動的是「這一批 DELETE 要跑多久」，SQLite 的鎖不管你設多大 timeout，只要對面那個 worker 等待的時間跨過 5 秒，它就會逾時失敗。

拖動滑桿改變這批 DELETE 花費的秒數 · timeout 門檻 5 秒

這批 DELETE 跑了 7 秒

5 秒 timeout 是原文設定的真實門檻；DELETE 花費的秒數在這裡是你可以自己拖動的假設值，用來示範規則本身怎麼運作，不是重現她那次事故的精確時間。

這個滑桿示範的其實是一個更通用的規則：任何跑在單一寫入鎖之上的批次工作，安全的批次大小不是靠猜，是靠這批工作的最長執行時間必須小於下游能容忍的等待時間反推回去。timeout 設多少、批次要切多細，兩個數字綁在一起，改一個就要重新檢查另一個。

這個判斷背後的原則很直接：單一寫入者模型下，任何一段寫入操作占用鎖的時間，都直接等於其他寫入者被卡住的時間，批次大小其實是在拿「多繞一層迴圈」換「鎖住時間縮短」。她也藉這次經驗留了一句評語：這種經驗讓她更能體會，為什麼有人會想用 Postgres 這種可以同時有多個寫入者的「真正」資料庫。這句話沒有變成「所以我要換 Postgres」，比較像是標出一條分界線——當寫入模式已經沒辦法再靠批次壓進 timeout 以內，或者同時要跑的 worker 數量已經多到批次策略追不上，才是該認真考慮多寫入者資料庫的時候，跟 SQLite 本身快不快沒有直接關係。

單一寫入者不是 SQLite 的疏忽，是它作為嵌入式資料庫的設計前提——沒有獨立的 server process，鎖的協調只能發生在同一台機器的檔案系統層級，用一把粗粒度的寫入鎖換取實作簡單、沒有額外的協調成本。Postgres 這類 client-server 資料庫是另一套權衡：靠 MVCC 讓多個 transaction 各自看到自己版本的資料快照，寫入者之間不需要互相等待，代價是要維護版本鏈、要有獨立的 vacuum 機制清理舊版本，實作複雜度高出一截。這兩種設計沒有絕對的優劣，只是假設不同：SQLite 假設寫入不會頻繁互相衝突，Postgres 假設會。她的清理工作，剛好踩到了 SQLite 那個假設的邊界。

這也是一個挑資料庫時可以先問自己的問題：系統裡會不會同時有多個獨立的行程各自想寫入同一份資料？如果答案是幾乎不會、頂多一個 app process 加一個背景 worker，SQLite 的單一寫入者模型通常撐得住；如果答案是會、而且行程數量會隨流量成長，那從一開始就該往 Postgres 這類多寫入者的方向設計，而不是等踩到 timeout 才回頭改。

順手做對的兩件事：WAL 跟拆 db 檔

筆記裡還有兩件比較小、卻一樣值得抄的判斷，跟 SQLite 本身的鎖機制有關。第一件是 WAL 模式——她一開始上生產環境，就照著幾乎所有部落格文章的建議把它打開，用她自己的話說是抱著希望。這句話本身是個誠實的提醒：WAL 是幾乎沒有理由不開的預設選項，但打開之後就沒事，本身也是一個還沒被驗證過的假設。WAL 帶來的差別，是 SQLite 本身的機制，不是這篇筆記的獨創發現：預設的 rollback journal 模式下，writer 在寫的時候，reader 得等它寫完；WAL 模式下，writer 把變更先寫進一個獨立的 WAL 檔，reader 讀的是寫入前的快照，不需要等待。下面這張圖把兩種模式的差別畫出來。

WAL 模式底下還有一個常被忽略的機制：WAL 檔本身會持續成長，需要靠 checkpoint 把 WAL 裡的變更寫回主資料庫檔案，WAL 檔才能被截斷回收。如果 checkpoint 因為某個長時間開著的 read transaction 而一直無法完成，WAL 檔會無限成長，這是 WAL 模式反過來咬人的地方。她的筆記沒有提到自己踩過這個雷，但這是打開 WAL、抱著希望這句話背後，一個值得先知道的風險。

第二件是拆資料庫檔案。在 Mess with DNS 這個專案裡，她把不需要放進同一個 transaction 的 table，拆成三個獨立的 db 檔。道理很直接：單一寫入者的鎖是綁在一個 db 檔案上，不需要一起交易的 table 擠在同一個檔案裡，只是在不必要的地方多製造鎖競爭。下面這張圖對照的是同一批 table 放在一個檔案，跟拆成三個檔案之後，寫入鎖的範圍差在哪裡。

這個做法也有代價：拆成多個 db 檔之後，原本可以用一個 transaction 同時包住的操作，現在要嘛接受這幾個檔案不是原子性一起更新的，要嘛自己在應用層補一層協調。她會做這個交換，前提是這幾個 table 本來就不需要放在同一個 transaction 裡——這是判斷的關鍵：先確認資料模型裡真的沒有這幾張表必須同時成功或同時失敗的需求，才值得為了降低鎖競爭去拆檔案，不然等於是把一致性問題留到日後才發現。

備份：兩條路，各自留著一個沒驗證完的問題

備份她走了兩條路，各自留著一個還沒解決的問題。第一條是 Restic：用 VACUUM INTO 把資料庫另存成一份乾淨的快照，壓縮後傳到 S3。這個做法簡單，每次都是一份完整、可以獨立驗證的檔案，但代價是備份程序偶爾會被系統 OOM kill，被殺掉之後資料庫檔案還留著鎖，得手動 unlock 才能繼續。第二條是 Litestream：做連續增量複製，不用每次搬一整份資料庫。她把 retention 設成 400 小時，但自己也坦白寫：不確定這樣設定到底有沒有效。下面這張表把兩條路擺在一起看。

兩條備份路線各自留著一個沒驗證完的問題。

工具	機制	設定	已知問題
Restic	VACUUM INTO 產生完整快照，gzip 壓縮後傳到 S3	每次都是一份完整拷貝	備份程序有時被系統 OOM kill，資料庫檔案留鎖，需手動 unlock
Litestream	連續增量複製，不需整份搬移	retention: 400h	作者自己也不確定這個設定到底有沒有效，還沒驗證過真的能還原

把兩條路放進同一張表，差別不是哪個比較好，是兩種完全不同的失敗模式：Restic 的風險出現在單次備份執行的當下，OOM 一發生就看得到；Litestream 的風險平常看不出來，只有真的要復原的那一刻才會現形，而她自己都還沒把這一步驗證完。如果你的專案兩條路都能承受，一起上反而比選邊站划算：快照顧的是這個時間點有一份完整拷貝，增量複製顧的是盡量少丟最近的資料，兩者要解決的問題本來就不一樣，不是互相替代的關係。

換一個角度看，這兩條路其實對應備份設計裡常講的兩個指標：可以接受丟多少資料，跟真正要復原時要花多久、有多少把握能成功。Restic 的完整快照對「復原速度」友善——復原時只要抓最新一份、解壓縮、掛回去，流程簡單、失敗模式也單純；代價是兩次快照之間的資料，一旦事故發生就是全部丟失，丟失範圍等於快照間隔。Litestream 的連續複製反過來，理論上能丟的資料量壓得很低，但真正復原時有多可靠，取決於一個她自己都還沒驗證過的環節：那份增量資料真的能不能重組回一個可用的資料庫。兩條路各自把風險留在不同的地方，這也是為什麼她沒有選邊站，而是兩條一起上。

把四道坎放在一起看，會發現一個共同的形狀：每一個都是先靠感覺抓到症狀，再用最小的改動去驗證，而不是從理論出發去重寫整套架構。ANALYZE 是一行指令，批次化是一個迴圈，拆 db 檔是一次搬移，兩條備份路線都是現成工具接上去——沒有一個是重寫系統。這也是為什麼這篇筆記讀起來像現場筆記，不是教科書：它示範的不是 SQLite 的正確用法，是遇到症狀時先從哪裡開始查的那套反射動作。

這篇筆記也老實留了幾個空白：DELETE 慢的真正原因沒有解決、Litestream 的還原到底可不可靠沒有驗證、WAL 打開之後有沒有真的監控過 checkpoint 也沒提。留白不是缺點，是誠實——比起把每個問題都包裝成已解決的最佳實踐，讓讀者知道哪裡還沒驗證完，才不會把別人還在觀察中的判斷，誤當成可以閉著眼睛照抄的定論。

可以直接抄的判斷：查詢變慢先問有沒有跑過 ANALYZE；任何長時間的寫入操作都用批次的方式把它壓進你的 timeout 預算內；備份至少留一條能真正驗證還原得回來的路——jvns 自己都還沒把 Litestream 這條路驗證完，值得你比她更早想清楚。