[CODE]

那個什麼都沒做的搜尋框：用 SQLite FTS5 處理五萬筆聚合資料

入庫流水線跑了六個月、累積了五萬筆資料，檢索那一側悄悄壞掉了。為什麼在這個規模上 FTS5 打贏了 Algolia 和 Elasticsearch，以及 production 實作真正長什麼樣子。

2 min read AI 生成
sqlite full-text-search nodejs database

一個看起來在運作的搜尋框，比根本不存在的搜尋框更糟糕。搜尋框的存在本身就是一種承諾。

Dev Signal 是我幫自己建的 RSS 聚合器，用來管理同時跑多個技術案時的資訊流。客戶案子和個人專案並行，靠順手瀏覽根本追不上。系統從 17 個來源拉內容——工程部落格、AI 公司文章、技術電子報、podcast 逐字稿——進一個 SQLite 資料庫，後端是 Next.js 14，部署在 Railway 上。前提很直白：在客戶會議前快速拉背景、找上個月某篇文章的具體論點、在需要的時候把三週前讀過的東西翻出來。搜尋不能用，那五萬筆資料等於不存在。

入庫那一側跑了六個月，從來沒出問題。檢索那一側在我沒注意的時候悄悄壞掉了。

有積累，沒檢索

最初的搜尋是 LIKE '%keyword%' 全表掃描，按插入順序回傳。五千筆時能用，五萬筆時三個問題疊在一起：沒有相關性排序（結果按入庫順序出現）、沒有詞形變化匹配（搜 “indexing” 找不到 “indexes”）、冷讀取慢到頁面看起來像在等逾時。有個一起用的人看著搜尋框問：「這東西到底在做什麼？」我答不出來。

Algolia 是顯而易見的選項。打開文件：API key 管理、計費頁面、需要自己維護的資料同步 job。Elasticsearch 一樣，規模更大。兩個分頁都關掉了。Dev Signal 五個人在用，資料庫配上 64MB WAL cache 就能塞進記憶體。這些服務在有規模的產品上是對的選擇，但在這個問題上，它們只是把維護成本搬進外部基礎設施，不是消掉它。我需要的是去讀那段我已經跳過了三年的 SQLite 文件。

SQLite 內建 FTS5。這件事我知道。我從來沒動過它。

消掉基礎設施問題的那個選擇

FTS5 是 SQLite 內建的虛擬資料表模組。不需要額外安裝，不需要外部程序，查詢不走網路，沒有憑證要輪換。一段 SQL 建立一張有 BM25 相關性排序的索引影子表：

CREATE VIRTUAL TABLE IF NOT EXISTS articles_fts
USING fts5(
  title,
  content,
  title_zh,
  content_zh,
  content='articles',
  content_rowid='id'
);

content='articles' 讓這張表成為 content table。FTS5 只存倒排索引結構，原始文字留在 articles。語料庫成長，儲存量不膨脹。初始化是一次 INSERT INTO articles_fts SELECT ... FROM articles——五萬筆，不到一秒索引完畢。後續靠 trigger 同步。

三個 trigger，不是 pipeline

Content table 不會自動跟著底層資料表更新。三個 trigger 處理：

CREATE TRIGGER articles_ai AFTER INSERT ON articles BEGIN
  INSERT INTO articles_fts(rowid, title, content, title_zh, content_zh)
  VALUES (new.id, new.title, new.content, new.title_zh, new.content_zh);
END;

CREATE TRIGGER articles_ad AFTER DELETE ON articles BEGIN
  DELETE FROM articles_fts WHERE rowid = old.id;
END;

CREATE TRIGGER articles_au AFTER UPDATE ON articles BEGIN
  UPDATE articles_fts
  SET title = new.title,
      content = new.content,
      title_zh = new.title_zh,
      content_zh = new.content_zh
  WHERE rowid = old.id;
END;

SQL 囉嗦，但只寫一次。真正的對比不是「trigger 對上正規搜尋服務」，而是「trigger 對上憑證輪換行事曆上永遠排不完的工作」——加上速率限制監控、加上最壞時刻失準的資料同步 pipeline。三段 SQL，提交之後不用再碰。

BM25 分數是負數——這個坑你一定踩

FTS5 的 bm25() 函式為每筆結果算相關性分數。文件裡埋著一件事：分數是負數。-5.2 比 -1.1 更相關。ORDER BY bm25(articles_fts) 不加 DESC 才是對的，把最負的排在最前面。我看著順序反過來的結果困惑了二十分鐘，才停下來去讀文件。

const search = db.prepare(`
  SELECT
    a.id,
    a.title,
    a.published_at,
    a.source,
    bm25(articles_fts) AS relevance_score
  FROM articles_fts
  JOIN articles a ON a.id = articles_fts.rowid
  WHERE articles_fts MATCH ?
  ORDER BY relevance_score
  LIMIT 20
`);

Production 的 searchArticles 實作實際上按 published_at DESC 排序，不是 BM25 分數。對新聞聚合器來說這是刻意的產品決策：搜 “observability tooling”，我要的是上週的討論，不是六個月前 BM25 分數最高的那篇。時效本身就是相關性的一個維度。BM25 排序在特定情境下仍然有用，比如需要找某個主題在語料庫中最集中的討論，而不是最新的。

FTS5 沒有模糊匹配。“indexing” 和 “indexes” 是不同 token，除非用前綴運算子 index*。對術語一致的英文技術語料庫可以接受，一般文章很快就碰壁。

沒有 WAL，並行查詢就排隊

Next.js API route 並行打資料庫。一個頁面載入同時觸發多個查詢。SQLite 預設的 journal mode 把一切序列化——一個寫入鎖住整個資料庫，所有讀取排在後面等。沒有 WAL，並行請求的排隊在頁面上看得出來。

PRAGMA journal_mode=WAL;
PRAGMA synchronous=NORMAL;
PRAGMA cache_size=-64000;  -- 64MB page cache

連線初始化時設一次。WAL 在頁面層級把讀寫分離，不再互相阻塞。synchronous=NORMAL 是 WAL 的標準取捨：可以抵擋 OS crash，抵擋不了寫入途中斷電。對部署在 Railway 上的單節點聚合器，可以接受。任何跑在 Next.js 上、有稍微正常一點負載的應用，不設這個 pragma 就是在等著看神秘的延遲毛刺。

中文搜尋的天花板

Dev Signal 的 title_zh 和 content_zh 欄位存的是 DeepL 翻譯後的中文內容。流水線在入庫時呼叫 DeepL API，把英文翻成繁體中文，寫進雙語欄位。FTS5 預設的 tokenizer 用空白切詞，對拉丁字母正確，對中日韓文直接失效。中文詞與詞之間沒有空格，整個句子被當成一個無法搜尋的巨型 token。

對五個人在用的個人工具，這是有記錄的限制，不是阻塞問題。搜尋詞恰好完整出現在翻譯後的文字裡才能找到結果；部分詞形匹配做不到。面向真實用戶、需要認真對待中文搜尋的產品，需要支援 CJK 的 tokenizer，或是換掉搜尋引擎。知道自己在用的是哪種 workaround，是讓這個取捨可以接受的前提。

在這個規模選 Elasticsearch，不是技術決策——是宣告你願意永遠維護一個外部依賴。FTS5 早就住在我本來就在跑的資料庫裡。這件事從來不是在選哪個搜尋引擎。是去不去讀文件的問題。