SQLite FTS5로 전문 검색 구현하기
블로그나 문서 앱을 만들다 보면 처음에는 검색 기능을 아주 단순하게 시작하게 됩니다. 제목이나 본문에 검색어가 들어 있는지 LIKE '%검색어%'로 확인하면 되니까요. 데이터가 몇십 개일 때는 이것만으로도 충분합니다.
그런데 글이 수백 개, 수천 개로 늘어나면 느낌이 달라집니다. 검색 속도가 느려지고, 관련도 순 정렬도 어렵고, 검색 결과에서 어느 부분이 매칭됐는지 보여주기도 번거로워요. “검색”이라는 단어가 한 번 들어간 글과 본문 전체가 검색에 관한 글을 같은 수준으로 다루게 되는 것도 아쉽습니다.
이럴 때 SQLite만 쓰고 있다면 바로 Elasticsearch 같은 검색 엔진을 붙여야 할까요? 꼭 그렇지는 않습니다. SQLite에는 FTS5라는 전문 검색(Full-Text Search) 기능이 들어 있습니다. 작은 블로그, 문서 검색, 메모 앱, 개인용 CMS 정도라면 별도 검색 서버 없이도 꽤 쓸 만한 검색 기능을 만들 수 있어요.
이번 글에서는 SQLite FTS5가 무엇인지, LIKE 검색과 무엇이 다른지, MATCH, bm25(), highlight(), snippet() 같은 기능을 어떻게 쓰는지 살펴보겠습니다.
LIKE 검색의 한계
가장 먼저 떠올릴 수 있는 검색 쿼리는 이런 형태입니다.
SELECT *
FROM posts
WHERE title LIKE '%sqlite%'
OR body LIKE '%sqlite%';이 방식은 단순하고 어디서나 동작합니다. 문제는 검색어 앞에 %가 붙어 있다는 점이에요. LIKE 'sqlite%'처럼 앞부분이 고정된 검색은 인덱스를 활용할 여지가 있지만, LIKE '%sqlite%'는 문자열 중간 어디에 검색어가 나올지 모르기 때문에 대개 많은 행을 훑어야 합니다. 데이터가 늘어날수록 느려지기 쉬운 구조죠.
기능 면에서도 아쉬움이 있습니다. LIKE는 “포함하느냐”만 알려줄 뿐입니다. 어떤 문서가 더 관련 있는지 점수를 매기지 않고, 검색어가 여러 개일 때 어떤 식으로 조합할지도 직접 구현해야 합니다. 검색 결과에서 매칭된 부분을 강조하려면 애플리케이션 코드에서 문자열을 다시 가공해야 하고요.
전문 검색은 접근 방식이 다릅니다. 문서를 미리 단어 단위로 쪼개고, 각 단어가 어떤 문서에 등장하는지 검색용 인덱스를 만들어둡니다. 나중에 검색어가 들어오면 본문 전체를 다시 훑는 대신 이 인덱스를 보고 후보 문서를 빠르게 찾아요. 이런 구조를 역색인(inverted index)이라고 부릅니다.
SQLite FTS5는 이 역색인을 SQLite 안에서 만들어주는 기능입니다.
FTS5란
FTS5는 SQLite의 가상 테이블(virtual table) 모듈입니다. 일반 테이블처럼 SELECT, INSERT, UPDATE, DELETE를 사용할 수 있지만, 내부 구현은 전문 검색 인덱스에 맞게 동작해요.
가장 작은 예제는 이렇게 시작합니다.
CREATE VIRTUAL TABLE posts_fts USING fts5(title, body);posts_fts는 실제 데이터를 행 단위로 저장하는 일반 테이블이라기보다 검색을 위한 특수 테이블입니다. title과 body 컬럼에 들어온 텍스트를 토큰으로 나누고, 어떤 토큰이 어떤 행에 있는지 인덱싱합니다.
데이터를 넣는 방식은 일반 테이블과 비슷합니다.
INSERT INTO posts_fts(title, body)
VALUES (
'SQLite FTS5로 블로그 검색 만들기',
'LIKE 검색이 느려질 때 FTS5 가상 테이블로 전문 검색을 구현할 수 있습니다.'
);검색할 때는 LIKE 대신 MATCH를 씁니다.
SELECT rowid, title
FROM posts_fts
WHERE posts_fts MATCH 'SQLite';FTS5 테이블에는 별도 기본 키를 선언하지 않았더라도 rowid가 있습니다. 보통 원본 테이블의 id와 FTS 테이블의 rowid를 맞춰두면 나중에 조인하기가 편합니다.
기본 검색해보기
예제를 조금 현실적으로 만들어보겠습니다. 먼저 블로그 글을 담는 일반 테이블을 만듭니다.
CREATE TABLE posts (
id INTEGER PRIMARY KEY,
title TEXT NOT NULL,
body TEXT NOT NULL,
published_at TEXT NOT NULL
);샘플 데이터는 네 개만 넣어볼게요.
INSERT INTO posts (title, body, published_at) VALUES
(
'SQLite FTS5로 블로그 검색 만들기',
'LIKE 검색이 느려질 때 FTS5 가상 테이블로 전문 검색을 구현할 수 있습니다.',
'2026-06-25'
),
(
'Cloudflare D1 시작하기',
'D1은 Workers에서 SQLite 문법으로 쿼리할 수 있는 서버리스 데이터베이스입니다.',
'2025-03-17'
),
(
'BM25 알고리즘 이해하기',
'검색 결과를 관련도 순으로 정렬하려면 단어 빈도와 문서 길이를 함께 고려해야 합니다.',
'2026-06-08'
),
(
'SQLite 인덱스 사용법',
'일반 인덱스는 정확한 값 비교와 정렬에는 좋지만 본문 검색에는 한계가 있습니다.',
'2024-01-10'
);이제 검색용 FTS5 테이블을 만들고 원본 데이터를 복사합니다.
CREATE VIRTUAL TABLE posts_fts USING fts5(title, body);
INSERT INTO posts_fts(rowid, title, body)
SELECT id, title, body FROM posts;검색이라는 단어로 찾아보면 제목이나 본문에 해당 단어가 들어 있는 행이 반환됩니다.
SELECT rowid, title
FROM posts_fts
WHERE posts_fts MATCH '검색';rowid title
----- --------------------------------
1 SQLite FTS5로 블로그 검색 만들기
3 BM25 알고리즘 이해하기SQLite로 찾으면 다른 결과가 나옵니다.
SELECT rowid, title
FROM posts_fts
WHERE posts_fts MATCH 'SQLite';rowid title
----- --------------------------------
1 SQLite FTS5로 블로그 검색 만들기
2 Cloudflare D1 시작하기
4 SQLite 인덱스 사용법여기서 Cloudflare D1 시작하기가 같이 나온 이유는 본문에 SQLite가 들어 있기 때문입니다. FTS5는 특정 컬럼만 검색할 수도 있습니다.
SELECT rowid, title
FROM posts_fts
WHERE posts_fts MATCH 'title:SQLite';이렇게 쓰면 title 컬럼에 SQLite가 들어간 문서만 찾습니다. 여러 단어를 함께 검색하면 기본적으로 두 단어를 모두 포함하는 문서를 찾습니다.
SELECT rowid, title
FROM posts_fts
WHERE posts_fts MATCH 'SQLite FTS5';rowid title
----- --------------------------------
1 SQLite FTS5로 블로그 검색 만들기접두어 검색도 가능합니다. 다만 접두어 검색을 자주 쓴다면 테이블을 만들 때 prefix 옵션을 같이 지정하는 편이 좋습니다.
CREATE VIRTUAL TABLE posts_fts USING fts5(
title,
body,
prefix = '2 3'
);이렇게 하면 2글자, 3글자 접두어에 대한 인덱스도 함께 만들어서 sql* 같은 검색을 더 빠르게 처리할 수 있습니다.
bm25로 관련도 순 정렬하기
검색 기능에서 중요한 건 “찾았다”보다 “좋은 결과를 위에 보여준다”입니다. FTS5에는 bm25()라는 보조 함수가 들어 있어서 검색 결과를 관련도 순으로 정렬할 수 있습니다.
SELECT rowid, title, bm25(posts_fts) AS score
FROM posts_fts
WHERE posts_fts MATCH '검색'
ORDER BY score;rowid title score
----- -------------------------------- ---------------------
3 BM25 알고리즘 이해하기 -9.86089644513138e-07
1 SQLite FTS5로 블로그 검색 만들기 -9.3411420204978e-07조금 낯설게 느껴지는 부분이 하나 있습니다. FTS5의 bm25() 점수는 더 좋은 결과일수록 더 작은 값으로 나옵니다. 그래서 ORDER BY score ASC, 즉 오름차순으로 정렬해야 관련도가 높은 문서가 먼저 나와요.
BM25가 어떤 원리로 점수를 매기는지는 BM25 알고리즘 글에서 수식을 직접 계산하며 자세히 다뤘습니다. 여기서는 실무적으로 이렇게 이해하면 충분합니다. 검색어가 문서에 등장하는 정도, 전체 문서에서 그 단어가 얼마나 희귀한지, 문서 길이가 얼마나 긴지 같은 신호를 함께 보고 점수를 매기는 방식입니다.
FTS5의 좋은 점은 이 계산을 우리가 직접 구현하지 않아도 된다는 겁니다. 검색 인덱스가 이미 단어 빈도와 문서 길이 정보를 알고 있으니, SQL에서 bm25()만 호출하면 됩니다.
검색 결과 꾸미기
검색 결과 페이지에서는 보통 매칭된 부분을 강조해서 보여줍니다. FTS5는 이 작업도 도와줍니다. highlight()는 특정 컬럼에서 매칭된 단어를 원하는 문자열로 감싸고, snippet()은 매칭된 주변 문맥만 잘라서 보여줍니다.
SELECT
rowid,
highlight(posts_fts, 0, '<mark>', '</mark>') AS title,
snippet(posts_fts, 1, '<mark>', '</mark>', '...', 12) AS preview
FROM posts_fts
WHERE posts_fts MATCH '검색';rowid: 1
title: SQLite FTS5로 블로그 <mark>검색</mark> 만들기
preview: LIKE 검색이 느려질 때 FTS5 가상 테이블로 전문 검색을 구현할 수 있습니다.
rowid: 3
title: BM25 알고리즘 이해하기
preview: <mark>검색</mark> 결과를 관련도 순으로 정렬하려면 단어 빈도와 문서 길이를 함께 고려해야 합니다.highlight(posts_fts, 0, ...)에서 0은 첫 번째 컬럼인 title을 뜻합니다. snippet(posts_fts, 1, ...)의 1은 두 번째 컬럼인 body를 뜻하고요. 마지막 숫자 12는 스니펫에 담을 토큰 수입니다.
이 함수들을 쓰면 애플리케이션 코드에서 검색어를 찾아 HTML 태그를 끼워 넣는 작업을 줄일 수 있습니다. 물론 사용자 입력을 그대로 HTML로 출력하면 안 되므로, 실제 화면에 렌더링할 때는 사용하는 프레임워크의 이스케이프 규칙을 꼭 확인해야 합니다.
원본 테이블과 함께 쓰기
위 예제에서는 posts 테이블과 posts_fts 테이블에 같은 데이터를 따로 저장했습니다. 작게 시작할 때는 이해하기 쉽지만, 운영 코드에서는 원본 데이터와 검색 인덱스가 어긋날 수 있다는 문제가 생깁니다.
예를 들어 posts의 제목을 수정했는데 posts_fts를 갱신하지 않으면 검색 결과에는 예전 제목이 남습니다. 이런 중복을 줄이려면 FTS5의 외부 콘텐츠 테이블(external content table) 패턴을 사용할 수 있습니다.
CREATE VIRTUAL TABLE posts_fts USING fts5(
title,
body,
content = 'posts',
content_rowid = 'id'
);이렇게 만들면 FTS5 테이블은 검색 인덱스를 담당하고, 실제 원문은 posts 테이블에 둡니다. 기존 데이터를 한 번 인덱싱하려면 rebuild 명령을 실행합니다.
INSERT INTO posts_fts(posts_fts) VALUES('rebuild');그다음 검색 결과에서 원본 테이블과 조인할 수 있습니다.
SELECT posts.id, posts.title, posts.published_at
FROM posts_fts
JOIN posts ON posts.id = posts_fts.rowid
WHERE posts_fts MATCH 'SQLite'
ORDER BY bm25(posts_fts);새 글이 추가되거나 수정될 때 인덱스를 자동으로 맞추려면 트리거를 붙입니다.
CREATE TRIGGER posts_ai AFTER INSERT ON posts BEGIN
INSERT INTO posts_fts(rowid, title, body)
VALUES (new.id, new.title, new.body);
END;
CREATE TRIGGER posts_ad AFTER DELETE ON posts BEGIN
INSERT INTO posts_fts(posts_fts, rowid, title, body)
VALUES ('delete', old.id, old.title, old.body);
END;
CREATE TRIGGER posts_au AFTER UPDATE ON posts BEGIN
INSERT INTO posts_fts(posts_fts, rowid, title, body)
VALUES ('delete', old.id, old.title, old.body);
INSERT INTO posts_fts(rowid, title, body)
VALUES (new.id, new.title, new.body);
END;트리거까지 붙이면 애플리케이션 코드는 posts 테이블만 수정해도 됩니다. 검색 인덱스 갱신은 데이터베이스가 맡습니다.
물론 모든 프로젝트에서 처음부터 이 구조가 필요한 건 아닙니다. 작은 스크립트나 읽기 전용 데이터라면 FTS 테이블에 직접 데이터를 넣는 방식이 더 단순할 수 있어요. 하지만 사용자가 글을 계속 작성하고 수정하는 서비스라면 원본 테이블과 FTS 인덱스를 분리하는 편이 안전합니다.
Cloudflare D1에서도 쓰기
여기까지 보면 자연스럽게 이런 질문이 나옵니다. “SQLite 기능이면 Cloudflare D1에서도 쓸 수 있나?”
네, 쓸 수 있습니다. D1은 SQLite 기반의 서버리스 데이터베이스이고, Cloudflare D1 문서에서도 지원하는 SQLite 확장 중 하나로 FTS5를 명시하고 있습니다. 그래서 로컬 SQLite에서 작성한 FTS5 스키마를 D1 마이그레이션에 넣어 사용할 수 있어요.
예를 들어 D1 마이그레이션 파일에 다음처럼 테이블과 FTS5 인덱스를 함께 정의할 수 있습니다.
CREATE TABLE posts (
id INTEGER PRIMARY KEY,
title TEXT NOT NULL,
body TEXT NOT NULL,
published_at TEXT NOT NULL
);
CREATE VIRTUAL TABLE posts_fts USING fts5(
title,
body,
content = 'posts',
content_rowid = 'id'
);마이그레이션을 로컬에서 먼저 적용해봅니다.
npx wrangler d1 migrations apply my-database --local프로덕션 D1에 적용할 때는 --remote를 붙입니다.
npx wrangler d1 migrations apply my-database --remoteWorker 코드에서는 일반 D1 쿼리처럼 실행하면 됩니다.
export default {
async fetch(request: Request, env: Env): Promise<Response> {
const url = new URL(request.url);
const q = url.searchParams.get("q")?.trim();
if (!q) {
return Response.json([]);
}
const { results } = await env.DB.prepare(
`
SELECT
posts.id,
posts.title,
snippet(posts_fts, 1, '<mark>', '</mark>', '...', 20) AS preview
FROM posts_fts
JOIN posts ON posts.id = posts_fts.rowid
WHERE posts_fts MATCH ?
ORDER BY bm25(posts_fts)
LIMIT 20
`,
)
.bind(q)
.all();
return Response.json(results);
},
};여기서도 검색어는 문자열로 직접 이어 붙이지 말고 bind()로 넘기는 편이 좋습니다. MATCH 쿼리 문법 자체는 사용자가 입력한 검색 연산자를 해석할 수 있으므로, 공개 검색창에서는 허용할 문법을 정하거나 단순 검색어만 받도록 전처리하는 것도 고려해야 합니다.
D1에서 특히 좋은 점은 Workers와 같은 배포 단위 안에서 검색 API를 바로 만들 수 있다는 겁니다. 작은 문서 사이트나 SaaS의 도움말 검색이라면 외부 검색 서버를 따로 운영하지 않고 D1 하나로 시작할 수 있습니다. 이미 Wrangler CLI와 D1 마이그레이션을 쓰고 있다면 추가 인프라가 거의 늘어나지 않는 셈이죠.
한국어 검색에서 조심할 점
FTS5를 소개할 때 꼭 짚고 넘어가야 할 부분이 있습니다. SQLite FTS5는 강력하지만 한국어 형태소 분석기까지 내장하고 있지는 않습니다.
기본 토크나이저는 공백과 문장부호를 기준으로 텍스트를 나눕니다. 영어처럼 단어 사이에 공백이 있는 언어에서는 꽤 잘 맞지만, 한국어는 조사와 어미가 붙고 띄어쓰기도 검색 품질에 큰 영향을 줍니다. 예를 들어 “검색”, “검색을”, “검색이”, “검색엔진”을 같은 의도로 보고 싶어도 기본 설정만으로는 기대처럼 묶이지 않을 수 있어요.
그래서 한국어 검색을 진지하게 다룬다면 보통 두 가지 중 하나를 선택합니다.
우선 입력 데이터를 저장하기 전에 검색용 텍스트를 따로 만들어둘 수 있습니다. 제목과 본문에서 조사나 기호를 어느 정도 정리하고, 자주 쓰는 동의어를 함께 넣는 방식입니다. 완벽한 형태소 분석은 아니어도 도메인이 좁다면 꽤 실용적이에요.
또 다른 방법은 FTS5를 1차 필터로 쓰고, 애플리케이션 레벨에서 후처리를 하는 겁니다. FTS5로 빠르게 후보를 줄인 뒤, 더 정교한 점수 계산이나 동의어 처리를 별도로 적용하는 식이죠. 나중에 규모가 커지면 의미 검색이나 하이브리드 검색으로 확장할 수도 있습니다.
중요한 건 FTS5가 검색 문제를 전부 해결해주는 만능 도구는 아니라는 점입니다. 대신 “별도 검색 서버를 붙이기 전 단계”에서 상당히 넓은 영역을 커버해줍니다.
언제 FTS5로 충분할까
FTS5가 잘 맞는 경우는 분명합니다. 데이터가 SQLite나 D1에 이미 있고, 검색 대상이 수천에서 수십만 문서 정도이며, 검색 요구사항이 키워드 기반이라면 좋은 선택입니다. 블로그 검색, 문서 검색, 메모 앱, 작은 관리자 페이지, 제품 도움말 검색 같은 경우가 여기에 들어갑니다.
운영 관점에서도 장점이 큽니다. 별도 검색 클러스터가 없으니 배포와 백업이 단순하고, 로컬 개발 환경에서도 같은 SQL을 그대로 돌려볼 수 있습니다. 특히 D1처럼 SQLite 문법을 그대로 가져가는 환경에서는 로컬 SQLite에서 검증한 검색 쿼리를 Workers로 옮기기 쉽습니다.
반대로 다음 요구사항이 강하다면 전문 검색 엔진이나 벡터 검색 도구를 검토하는 편이 좋습니다. 복잡한 형태소 분석, 동의어 사전, 오타 교정, 자동 완성, 대규모 로그 검색, 다국어 랭킹 튜닝, 분산 인덱싱 같은 기능이 필요하다면 FTS5만으로는 부족할 수 있어요.
즉 FTS5는 “작지만 진짜 검색”을 만들기 좋은 도구입니다. LIKE보다 훨씬 검색답고, Elasticsearch보다 훨씬 가볍습니다. 이 중간 지점이 필요한 프로젝트가 생각보다 많습니다.
마치며
SQLite FTS5는 SQLite 안에서 전문 검색을 구현할 수 있게 해주는 현실적인 선택지입니다. CREATE VIRTUAL TABLE ... USING fts5로 검색 인덱스를 만들고, MATCH로 검색하고, bm25()로 관련도 순 정렬을 하고, highlight()와 snippet()으로 결과 화면까지 다듬을 수 있습니다.
특히 Cloudflare D1에서도 FTS5를 사용할 수 있다는 점이 반갑습니다. Workers 기반 앱에서 작은 검색 기능이 필요할 때 외부 검색 서비스를 붙이기 전에 D1 + FTS5 조합을 먼저 검토해볼 만해요. 나중에 검색 요구사항이 커지면 그때 전문 검색 엔진이나 벡터 검색으로 확장해도 늦지 않습니다.
더 자세한 SQL 문법과 옵션은 SQLite FTS5 공식 문서를 참고하세요. D1에서 지원되는 SQLite 확장은 Cloudflare D1 SQL statements 문서에서 확인할 수 있습니다.
This work is licensed under
CC BY 4.0
