SQLite FTS5 CJK Search: Trigram Tokenizer and a 2-Char LIKE Fallback

I saved a note as 커넥션풀 and searched for 풀. Nothing came back.

SQLite FTS5 uses unicode61 by default. It indexes the front of tokens split on spaces. 커넥션풀 is one token, so it finds 커넥 but not 풀.

The gap is large in Korean, where particles and compound nouns attach: 회의록에서, 프로젝트관리. An index that looks only at the front finds half.

Filling the gap with LIKE scans the table on every key

The first fix was simple: use LIKE '%풀%' for what FTS cannot find.

It works. The problem is that a LIKE with % at the front cannot use an index and reads the table from beginning to end.

We search five kinds of body: title, original, completion note, summary, and assembled text. There are also attachment filenames and OCR text, document-page text, annotations, transcriptions, and link bodies.

Every key press read these tables in full.

So I gave up real-time search

Searching on every keystroke froze the app, so I delayed search until Enter.

That covered a performance problem by cutting the feature. Users had to finish typing and press Enter. I gave up the experience of seeing a result after two or three characters.

The comment records the cause.

Scanning three tables on every key caused search to be delayed until Enter.

Reducing the feature leaves the performance cause intact.

trigram indexes every three-character window

FTS5 has a trigram tokenizer. It cuts text into overlapping groups of three characters.

커넥션풀 is indexed like this.

커넥션 / 넥션풀

풀 is contained in 넥션풀, so the index finds it. Partial match becomes an index lookup.

In GRDB I declare it this way.

// AppDatabase.swift:1923-1933 (v93 마이그레이션)
let trigram = FTS5TokenizerDescriptor(components: ["trigram"])

try db.dropFTS5SynchronizationTriggers(forTable: "item_fts")
try db.drop(table: "item_fts")
try db.create(virtualTable: "item_fts", using: FTS5()) { t in
  t.synchronize(withTable: "item")   // 원본 테이블과 자동 동기화
  t.tokenizer = trigram
  t.column("title")
  t.column("rawInputText")
  t.column("completionNote")
}

synchronize(withTable:) creates triggers. When a row enters or changes in item, the index follows it. There is no separate place in code to update the index.

The index uses external content. The original table is canonical and the index is derived. If the index breaks, it can be rebuilt from the original.

Comparing the two tokenizers with the same queries gives this.

Query	unicode61	trigram
커넥 (token front)	Hit	Hit
션풀 (token middle)	Miss	Hit
커넥션풀	Hit	Hit
Two-character query	Hit	Miss, sent to scan
풀* prefix marker	Has meaning	Does nothing
Storage	Small	Larger, all three-character windows

The last two rows are what changed and what was lost. The prefix marker loses meaning, so query-building code must change, and indexing every three-character window grows the index. I did not measure how much.

I recover two-character queries with a scan, but by then candidates have already been narrowed.

Three characters hit; two do not

The name trigram means three characters. Two characters do not exist in the index.

I measured it in SQLite 3.54. A three-character MATCH hits; a two-character one misses.

// SearchQueryNormalization.swift:62-63
/// trigram은 3글자 창을 색인한다 — 그보다 짧은 낱말은 인덱스에 존재하지 않는다.
static let trigramMinimumLength = 3

The code must know this. Sending a two-character query to MATCH quietly returns zero results.

I do not add the prefix marker

Adding an asterisk like 풀* enables prefix search in FTS5 and is useful with unicode61.

It is different with trigram.

// SearchQueryNormalization.swift:33-36
/// 접두 표시(`*`)는 붙이지 않는다: trigram에서 그것은 아무 일도 하지 않고
/// (2자 접두 질의 실측 0건), 따옴표 안의 낱말 자체가 이미 **부분 일치**로 동작한다.

trigram is already partial matching. The asterisk does nothing, and a two-character prefix query measured zero results.

Changing a tokenizer means checking query grammar too.

I separate what the index accepts from what must be scanned

I cannot give up two-character searches. Users type words such as AI and 회의.

So I split queries in two.

// SearchQueryNormalization.swift:11-15
/// 색인은 trigram이라 **3글자부터** 인덱스로 잡히므로, 질의는 스스로를 둘로
/// 가른다: 인덱스가 받는 토큰(`ftsExpression`)과 그러지 못해 훑어야 하는
/// 나머지(`residualLikePatterns`). 둘을 합치면 언제나 원래 토큰 전부다 —
/// 한쪽만 쓰면 질의의 일부가 조용히 사라진다.

The last sentence is the safety guard: the union of the two lists must equal the original tokens.

Query	Accepted by index	To scan
커넥션풀 설정	커넥션풀, 설정	None
AI 요약	요약	AI
회의 록	None	회의, 록

The third row is the old slow path, but it is different now. The index narrows candidates first, and only those candidates are checked for short tokens.

Scanning the entire table and checking a few dozen candidates are different costs.

I do not accept FTS operators from the caller

A user may type title:회의 or AI OR 요약. Passing it directly to FTS interprets it as FTS5 syntax.

// SearchQueryNormalization.swift:33-39
/// FTS 연산자는 호출자에게서 받지 않는다 — 토큰을 통째로 따옴표에 넣어
/// `title:`이나 `OR` 같은 글자도 평범한 검색어가 되게 한다.
public var ftsExpression: String? {
  let searchable = trigramTokens.map { "\"\($0)\"" }
  return searchable.isEmpty ? nil : searchable.joined(separator: " AND ")
}

I quote each token. OR becomes the word “OR,” not an operator.

Opening search grammar to users is a separate feature. If I do not open it, syntax characters must not leak in.

I expanded indexing to six tables

The same migration created trigram indexes in six places.

Table	Indexed columns
item	Title, original, completion note
item_attachment	Filename, OCR text
item_attachment_page	Page text
annotation	Annotation body
voice_transcript_segment	Voice transcription
link_extract	Link body markdown

Text in photos and words transcribed from audio are found by the same search. To a user, saved material is one thing, so search should be one thing too.

I use only the strongest score instead of adding scores

Several paths can find the same document, in its title and OCR text.

How scores are combined determines ordering. At first I thought to add them: a document found in several places must be more relevant.

That was wrong.

// SearchEngine.swift:139-142
// 여러 검색 경로가 같은 문서를 되찾았다는 사실은 관련도가 아니다.
// 가장 강한 실제 필드 일치만 점수로 삼아, 필드가 많거나 최신이라는 이유로
// 정확 제목을 덮지 못하게 한다.
lexicalScore = max(lexicalScore, lexicalContribution)

It is max, not a sum.

Adding favors documents with more fields. A note with ten attachments has ten places to match, and can cover a note whose title exactly matches.

When a user searches 회의록, the desired note is the one titled 회의록, not another note with the term in ten attachment filenames.

bm25 must be in the same query as MATCH

FTS5's bm25() calculates relevance order, with one constraint.

// SearchEngine.swift:358-360
// 색인이 걸릴 때만 `JOIN`으로 붙인다 — bm25는 MATCH가 같은 질의 안에
// 있어야 부를 수 있고, 그 순위가 상한(`sourceLimit`)에 걸릴 때 무엇을
// 남길지 정한다. 짧은 토큰뿐이면 색인이 없으므로 최신순으로 훑는다.

Without MATCH, I cannot call bm25(). So a query that cannot use the index, such as a two-character query, has no relevance score and uses newest first.

Each source has a candidate cap and the final result cap is 40. Ranking decides what is discarded at a cap.

I keep semantic search behind lexical search

I also want a different but similar word: searching 회의 should ideally find 미팅.

I turn words into vectors with NLEmbedding and calculate cosine similarity. This is an on-device model.

// SearchEngine.swift:148-150
var finalScore: Float {
  lexicalScore > 0 ? lexicalScore : (semanticScore ?? 0)
}

I do not mix the scores. If a text match exists, I use only it; only when none exists do I look at semantic score.

Mixing them can push an exact text match below a semantically similar result. Showing a document containing the user's exact text first is predictable.

I also set caps and a threshold.

Value	What
0.7	Minimum cosine value accepted as semantic match
200	Semantic candidate cap, newest first
5	Expansion cap
40	Final result cap

0.7 is not low. An archived document said 0.35, but both source and work record say 0.7. The documentation was stale.

I do not load a tens-of-MB model before the first search

NLEmbedding is an on-device model and loading it costs synchronous disk I/O. Loading it at launch slows startup.

// SearchEngine.swift:176-182
// NLEmbedding 로드(수십 MB 온디바이스 모델, 동기 디스크 I/O)는 최초
// 실제 사용 시점까지 지연 — struct 저장 프로퍼티는 lazy 불가하므로
// 클로저가 캡처하는 박스 클래스로 1회 로드·캐시한다.
final class LazyEmbedding {
  lazy var value: NLEmbedding? = NLEmbedding.wordEmbedding(for: .korean)
}
let box = LazyEmbedding()

SearchEngine is a struct, so it cannot use lazy var. I made a class for a closure to capture. While the closure lives, the box lives too, and loading happens once.

Sentence vectors are the average of word vectors: obtain each word vector, add them, and divide by count.

I moved archive state from a tag to a column

This decision is not directly about search, but its trace remains in the same code.

Archived records are excluded from search by default. Previously archive state was a reserved word in the tag array: a 보관함 tag meant archived.

// SearchEngine.swift:200-203
/// 보관한 기록은 기본으로 빠진다. 예전에는 `scopeTag: "보관함"`이 그것을
/// 되돌리는 수단이었다 — 보관 상태가 태그 배열 안의 예약어였기 때문이다.
/// 상태를 컬럼으로 뺀 지금(v87) 그 낱말은 그냥 사용자 태그라서 같은 일을
/// 할 수 없고, 해서도 안 된다. **보관함 안을 찾는 것은 별도의 축이다.**

Representing state as a tag prevents a user from using that word as a tag, and deleting the tag changes state.

I moved it to a column in v87. Since then 보관함 is an ordinary user tag. Searching inside the archive is a separate axis through includeArchived.

State and user data placed together eventually have to be separated.

I restored real-time search

I undid the delay until Enter. Now a character is immediately a query.

There was one place that put the query into the model, a function that confirmed Enter. I changed that place to run whenever input changes.

The debounce is 140ms. A new character cancels the previous generation before the query, so SQLite does not run a search that will be discarded.

The current result stays in place until the next answer arrives. If the screen goes empty when the user types one more character, they read that as the result disappearing.

I deleted one more thing: a screen that made suggestions by scanning titles of 200 already-loaded items. With real search on every character, there is no reason for a separate title-only suggestion. I removed 16 dead localized strings and their contract-test entries too.

I checked without pressing Enter once

I typed one character at a time in the simulator.

Input	Screen
저	1 search result
저장된	1 result
저장된다	1 result
ㄴㅁㅍ	No matching records

The third row is the result of this work. The record's title is AUTO SAVE TEST 수정됨; it was found by a word inside the body, not the title.

The last row is also necessary. I need to distinguish a real zero-result search from search not running.

I added six contract tests: find page text when combined text is empty; zero results before link collection and a hit afterward; match 무제표 inside 연결재무제표; find two-character queries without the index; only the last key reaches the engine; and keep the previous result alive for the next key.