Full-text search
Full-text search is a technique for searching text content by relevance to a query — matching keywords, handling stemming and ranking results — rather than requiring an exact substring or equality match.
Overview
Full-text search (FTS) systems index the words within text documents so that queries can retrieve and rank documents by relevance, rather than requiring an exact match. Under the hood, most FTS implementations build an inverted index — a mapping from each distinct term to the list of documents containing it — which makes keyword lookups fast even over large text collections. Results are typically ranked using a scoring function; the most common is BM25 (Best Matching 25), which scores a document based on term frequency, document length, and how rare a term is across the whole collection.
Full-text search in DuckDB
DuckDB includes an fts extension that implements an inverted index and BM25 scoring, similar in spirit to SQLite's FTS5.
Copy code
INSTALL fts;
LOAD fts;
PRAGMA create_fts_index('articles', 'id', 'title', 'body');
SELECT id, title, score
FROM (
SELECT *, fts_main_articles.match_bm25(id, 'duckdb analytics') AS score
FROM articles
)
WHERE score IS NOT NULL
ORDER BY score DESC;
create_fts_index builds the inverted index (with options for stemming, stopword removal, and case folding), and the generated match_bm25 macro scores rows against a query string. Because this runs inside DuckDB, full-text relevance ranking can be combined directly with regular SQL filters, joins, and aggregations in the same query — no separate search service required.
Full-text search versus vector/semantic search
Full-text search matches on literal terms (with some normalization via stemming), which makes it precise for exact keyword and phrase matching but blind to synonyms or paraphrasing. Vector/embedding-based semantic search captures meaning rather than exact wording, at the cost of being less precise for exact terms like product codes or names. Many modern search systems combine both approaches ("hybrid search"), and DuckDB can support this pattern by running FTS's match_bm25 and vector similarity from its vss extension in the same query.
Related terms
A vector database stores high-dimensional numeric vectors (typically embeddings) and provides fast similarity search over them, powering use cases like semantic search and retrieval-augmented generation.
Embeddings →Embeddings are dense numeric vector representations of data — text, images, audio, or other objects — learned so that semantically similar inputs end up close together in vector space.
Database index →A database index is a data structure that stores a subset of a table's data in an order optimized for fast lookups, letting queries find matching rows without scanning the whole table.
Lance format →Lance is a modern, open-source columnar file format designed for machine learning and vector-search workloads, offering fast random access, versioning, and native support for embeddings alongside tabular data.
extension →An extension in DuckDB is a modular component that adds functionality to the core database system.
query engine →A query engine parses, optimizes, and executes SQL queries against data sources. Learn its core architecture, how it differs from a database, and see examples like DuckDB and Presto.
FAQS
No. The fts extension runs entirely inside DuckDB as a SQL extension, building an inverted index over a table's text columns and exposing BM25 relevance scoring through a generated SQL function, so it can replace a dedicated search service for small-to-medium text search workloads.
BM25 (Best Matching 25) is a widely used relevance-ranking function for full-text search that scores documents based on term frequency, document length, and how common or rare each query term is across the indexed collection.
