---
title: "Full-text search"
description: "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."
canonical: "https://motherduck.com/glossary/full-text-search/"
related:
  - title: "Search in DuckDB: Integrating Full Text and Embedding Methods"
    url: "https://motherduck.com/blog/search-using-duckdb-part-3/"
  - title: "DuckDB SQL | MotherDuck Docs"
    url: "https://motherduck.com/docs/sql-reference/duckdb-sql-reference/"
  - title: "DuckLake: The Definitive Guide — Live Author Q&A with Matt Martin & Alex Monahan | MotherDuck"
    url: "https://motherduck.com/videos/ducklake-definitive-guide-oreilly-book/"
gated_asset:
  title: "DuckLake: The Lakehouse Table Format"
  url: "https://motherduck.com/lp/ducklake-lakehouse-table-format-book-full/"
---

# 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.

```sql
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.