SQLite FTS5 전문 검색: 가상 테이블과 MATCH 사용법

LIKE로는 한계가 있다

SQLite에서 텍스트를 검색해 본 적이 있다면 십중팔구 LIKE '%word%'부터 떠올렸을 겁니다. 작은 테이블에서는 잘 동작하지만, 데이터가 커지면 금세 무너지죠. 도움이 될 만한 인덱스가 없기 때문에 SQLite는 모든 행을 훑으면서 소문자로 바꾸고 부분 문자열을 일일이 확인해야 합니다. 게다가 단어 경계, 랭킹, 다중 키워드 검색, 접두사 매칭 같은 건 전부 직접 구현해야 합니다.

이때 정답이 바로 SQLite에 내장된 FTS5(sqlite full text search) 입니다. FTS5는 텍스트 컬럼에 역색인(inverted index)을 자동으로 유지해 주는 가상 테이블(fts5 가상 테이블)로, 자체 쿼리 문법을 이해하고 BM25로 결과 순위까지 매겨 줍니다. 게다가 SQLite에 기본 포함되어 있어서 별도로 확장 모듈을 설치할 필요도 없습니다.

fts5 가상 테이블 만들기

FTS5 테이블은 CREATE VIRTUAL TABLE ... USING fts5(...) 구문으로 만들고, 색인할 텍스트 컬럼을 나열하면 됩니다:

CREATE VIRTUAL TABLE posts USING fts5(title, body);

INSERT INTO posts (title, body) VALUES
  ('SQLite 전체 텍스트 검색', 'FTS5는 빠르며 내장되어 있습니다.'),
  ('SQLite의 인덱스', 'B-트리는 SQLite의 대부분 조회를 지원합니다.'),
  ('SQLite를 사용하는 이유', '프로세스 내에서 실행되며 데이터를 하나의 파일에 저장합니다.');

SELECT title FROM posts WHERE posts MATCH 'sqlite';

여기서 눈여겨볼 점이 세 가지 있습니다. 먼저 컬럼에 타입이 없습니다 — FTS5는 모든 값을 텍스트로 다루기 때문이죠. 그리고 MATCH 연산자는 컬럼이 아니라 테이블 이름을 대상으로 씁니다(posts MATCH ... 형태). 마지막으로 쿼리는 대소문자를 구분하지 않고 토큰 단위로 매칭되기 때문에, 'sqlite'로 검색해도 어느 행에 들어 있든 SQLite를 찾아냅니다.

MATCH 쿼리 문법 살펴보기

MATCH에는 단어 하나만 넣을 수 있는 게 아닙니다. 쿼리 문자열은 나름의 작은 문법 체계를 갖추고 있습니다:

CREATE VIRTUAL TABLE posts USING fts5(title, body);
INSERT INTO posts (title, body) VALUES
  ('전문 검색', 'FTS5는 구문 및 접두사 쿼리를 지원합니다.'),
  ('인덱스', 'B-트리 인덱스는 동등 조회 속도를 높여줍니다.'),
  ('트리거', '트리거를 사용하여 FTS를 원본 테이블과 동기화 상태로 유지하세요.');

SELECT title FROM posts WHERE posts MATCH 'fts5 AND prefix';
SELECT title FROM posts WHERE posts MATCH '"keep fts"';
SELECT title FROM posts WHERE posts MATCH 'trig*';
SELECT title FROM posts WHERE posts MATCH 'index NOT trigger';

각각의 의미를 정리하면 이렇습니다:

• 'fts5 AND prefix' — 두 토큰이 모두 등장해야 함 (순서 무관, 위치 무관).
• '"keep fts"' — 정확히 이 순서대로 등장하는 구문 검색.
• 'trig*' — 접두사 검색. trigger, triggers, trigonometry 등에 매칭됩니다.
• 'index NOT trigger' — index는 포함하되 trigger는 포함하지 않는 결과.

특정 컬럼만 노리고 싶다면 column:term 형태로 쓸 수 있습니다. 예를 들어 'title:sqlite' 처럼요. 전체 문법에는 그룹핑을 위한 괄호와 OR 연산자도 있어서, 일반적인 검색 엔진에서 기대하는 표현은 거의 다 지원한다고 보면 됩니다.

BM25로 검색 결과 랭킹 매기기

FTS5는 기본적으로 모든 행에 숨겨진 rank 컬럼을 붙여줍니다. 이 값이 바로 BM25 관련도 점수인데, 숫자가 작을수록 더 잘 맞는 결과입니다. 따라서 이 컬럼 기준으로 정렬하면 가장 관련도 높은 결과부터 받아볼 수 있습니다:

CREATE VIRTUAL TABLE posts USING fts5(title, body);
INSERT INTO posts (title, body) VALUES
  ('SQLite 튜토리얼', 'SQLite를 처음부터 배워보세요.'),
  ('SQLite vs Postgres', '소규모 앱을 위한 SQLite와 Postgres 비교.'),
  ('SQLite SQLite SQLite', 'SQLite를 여러 번 언급하는 게시물.');

SELECT title, rank
FROM posts
WHERE posts MATCH 'sqlite'
ORDER BY rank;

특정 컬럼에 더 큰 가중치를 주고 싶다면, 선언 순서에 맞춰 컬럼별 가중치를 bm25() 함수에 넘겨주면 됩니다.

CREATE VIRTUAL TABLE posts USING fts5(title, body);
INSERT INTO posts (title, body) VALUES
  ('SQLite 인덱스', 'B-트리에 대한 짧은 메모.'),
  ('Postgres 노트', '비교를 위해 sqlite에 대한 단락을 포함합니다.');

SELECT title, bm25(posts, 10.0, 1.0) AS score
FROM posts
WHERE posts MATCH 'sqlite'
ORDER BY score;

첫 번째 글이 위로 올라온 이유는 sqlite가 body(가중치 1×)뿐 아니라 title(가중치 10×)에도 등장했기 때문입니다. 가중치는 실제 앱에서 어떤 필드를 더 중요하게 볼지에 맞춰 정하면 됩니다.

검색 인덱스 동기화 유지하기

가장 단순한 FTS5 테이블은 본문 텍스트를 자체적으로 복사해서 보관합니다. 로그처럼 INSERT만 일어나는 데이터라면 이 방식이 충분하지만, 보통은 이미 원본 테이블이 따로 있고 FTS 인덱스가 그걸 따라가도록 만들고 싶을 겁니다. 이럴 때 깔끔한 패턴이 바로 외부 콘텐츠(external content) FTS 테이블에 트리거 세 개를 붙이는 방식이죠.

CREATE TABLE articles (
    id INTEGER PRIMARY KEY,
    title TEXT,
    body TEXT
);

CREATE VIRTUAL TABLE articles_fts USING fts5(
    title, body,
    content='articles',
    content_rowid='id'
);

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

CREATE TRIGGER articles_ad AFTER DELETE ON articles BEGIN
    INSERT INTO articles_fts(articles_fts, rowid, title, body)
    VALUES ('delete', old.id, old.title, old.body);
END;

CREATE TRIGGER articles_au AFTER UPDATE ON articles BEGIN
    INSERT INTO articles_fts(articles_fts, rowid, title, body)
    VALUES ('delete', old.id, old.title, old.body);
    INSERT INTO articles_fts(rowid, title, body) VALUES (new.id, new.title, new.body);
END;

INSERT INTO articles (title, body) VALUES
  ('SQLite 트리거', 'AFTER INSERT 트리거를 사용하는 방법.'),
  ('FTS5 패턴', '외부 콘텐츠는 데이터를 한곳에 보관합니다.');

SELECT a.id, a.title
FROM articles a
JOIN articles_fts f ON f.rowid = a.id
WHERE articles_fts MATCH 'triggers';

content='articles' 옵션은 FTS5에게 본문 텍스트 자체는 저장하지 말라고 알려주는 설정이에요. 필요할 때마다 원본인 articles 테이블에서 꺼내 쓰는 방식이죠. 트리거는 articles에 일어난 변경을 FTS 인덱스에 그대로 반영하는 역할을 합니다. 결국 articles가 원본 데이터(single source of truth)가 되고, articles_fts는 그 옆에 붙은 검색 전용 구조가 되는 셈이에요.

조금 어색해 보이는 INSERT INTO articles_fts(articles_fts, ...) VALUES ('delete', ...) 구문은 FTS5에서 인덱스에 "이 행을 지워라"라고 명령할 때 쓰는 특수 문법입니다.

검색어 스니펫과 하이라이팅

검색 결과를 보여줄 때는 보통 매칭된 키워드가 강조된 미리보기 형태가 필요하죠. FTS5는 이를 위한 두 가지 함수를 제공합니다:

CREATE VIRTUAL TABLE posts USING fts5(title, body);
INSERT INTO posts (title, body) VALUES
  ('시작하기', 'SQLite는 데이터를 디스크의 단일 파일에 저장하는 작고 빠른 임베디드 데이터베이스 엔진입니다.');

SELECT
    highlight(posts, 0, '<b>', '</b>') AS title,
    snippet(posts, 1, '<mark>', '</mark>', '...', 10) AS preview
FROM posts
WHERE posts MATCH 'sqlite database';

• highlight(table, column_index, open, close)은 매칭된 토큰을 지정한 태그로 감싼 컬럼 전체 텍스트를 돌려줍니다.
• snippet(table, column_index, open, close, ellipsis, token_count)은 매칭 위치를 중심으로 짧은 발췌문을 만들어 줍니다.

컬럼 인덱스는 선언 순서대로 0부터 시작합니다. 검색 UI에서 흔히 보는 "매칭된 단어를 노란색으로 강조" 기능은 결국 이 두 함수로 만들어집니다.

알아두면 좋은 함정들

자주 발이 걸리는 지점 몇 가지를 정리합니다.

• MATCH는 FTS 테이블에서만 동작합니다. 일반 테이블의 컬럼에 MATCH를 쓸 수는 없습니다. 기존 테이블을 검색해야 한다면 위에서 살펴본 외부 콘텐츠 테이블 패턴을 사용하세요.
• rank로 정렬하는 걸 잊지 마세요. 빼먹으면 FTS5는 저장 순서대로 결과를 돌려주는데, 이건 관련도와 아무 상관이 없습니다.
• 토크나이저 선택이 중요합니다. 기본 토크나이저인 unicode61은 유니코드 단어 경계로 분할하고 소문자로 정규화합니다. run이 running과 매칭되도록 어간 추출을 원한다면 porter 토크나이저를 쓰면 됩니다: USING fts5(body, tokenize='porter').
• FTS5는 오타를 허용하는 엔진이 아닙니다. 접두사 검색은 지원하지만 퍼지 매칭은 하지 않습니다. "혹시 이걸 찾으셨나요?" 같은 동작이 필요하다면 FTS5 위에 별도 레이어를 얹어야 합니다.
• 콘텐츠 없는 테이블(content='')은 가볍지만 원문을 잃습니다. 검색은 가능하지만 원본 텍스트는 가져올 수 없고 rowid만 얻을 수 있습니다. 원문을 다른 곳에 저장해 두는 경우에 유용합니다.

다음 주제: 윈도우 함수

지금까지는 텍스트 검색 이야기였습니다. 다음 페이지에서는 결이 다른 고급 쿼리, 즉 윈도우 함수를 다룹니다. 행을 집계로 뭉개지 않고도 누적 합계, 순위, 그룹별 분석을 계산할 수 있게 해주는 기능입니다.