FTS5 — Full-Text Search Without Postgres

Real text search, built in

For substring search across more than a few thousand rows, LIKE '%foo%' is the wrong tool — every row must be scanned. SQLite ships FTS5, a full-text search extension that indexes tokenized content for fast MATCH queries with relevance ranking.

Three things FTS5 gives you that LIKE cannot:

Sub-second search across millions of rows.
Tokenization (word boundaries, optional unicode/porter stemmers, trigram tokenizer for substrings).
BM25 relevance ranking via rank.

Self-reference: Pippa's session search uses FTS5 over the JSONL ground truth — the WebUI's 'find a past message' feature is a MATCH query against an FTS5 virtual table that mirrors the messages table.

Code

Set up FTS5 mirror for an existing table·sql

-- Create the FTS5 virtual table linked to messages
CREATE VIRTUAL TABLE messages_fts USING fts5(
  content,
  content='messages', content_rowid='id',
  tokenize='unicode61'
);

-- Backfill the index
INSERT INTO messages_fts(rowid, content) SELECT id, content FROM messages;

-- Keep it in sync via triggers
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 queries with relevance ranking·sql

-- Search for '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 search
SELECT * FROM messages_fts WHERE messages_fts MATCH '"foreign keys"';

-- Prefix
SELECT * FROM messages_fts WHERE messages_fts MATCH 'sql*';

Exercise

Take a table with at least 50k rows of text content (messages, posts, notes). Build an FTS5 mirror with triggers. Run three MATCH queries: a single word, a phrase, a prefix. Compare wall-clock time vs the equivalent LIKE '%word%' query. Note the gap and pick which workloads warrant the extra schema complexity.

FTS5 — Full-Text Search Without Postgres

Real text search, built in

Code

External links

Exercise

Progress

Comments 0