Full-Text Search Extension

Full-Text Search is an extension to DuckDB that allows for search through strings, similar to SQLite's FTS5 extension.

Installing and Loading

The fts extension will be transparently autoloaded on first use from the official extension repository. If you would like to install and load it manually, run:

INSTALL fts;
LOAD fts;

Usage

The extension adds two PRAGMA statements to DuckDB: one to create, and one to drop an index. Additionally, a scalar macro stem is added, which is used internally by the extension. Each index schema also contains search macros and an analyze_text table macro configured for that index.

PRAGMA create_fts_index

create_fts_index(input_table, input_id, *input_values, stemmer = 'porter',
                 tokenizer = 'regex',
                 stopwords = 'english',
                 ignore = "[0-9!@#$%^&*()_+={}\\[\\]:;<>,.?~\\\\/\\|''\"`-]+",
                 strip_accents = 1, lower = 1, overwrite = 0,
                 incremental = 0, cluster_terms = 0, layered_search = 0)

PRAGMA that creates a FTS index for the specified table.

This PRAGMA builds the index under a newly created schema. The schema will be named after the input table: if an index is created on table 'main.table_name', then the schema will be named 'fts_main_table_name'.

By default, indexes are static snapshots. If the input table changes after index creation, rebuild the index with overwrite = true or use incremental = true when creating the index. Incremental indexes are maintained with triggers for INSERT and DELETE statements. They require trigger support, a document id column declared NOT NULL or PRIMARY KEY, and unique document id values. Persistent databases must use storage version v2.0.0 or newer for incremental indexes.

cluster_terms = true changes only the physical ordering of the generated terms table. For incremental indexes, the initial index build uses the clustered layout, but trigger-appended rows are not globally reclustered.

PRAGMA drop_fts_index

drop_fts_index(input_table)

Drops a FTS index for the specified table. This removes the generated FTS schema and any triggers used for incremental maintenance. Recreating an index with overwrite = true performs the same cleanup before building the new index.

Analyzer Inspection

Every generated FTS schema exposes the analyzer used by that index as a table macro:

SELECT *
FROM fts_main_documents.analyze_text('The running foxes')
ORDER BY position;

The result contains the normalized unstemmed raw_term, final indexed term, one-based absolute position, position_increment, position_length, start_offset, end_offset, and token_type. Empty tokenizer output is removed before positions are assigned. Stopwords are removed afterward, so leading and internal stopwords remain visible as position gaps.

Current analyzers use a position length of one and the token type word. Offsets are reserved as nullable, half-open, zero-based UTF-8 byte offsets into the original input. They are currently NULL because normalization prevents the regex and OpenSearch-compatible tokenizers from mapping every token reliably back to the original string.

analyze_text uses the same generated analyzer definition as bulk indexing, incremental maintenance, and query analysis. The lower-level list-returning tokenize macro remains available for compatibility, but it does not apply stopword removal or stemming. Analyzer contract changes are versioned in index_metadata and require dropping and recreating an existing index.

match_bm25 Function

match_bm25(input_id, query_string, fields := NULL, k := 1.2, b := 0.75,
           conjunctive := 0, field_weights := NULL, field_b := NULL,
           scoring_model := 'bm25f', tie_breaker := 0.0)

When an index is built, this retrieval macro is created that can be used to search the index.

BM25F is the default for both single-field and multi-field indexes. It normalizes term frequency independently for each selected field, combines those frequencies using field_weights, and applies BM25 saturation once. A single-field index reduces to ordinary BM25. Existing multi-field indexes may produce different scores and ordering after rebuilding with this version.

Layered BM25 Search

search_layered_bm25(query_string, fields := NULL, top_k := 50, k := 1.2,
                    b := 0.75, term_limit := 32, max_df_ratio := 0.15,
                    max_df := 50000, enable_prefix := true,
                    enable_substring := true, enable_fuzzy := true,
                    enable_short_fuzzy := true, expand_exact_terms := false,
                    query_mode := 'standard', field_weights := NULL,
                    field_b := NULL, scoring_model := 'bm25f',
                    tie_breaker := 0.0, near_distance := 10)

match_layered_bm25(input_id, query_string, fields := NULL, k := 1.2,
                   b := 0.75, term_limit := 32, max_df_ratio := 0.15,
                   max_df := 50000, enable_prefix := true,
                   enable_substring := true, enable_fuzzy := true,
                   enable_short_fuzzy := true, expand_exact_terms := false,
                   query_mode := 'standard', field_weights := NULL,
                   field_b := NULL, scoring_model := 'bm25f',
                   tie_breaker := 0.0, near_distance := 10)

When layered_search is enabled, the extension builds dictionary sidecar tables used to expand query terms before scoring over the standard FTS terms table. These sidecar tables grow with the term dictionary rather than with the document corpus.

search_layered_bm25 is a table macro returning docname, score, and rank. match_layered_bm25 is the scalar form for use against an input table row. Both macros use the same tokenization, stopword removal, stemming, field filtering, and BM25 parameters as the base FTS index.

Exact terms are always included in the candidate set. Prefix, substring, and fuzzy alternatives are optional and receive lower expansion weights before BM25 scoring. Numeric query terms are searched exactly and are excluded from trigram/fuzzy expansion. Stopwords are removed before both exact matching and expansion, so a query containing only stopwords returns no rows.

In bm25f mode, each field term frequency is divided by its field-specific length normalization. The weighted normalized frequencies are summed into a pseudo-frequency before BM25 saturation is applied once. In best_fields mode, each field is saturated and scored separately, then combined as max(field_score) + tie_breaker * (sum(field_score) - max(field_score)). IDF remains corpus-wide for both models. Unknown fields and invalid models, weights, normalization values, or tie breakers produce an error. A nonzero tie breaker is only valid with best_fields.

Autocomplete mode requires a final searchable token of at least two characters. It routes that token through a compact two/three-character prefix table and then verifies the full raw-token prefix. The existing postings table stores a raw-token identifier alongside each stemmed term, so autocomplete remains correct when multiple raw forms share a stem. It does not use substring or fuzzy expansion for the final token.

Phrase mode uses positional postings to require zero-slop matches in one indexed field. Positions are assigned after empty tokenizer output is removed and before stopword removal, so removed stopwords retain their positional gap. Phrase-prefix mode applies the same positional check but expands only the final unfinished raw token through the prefix sidecar. term_limit bounds those deterministically ordered completions; document-frequency filters and fuzzy or substring expansion are not applied. A one-token phrase uses standard mode, while a one-token phrase-prefix uses autocomplete mode.

Near mode uses the same positional postings to require that every query term occurs in one indexed field, in any order, with at most near_distance tokens between the first and the last of them. The distance is counted once across the whole match rather than for each pair, and intervening query terms count toward it, so two terms need near_distance = 0 to be adjacent while three consecutive terms need 1. As in phrase mode, removed stopwords retain their positional gap, so a stopword between two terms consumes distance. A term repeated in the query counts once, and quoted phrases are not supported; the query is a set of distinct terms. Near mode matches exact dictionary terms, so prefix, fuzzy, and substring expansion are not applied, and a term that occurs in no document disqualifies every document. near_distance is NEAR N from SQLite's FTS5 and shares its default of 10.

Wildcard and regex modes treat the complete query_string as one whole-token pattern and match it verbatim against the normalized raw-term dictionary. Wildcard syntax supports *, ?, and backslash escaping. Regex syntax is a flat RE2 subset containing literals, escaped non-alphanumeric literals, ., character classes, and *, +, ?, or bounded repetition quantifiers. Groups, alternation, anchors, inline flags, and character-class escapes are rejected. The bounded native pattern analyzer validates this grammar and extracts one mandatory literal. Candidate generation, dictionary verification, field deduplication, and scoring remain relational SQL operations.

An indexable pattern must contain either an unquantified leading literal prefix of at least two characters or another mandatory literal run of at least three characters. Leading prefixes use the existing prefix sidecar; internal literals intersect the normalized-term trigram index plus a sparse, deduplicated sidecar for raw forms not covered by it. Candidates are then verified with regexp_full_match, so sidecar lookup cannot introduce false matches. Pattern modes ignore term_limit and document-frequency expansion limits.

Pattern clauses use constant scoring: every matching document field contributes once, regardless of token frequency or the number of matching raw terms. BM25F sums matching field weights. best_fields applies its normal tie-breaker formula to those field weights. BM25 length normalization and IDF do not apply to pattern clauses. Because pattern text is not passed through the index's text analyzer at query time, callers must use the normalized spelling; for example, a lowercase index does not lowercase an uppercase wildcard pattern automatically.

Structured Boolean queries evaluate all pattern leaves together through the same indexed paths, carrying each leaf's node ID through candidate generation and scoring. Ordinary leaves continue to use the non-pattern layered core, so an unused pattern branch does not add a correlated search plan per leaf.

Layered search can be static or incremental. With layered_search = true and incremental = false, the sidecar tables are built once and later table changes are not visible until the index is rebuilt. With both options enabled, the sidecar tables are maintained together with the base FTS index for INSERT and DELETE.

Indexes store a compact field-length list on each document row and corpus average lengths in the statistics row. Layered indexes additionally store a one-based position on every posting. The index_metadata view records the physical format version, enabled features, indexed fields, and analyzer fingerprint. The fingerprint includes a canonical digest of the effective stopword set; the separate stopwords column retains its configured source for provenance. Existing indexes created by an older extension version must be dropped and rebuilt before field-aware or positional scoring can be used.

Structured Boolean Layered Search

search_layered_bm25_query(query, top_k := 50, k := 1.2, b := 0.75,
                          term_limit := 32, max_df_ratio := 0.15,
                          max_df := 50000, enable_prefix := true,
                          enable_substring := true, enable_fuzzy := true,
                          enable_short_fuzzy := true,
                          expand_exact_terms := false,
                          field_weights := NULL, field_b := NULL,
                          scoring_model := 'bm25f', tie_breaker := 0.0,
                          max_leaf_clauses := 1024,
                          max_boolean_depth := 64)

match_layered_bm25_query(input_id, query, k := 1.2, b := 0.75,
                         term_limit := 32, max_df_ratio := 0.15,
                         max_df := 50000, enable_prefix := true,
                         enable_substring := true, enable_fuzzy := true,
                         enable_short_fuzzy := true,
                         expand_exact_terms := false,
                         field_weights := NULL, field_b := NULL,
                         scoring_model := 'bm25f', tie_breaker := 0.0,
                         max_leaf_clauses := 1024,
                         max_boolean_depth := 64)

These macros accept a JSON query tree. Load DuckDB's json extension before creating a layered index to install the structured macros together with the index. For a layered index that already exists, or one created before json was loaded, load json and run PRAGMA create_fts_boolean_query_macros('table_name'); this installs only the macros and does not rebuild the index. A node is either a leaf with query or a Boolean group containing must, should, and must_not arrays:

LOAD json;

SELECT docname, score, rank
FROM fts_main_animal_sounds.search_layered_bm25_query(
    '{
        "must": [
            {"query": "quack", "fields": ["text_content"]}
        ],
        "should": [
            {
                "query": "han",
                "fields": ["author"],
                "query_mode": "autocomplete",
                "boost": 2.0
            }
        ],
        "must_not": [
            {"query": "archive"}
        ],
        "minimum_should_match": 0
    }'::JSON,
    top_k := 10
);

Every must clause must match and any matching must_not clause excludes the document. A should-only group requires one matching clause by default; a group with at least one must clause defaults to zero. An explicit minimum_should_match counts direct should children. Positive child scores are added and then multiplied by the group's optional boost. Leaf boosts are applied to the leaf BM25 score first.

Leaves can select indexed fields with a JSON string array and choose standard, autocomplete, phrase, phrase_prefix, near, wildcard, or regex query mode independently. A near leaf takes its own optional near_distance, so one clause can require proximity while another does not. Expansion controls and field scoring configuration remain macro-level settings so all leaves share the same resource limits and field model. scoring_model := 'bm25f' uses canonical BM25F with optional field_weights and per-field length normalization through field_b; best_fields selects the strongest field and optionally incorporates the others through tie_breaker. Ordinary leaves reuse the non-pattern layered core, while wildcard and regex leaves share one set-oriented indexed pattern pipeline. The Boolean layer combines the resulting scores over the same layered index.

By default, query trees are limited to 1,024 leaves and a maximum Boolean depth of 64. Callers can lower or raise these resource limits with max_leaf_clauses and max_boolean_depth; passing NULL disables the corresponding extension-level limit. The Boolean tree is evaluated recursively, so these are resource controls rather than fixed SQL-plan limits. Empty groups, pure-negative groups, unknown keys, mixed leaf/group nodes, invalid boosts, and out-of-range minimum_should_match values are rejected. Setting minimum_should_match explicitly to zero on a should-only group matches all indexed documents; documents that match no optional leaf receive score 0.

stem Function

stem(input_string, stemmer)

Reduces words to their base. Used internally by the extension.

Example Usage

Create a table and fill it with text data:

CREATE TABLE documents (
    document_identifier VARCHAR,
    text_content VARCHAR,
    author VARCHAR,
    doc_version INTEGER
);
INSERT INTO documents
    VALUES ('doc1',
            'The mallard is a dabbling duck that breeds throughout the temperate.',
            'Hannes Mühleisen',
            3),
           ('doc2',
            'The cat is a domestic species of small carnivorous mammal.',
            'Laurens Kuiper',
            2
           );

Build the index, and make both the text_content and author columns searchable.

PRAGMA create_fts_index(
    'documents', 'document_identifier', 'text_content', 'author'
);

Search the author field index for documents that are authored by Muhleisen. This retrieves doc1:

SELECT document_identifier, text_content, score
FROM (
    SELECT *, fts_main_documents.match_bm25(
        document_identifier,
        'Muhleisen',
        fields := 'author'
    ) AS score
    FROM documents
) sq
WHERE score IS NOT NULL
  AND doc_version > 2
ORDER BY score DESC;

Search for documents about small cats. This retrieves doc2:

SELECT document_identifier, text_content, score
FROM (
    SELECT *, fts_main_documents.match_bm25(
        document_identifier,
        'small cats'
    ) AS score
    FROM documents
) sq
WHERE score IS NOT NULL
ORDER BY score DESC;

Build an incremental index when inserts and deletes should update the index automatically. The document identifier must be non-null and unique:

CREATE TABLE live_documents (
    document_identifier VARCHAR NOT NULL,
    text_content VARCHAR
);

INSERT INTO live_documents
    VALUES ('doc1', 'quacking quacking'),
           ('doc2', 'barking barking');

PRAGMA create_fts_index(
    'live_documents',
    'document_identifier',
    'text_content',
    incremental = true
);

INSERT INTO live_documents VALUES ('doc3', 'meowing');

SELECT document_identifier
FROM (
    SELECT *, fts_main_live_documents.match_bm25(
        document_identifier,
        'meowing'
    ) AS score
    FROM live_documents
) sq
WHERE score IS NOT NULL;

Use layered search when query terms should match exact terms plus prefix, substring, or typo-tolerant alternatives:

CREATE TABLE animal_sounds (
    document_identifier VARCHAR NOT NULL,
    text_content VARCHAR,
    author VARCHAR
);

INSERT INTO animal_sounds
    VALUES ('doc1', 'quacking quacking', 'Hannes'),
           ('doc2', 'barking barking', 'Mark'),
           ('doc3', 'meowing meowing', 'Laurens');

PRAGMA create_fts_index(
    'animal_sounds',
    'document_identifier',
    'text_content',
    'author',
    stemmer = 'none',
    stopwords = 'none',
    layered_search = true,
    incremental = true
);

SELECT docname, score, rank
FROM fts_main_animal_sounds.search_layered_bm25(
    'quack',
    fields := 'text_content',
    top_k := 10
);

Weight fields independently, or prefer the strongest matching field while retaining a fraction of the remaining field scores:

SELECT docname, score, rank
FROM fts_main_animal_sounds.search_layered_bm25(
    'mark',
    field_weights := MAP {
        'author': 4.0,
        'text_content': 1.0
    },
    field_b := MAP {'author': 0.3, 'text_content': 0.8},
    scoring_model := 'best_fields',
    tie_breaker := 0.1,
    top_k := 10
);

The scalar layered helper can be used in the same row-filtering pattern as match_bm25:

SELECT document_identifier, text_content, score
FROM (
    SELECT *, fts_main_animal_sounds.match_layered_bm25(
        document_identifier,
        'mark',
        fields := 'author',
        enable_short_fuzzy := false
    ) AS score
    FROM animal_sounds
) sq
WHERE score IS NOT NULL
ORDER BY score DESC;

By default, exact dictionary matches are not further expanded. Set expand_exact_terms := true to include alternatives for exact query terms:

SELECT docname
FROM fts_main_animal_sounds.search_layered_bm25(
    'mark',
    fields := 'author',
    expand_exact_terms := true
);

For low-latency term-prefix search, use autocomplete mode. Earlier tokens are matched exactly after the configured stemming, while only the final token is treated as a raw prefix:

SELECT docname, score, rank
FROM fts_main_animal_sounds.search_layered_bm25(
    'han quac',
    fields := 'text_content,author',
    query_mode := 'autocomplete',
    top_k := 10
);

Use phrase mode for exact adjacency and phrase-prefix mode when the final token is still being typed:

SELECT docname, score, rank
FROM fts_main_animal_sounds.search_layered_bm25(
    'quacking quacking',
    fields := 'text_content',
    query_mode := 'phrase',
    top_k := 10
);

SELECT docname, score, rank
FROM fts_main_animal_sounds.search_layered_bm25(
    'quacking quac',
    fields := 'text_content',
    query_mode := 'phrase_prefix',
    top_k := 10
);

Warning Without incremental = true, the FTS index is a static snapshot and will not update automatically when the input table changes. Static layered indexes behave the same way: their sidecar tables are also rebuilt only when the index is rebuilt. With incremental = true, the index and layered sidecar are maintained for INSERT and DELETE statements on tables that support triggers.

Stemmers

The extension bundles the Snowball stemming library (vendored under third_party/snowball/).

Starting with the upgrade to Snowball v3, the previously available 'german2' and 'kraaij_pohlmann' stemmers (legacy variants that were undocumented but accepted by the underlying library) have been removed upstream. If you relied on them, rebuild the FTS index with one of the documented stemmers instead.