FTS5 Ranking, Highlighting, External Content, and Maintenance

Learning outcomes

A useful search feature needs more than “matched or did not match.” It needs deterministic identity, ranking, presentation excerpts, and a maintenance model that stays correct as source rows change. This lesson uses FTS5's built-in ranking/highlighting functions first, then deliberately exposes the synchronization risk of external-content indexes.

Ranking is relative evidence, not a business truth

FTS5's built-in bm25() produces a relevance score based on query phrases, term frequency, document frequency, document length, and optional column weights. SQLite multiplies its BM25 result by -1 so that better matches have numerically smaller values and natural ascending order works. The hidden rank column maps to unweighted BM25 by default and can be efficient when sorting.

SELECT rowid,       title,       rank,       bm25(search_fts) AS scoreFROM search_ftsWHERE search_fts MATCH 'pump vibration'ORDER BY rankLIMIT 10;

Do not turn the raw score into a user-facing “87% relevant” number. It is a ranking signal within the configured corpus/query, not a calibrated probability.

Weight important columns deliberately

A title hit may be more informative than the same token buried in a long body. FTS5 allows column weights as trailing bm25() arguments. In bm25(search_fts, 5.0, 1.0), title matches count more heavily than body matches for ranking.

SELECT rowid,       title,       bm25(search_fts, 5.0, 1.0) AS weighted_scoreFROM search_ftsWHERE search_fts MATCH 'bearing'ORDER BY weighted_score;

Tune weights with judged queries and real users, not because 5.0 “sounds important.”

Highlight and snippet turn a match into a usable result

SELECT rowid,       highlight(search_fts, 0, '[', ']') AS marked_title,       snippet(search_fts, 1, '[', ']', ' ... ', 18) AS excerpt,       rankFROM search_ftsWHERE search_fts MATCH 'pump AND vibration'ORDER BY rankLIMIT 5;

highlight() returns a complete selected column with markers around phrase matches. snippet() chooses a short fragment, lets a negative column index ask FTS5 to choose a column automatically, and limits the fragment to at most 64 tokens. If the result is rendered as HTML, escape stored content and control your marker strategy to avoid turning database text into markup injection.

External content avoids duplicate stored text—but creates synchronization work

Suppose the application already owns an ordinary knowledge_article table. An external-content FTS5 table can index its title/body while retrieving displayed content from that table. The FTS index and content table are separate structures. SQLite does not automatically infer how your application wants them synchronized.

CREATE TABLE knowledge_article(  article_id INTEGER PRIMARY KEY,  title TEXT NOT NULL,  body TEXT NOT NULL,  updated_at TEXT NOT NULL);INSERT INTO knowledge_article VALUES(201,'Pump bearing baseline','Cooling pump bearing vibration baseline is 2.1 mm/s.','2026-08-01T10:00:00Z'),(202,'Fan belt inspection','Inspect fan belt tension before bearing replacement.','2026-08-02T09:00:00Z');CREATE VIRTUAL TABLE article_fts USING fts5(  title,  body,  content='knowledge_article',  content_rowid='article_id');

At this exact moment, the content table has rows but the newly created FTS index has no entries for those preexisting rows. A plain SELECT * FROM article_fts can appear to return content through the external table while a MATCH query returns no matches—an important “looks fine until search” failure mode.

Initialize existing data, then maintain future changes

Use FTS5's rebuild command to construct the index from existing external content. AFTER triggers can then maintain INSERT/DELETE/UPDATE changes. The delete command must receive the old indexed values so FTS5 knows which token entries to remove.

INSERT INTO article_fts(article_fts) VALUES('rebuild');CREATE TRIGGER knowledge_article_ai AFTER INSERT ON knowledge_article BEGIN  INSERT INTO article_fts(rowid,title,body)  VALUES(new.article_id,new.title,new.body);END;CREATE TRIGGER knowledge_article_ad AFTER DELETE ON knowledge_article BEGIN  INSERT INTO article_fts(article_fts,rowid,title,body)  VALUES('delete',old.article_id,old.title,old.body);END;CREATE TRIGGER knowledge_article_au AFTER UPDATE ON knowledge_article BEGIN  INSERT INTO article_fts(article_fts,rowid,title,body)  VALUES('delete',old.article_id,old.title,old.body);  INSERT INTO article_fts(rowid,title,body)  VALUES(new.article_id,new.title,new.body);END;

This is one of the places where Chapter 12's trigger caution matters: the trigger set is justified only because index synchronization is a narrow, documented invariant. Keep it small and test insert/update/delete paths explicitly.

Contentless choices are specialized storage contracts

Integrity-check, rebuild, and optimize solve different problems

FTS5 exposes special commands by inserting control text into the hidden column named after the FTS table. They are not interchangeable maintenance rituals.

-- Verify internal FTS structures.INSERT INTO article_fts(article_fts) VALUES('integrity-check');-- For an external-content table, compare the index to content too.INSERT INTO article_fts(article_fts, rank)VALUES('integrity-check', 1);-- Rebuild the complete index from the external content table.INSERT INTO article_fts(article_fts) VALUES('rebuild');-- Merge FTS index b-trees into a single large structure.INSERT INTO article_fts(article_fts) VALUES('optimize');

integrity-check diagnoses consistency and fails if discrepancies are found. rebuild discards/reconstructs the full-text index from content and is unavailable for traditional contentless tables. optimize reorganizes all current FTS segments and may take substantial time on a large index; it is not something to run after every write.

Measure storage and write overhead instead of guessing

An FTS index stores token/posting structures and may store the source content too, depending on mode. That means inserts and updates do extra work. On a disposable database, record file/page size before loading a corpus, after loading the ordinary content, and after building FTS.

PRAGMA page_size;PRAGMA page_count;-- If dbstat is available, inspect FTS5 shadow-table space.SELECT name, SUM(pgsize) AS bytesFROM dbstatWHERE name GLOB 'article_fts*'GROUP BY nameORDER BY bytes DESC;

dbstat is optional. Page-count deltas are coarse but portable enough for a lab. Benchmark search and ingest separately because a design can improve reads while increasing write cost.

Lab: a mini FieldNotes search workflow

INSERT INTO knowledge_article(title,body,updated_at)VALUES('Sensor vibration triage',       'Check mounting, calibration, then compare vibration trend to the pump baseline.',       '2026-08-12T06:00:00Z');SELECT a.article_id,       highlight(article_fts,0,'[',']') AS title,       snippet(article_fts,1,'[',']',' ... ',20) AS excerpt,       article_fts.rankFROM article_ftsJOIN knowledge_article AS a ON a.article_id=article_fts.rowidWHERE article_fts MATCH 'vibration AND baseline'ORDER BY article_fts.rankLIMIT 5;INSERT INTO article_fts(article_fts,rank)VALUES('integrity-check',1);

Then update one article and delete another. Verify that old tokens stop matching and new tokens do match. This lifecycle test is more valuable than a single successful SELECT immediately after index creation.

Failure case: triggers created after existing data

Triggers only react to future changes. Creating them after rows already exist does not backfill the FTS index. If you omit the initial rebuild, plain reads and MATCH reads can disagree in confusing ways. The safe migration sequence is: create index → populate/rebuild existing content → install/test synchronization path → run integrity-check → deploy.

Verification checkpoint

Production judgment and bridge

FTS5 is a database-owned search subsystem with real lifecycle cost. Lesson 4 applies the same virtual-table architecture to a different problem: multidimensional range search with R*Tree bounding boxes.

Authoritative references

• SQLite FTS5 Extension
• SQLite FTS5 external-content tables and pitfalls
• SQLite dbstat Virtual Table