FTS5 — Postgres 없이 Full-text search · SQLite Quest

진짜 텍스트 검색이 기본으로 들어 있어

row가 몇천 개를 넘어가면 LIKE '%foo%'는 잘못 고른 도구야. 모든 row를 훑거든. SQLite에는 FTS5가 들어 있어. 토큰으로 쪼갠 내용을 인덱싱해두고, MATCH query를 빠르게 돌리고, 관련도로 순위까지 매겨주는 full-text search extension이야.

FTS5가 주는데 LIKE는 못 주는 게 셋이야.

수백만 row에서도 1초 안에 끝나는 검색.
토큰 쪼개기. 단어 경계를 알아보고, 원하면 unicode나 porter stemmer를 붙이고, 부분 문자열 검색에는 trigram tokenizer를 쓸 수 있어.
rank를 통한 BM25 관련도 순위.

Self-reference: 피파의 세션 검색이 JSONL ground truth 위에서 FTS5로 돌아. WebUI에서 '예전 message 찾기'를 누르면 messages 테이블을 그대로 비추는 FTS5 virtual 테이블에 MATCH query가 나가.

Code

기존 테이블에 FTS5 미러 붙이기·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;

관련도 순위까지 매기는 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*';

Exercise

텍스트가 담긴 row 5만 개 이상짜리 테이블을 준비해. message든 post든 note든 상관없어. trigger까지 붙여서 FTS5 미러를 만들고 MATCH query를 세 개 던져. 단어 하나, 구절, 앞부분 매치. 같은 뜻의 LIKE '%word%'와 실제 시간을 견줘봐. 차이를 적고, 어떤 워크로드라야 이 schema 복잡도를 감수할 만한지 정리해.

FTS5 — Postgres 없이 Full-text search

진짜 텍스트 검색이 기본으로 들어 있어

Code

External links

Exercise

Progress

댓글 0