Short answer

FTS5 is SQLite’s full-text search extension. It is a virtual table module: you create a table with CREATE VIRTUAL TABLE ... USING fts5(...), insert text into it like any other table, and query it with the MATCH operator instead of LIKE. Under the hood, FTS5 tokenizes the text and builds an inverted index from a set of b-trees. It can rank results by relevance with its built-in BM25 function, extract matching snippets, and highlight matched terms. FTS5 is the newer-generation successor to FTS3 and FTS4, although migrating an existing application requires some schema and query changes.

How it works

Relational databases are good at exact matches and range queries, but WHERE body LIKE '%keyword%' on a large text column usually means scanning every row. A conventional index cannot speed up a pattern that begins with a wildcard. Full-text search applies the same principle as a book index: instead of scanning every page for a word, it looks up the word and jumps directly to its occurrences.

FTS5 builds this index automatically. When you insert a row, it runs the text through a tokenizer. The default unicode61 tokenizer treats Unicode letters and numbers as token characters; by default, searches are case-independent and most Latin-script diacritics are removed. The ascii tokenizer uses simpler ASCII rules, porter adds stemming (so “running” and “run” can resolve to the same stem), and trigram indexes overlapping three-character sequences to support substring rather than only token-based matching. Each resulting token gets an index entry that points back to its row and column. FTS5 stores these entries in segment b-trees that are periodically merged, an approach similar in spirit to an LSM-tree’s write path.

Once the content is indexed, SELECT * FROM docs WHERE docs MATCH 'sqlite AND search' finds matching rows through the index instead of scanning the source text. FTS5 also provides auxiliary functions for use in queries: bm25() scores each matching row using the BM25 relevance algorithm (a lower value means a better match, so you typically use ORDER BY bm25(docs)), snippet() returns a short excerpt around a match, and highlight() wraps matched terms in markup of your choice, such as <b>...</b>.

By default, an FTS5 table stores a copy of the indexed text alongside the index. If the source text already lives in another table, you can configure an external content table with content='mytable'. This avoids storing a second copy of the source columns, but the application must keep the FTS index synchronized with inserts, updates, and deletes—commonly with database triggers. FTS5 also has a contentless mode (content='') for cases where only the index is needed and the original column values do not need to be read back. Contentless tables have additional update and delete constraints.

FTS5 is not a drop-in replacement for FTS3 or FTS4: virtual-table declarations, query syntax, and auxiliary functions differ in places. SQLite describes FTS5 as a newer version of FTS4 that fixes issues which could not be addressed without breaking compatibility. It also provides built-in BM25 ranking and extensive configuration, including custom tokenizers, prefix= indexes that accelerate prefix queries, and detail= modes that can reduce index size when phrase or NEAR queries are not required.

Examples

A minimal setup:

CREATE VIRTUAL TABLE docs USING fts5(title, body);

INSERT INTO docs(title, body) VALUES
  ('SQLite basics', 'SQLite is a small, fast, embedded SQL database engine.'),
  ('Full-text search', 'FTS5 lets you search text efficiently using an index.');

SELECT title, bm25(docs) AS rank, snippet(docs, 1, '[', ']', '...', 8)
FROM docs
WHERE docs MATCH 'search'
ORDER BY rank;

FTS5 fits applications that need to search their own data without operating a separate search server:

  • Desktop and mobile apps built on SQLite—such as note-taking apps, email clients, and password managers—can use it for in-app search over notes, messages, or entries.
  • Datasette and similar SQLite-backed data tools can use FTS5 to search published datasets.
  • Static-site and documentation search tools can build an FTS5 index at build time and ship it with the site for client-side search through SQLite compiled to WebAssembly.
  • Any application that already uses SQLite as its embedded datastore can add full-text search without introducing Elasticsearch or another server when that extra infrastructure would be disproportionate to the data volume.

FAQ

Is FTS5 faster than LIKE ‘%term%’? For large collections and token-based searches, usually by a wide margin. A LIKE pattern with a leading wildcard cannot use a conventional index and generally requires a full scan, whereas FTS5 looks up matching tokens in its inverted index. The exact difference depends on the data and query.

Do I need to enable FTS5 separately? It depends on the SQLite build. FTS5 has been included in the SQLite amalgamation since version 3.9.0 (2015-10-14), but it still has to be enabled at compile time. The amalgamation’s configure script currently enables it by default, while the canonical source tree’s configure script does not. You can check your runtime with SELECT sqlite_compileoption_used('ENABLE_FTS5');; a result of 0 means that the build does not include FTS5.

Can FTS5 rank results by relevance? Yes. Its built-in bm25() auxiliary function implements BM25 ranking. In SQLite’s implementation, a lower score means a better match.

Does FTS5 support fuzzy or partial-word search? Whole-token matching is the default. Prefix queries (term*) are built in, and the trigram tokenizer supports substring matching. True typo-tolerant fuzzy matching is not built into FTS5; it requires additional logic or an extension such as spellfix1.

Do I need to keep an FTS5 index in sync manually? Only when the indexed content lives outside the FTS5 table, for example with content='othertable'. The application must then keep the index synchronized, often with triggers. A standalone FTS5 table—the default, with no content= option—maintains its own index as rows are inserted, updated, or deleted.

Sources