FTS5 — Postgres 없이 Full-text search

~16 min · fts5, search, performance

Level 0Scout

0 XP0/80 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

진짜 텍스트 검색, built-in

몇천 row 넘는 substring 검색에 LIKE '%foo%' 가 잘못된 도구 — 모든 row scan. SQLite 가 FTS5 ship — tokenized content 인덱싱 + relevance 랭킹 있는 빠른 MATCH query 의 full-text search extension.

FTS5 가 LIKE 못 주는 거 3 가지:

수백만 row 의 sub-second 검색.
토크나이징 (단어 경계, 옵션 unicode/porter stemmer, substring 용 trigram tokenizer).
rank 통한 BM25 relevance 랭킹.

Self-reference: 피파의 세션 검색이 JSONL ground truth 위 FTS5 사용 — WebUI 의 'past message 찾기' 가 messages 테이블 미러링하는 FTS5 virtual 테이블에 MATCH query.

Code

기존 테이블에 FTS5 미러 setup·sql

-- messages 와 link 된 FTS5 virtual 테이블 생성
CREATE VIRTUAL TABLE messages_fts USING fts5(
  content,
  content='messages', content_rowid='id',
  tokenize='unicode61'
);

-- 인덱스 backfill
INSERT INTO messages_fts(rowid, content) SELECT id, content FROM messages;

-- Trigger 로 sync 유지
CREATE TRIGGER messages_ai AFTER INSERT ON messages BEGIN
  INSERT INTO messages_fts(rowid, content) VALUES (new.id, new.content);
END;
CREATE TRIGGER messages_ad AFTER DELETE ON messages BEGIN
  INSERT INTO messages_fts(messages_fts, rowid, content) VALUES('delete', old.id, old.content);
END;
CREATE TRIGGER messages_au AFTER UPDATE ON messages BEGIN
  INSERT INTO messages_fts(messages_fts, rowid, content) VALUES('delete', old.id, old.content);
  INSERT INTO messages_fts(rowid, content) VALUES (new.id, new.content);
END;

Relevance 랭킹 있는 MATCH query·sql

-- 'sqlite renaissance' 검색
SELECT m.id, m.created_at, m.content, bm25(messages_fts) AS score
FROM   messages_fts
INNER  JOIN messages m ON m.id = messages_fts.rowid
WHERE  messages_fts MATCH 'sqlite renaissance'
ORDER  BY score
LIMIT  20;

-- Phrase 검색
SELECT * FROM messages_fts WHERE messages_fts MATCH '"foreign keys"';

-- Prefix
SELECT * FROM messages_fts WHERE messages_fts MATCH 'sql*';

External links

Exercise

50k+ row text content 테이블 (message, post, note). Trigger 와 함께 FTS5 미러 빌드. MATCH query 3 개: 단일 단어, phrase, prefix. 동등한 LIKE '%word%' 와 wall-clock 비교. Gap 기록 + 어떤 워크로드가 schema 복잡도 가치 있는지.

Progress

Progress is local-only — sign in to sync across devices.

← Previous거짓말 안 하는 벤치마크 Next →퀴즈 · 3 questions

이 페이지에서 버그를 발견하셨거나 피드백이 있으세요?문제 신고

🔔 답글 알림 (로그인 필요)

로그인 — 댓글을 남기려면 로그인해 주세요.

아직 댓글이 없어요. 첫 댓글을 남겨보세요.