Full-text search with FTS5

Virtual tables, tokenizers, prefix and phrase queries, bm25 ranking, external-content tables and snippet highlighting.

Creating an FTS5 table

-- a standalone index: it stores its own copy of the text
create virtual table book_fts using fts5(
  title,
  description,
  tokenize = 'porter unicode61 remove_diacritics 2'
);

insert into book_fts (title, description) values ('The Dispossessed', 'A novel about an anarchist moon');

-- external content: the FTS table reads from the base table
create virtual table book_fts using fts5(
  title,
  description,
  content = 'book',
  content_rowid = 'id',
  tokenize = 'porter unicode61'
);

-- keep it in sync with triggers
create trigger book_ai after insert on book begin
  insert into book_fts (rowid, title, description) values (new.id, new.title, new.description);
end;
create trigger book_ad after delete on book begin
  insert into book_fts (book_fts, rowid, title, description)
    values ('delete', old.id, old.title, old.description);
end;
create trigger book_au after update on book begin
  insert into book_fts (book_fts, rowid, title, description)
    values ('delete', old.id, old.title, old.description);
  insert into book_fts (rowid, title, description) values (new.id, new.title, new.description);
end;

-- backfill an existing table
insert into book_fts (book_fts) values ('rebuild');

• An external-content table saves the duplicated text but requires the triggers. Without them the index drifts, and the drift is invisible until a search returns the wrong document.
• rebuild reconstructs the whole index from the content table - the fix when the triggers were missing.
• The delete that precedes an update must pass the old column values, which is why the trigger above repeats them.
• integrity-check verifies that an external-content index matches its content table.

Query syntax and ranking

-- a single term
select rowid from book_fts where book_fts match 'anarchist';

-- AND is implicit, OR is explicit, NOT excludes
select rowid from book_fts where book_fts match 'utopia OR anarchist';
select rowid from book_fts where book_fts match 'system NOT slavery';

-- a phrase and a prefix
select rowid from book_fts where book_fts match '"distributed systems"';
select rowid from book_fts where book_fts match 'anarch*';

-- restrict to a column
select rowid from book_fts where book_fts match 'title: dispossessed';

-- NEAR with a distance
select rowid from book_fts where book_fts match 'NEAR(shevek anarchist, 10)';

-- ranked results with bm25 and a highlighted snippet
select b.id, b.title,
       bm25(book_fts, 10.0, 1.0) as score,
       snippet(book_fts, 1, '<b>', '</b>', '...', 20) as excerpt
from book_fts
join book b on b.id = book_fts.rowid
where book_fts match ?
order by score
limit 20;

• Column weights let you favour the title over the body: bm25(book_fts, 10.0, 1.0) multiplies the title contribution by ten.
• Scores are negative, so order by score ascending is correct - a common bug is reversing it.
• snippet and highlight operate on the FTS table, not the base table, and require the match to be in the query.
• The porter tokenizer stems English words, so 'running' matches 'run'. It is English-specific; use unicode61 alone for other languages.

Options and maintenance

-- query the vocabulary to build an autocomplete list
create virtual table book_vocab using fts5vocab(book_fts, 'row');
select term, doc, cnt from book_vocab where term like 'anar%' order by cnt desc limit 10;

-- an external-content-less variant with contentless tables (insert-only)
create virtual table log_fts using fts5(body, content = '');

-- maintenance
insert into book_fts (book_fts) values ('optimize');    -- merge segments, smaller and faster
insert into book_fts (book_fts) values ('integrity-check');

-- configure through the table options
-- prefix indexes speed up prefix queries at the cost of size
create virtual table book_fts using fts5(title, prefix = '2 3');

• optimize merges the internal segments. It is expensive, so run it after bulk changes rather than on every write.
• Prefix indexes (prefix = '2 3') make anarch* fast. Without them a prefix query scans the term list.
• A contentless table stores only the index - it cannot return the original text, so snippet and highlight are unavailable.
• FTS5 tables live in the same file, so a backup includes them and a VACUUM compacts them along with everything else.

FAQ

Related

JSON, generated columns and date functions Indexes, ANALYZE and EXPLAIN QUERY PLAN

Last refreshed 2026-09-18.