Executive summary

BM25 remains a first-class retrieval algorithm in 2026 rather than a historical curiosity. Apache Lucene still exposes BM25 as a core similarity with defaults k1 = 1.2 and b = 0.75, while current Elasticsearch documentation continues to identify BM25 as its default similarity. [Lucene BM25Similarity] [Elasticsearch similarity documentation]

SQLite FTS5 is similarly alive and maintained. It first appeared experimentally in SQLite 3.8.11 on 27 July 2015 and entered the amalgamation with SQLite 3.9.0 on 14 October 2015. Subsequent releases added features such as configurable index detail, trigram indexes, new contentless modes, token-data support and locale-aware tokenizer APIs. [SQLite release history] [Official FTS5 documentation]

The key architectural recommendation is therefore not BM25 versus vectors. For most serious 2026 systems it is exact metadata lookup + BM25 candidate retrieval + optional dense retrieval + optional reranking. This preserves BM25’s excellent identifier and terminology recall while adding semantic matching only where it genuinely contributes.

History and evolution

Who invented BM25?

It is more accurate to describe BM25 as the product of a research lineage than to credit it to one inventor. Its foundations lie in the probabilistic relevance work of Stephen E. Robertson and Karen Spärck Jones. Their 1976 paper, Relevance Weighting of Search Terms, formalised probabilistic term weighting using relevance information. [Robertson & Spärck Jones, 1976]

The weighting function now recognised as BM25 emerged from the Okapi information-retrieval research programme at City University, London. The landmark public description normally cited for BM25 is Okapi at TREC-3 by Stephen E. Robertson, Steve Walker, Susan Jones, Micheline Hancock-Beaulieu and Mike Gatford, presented in the Third Text REtrieval Conference proceedings in 1994. NIST’s TREC-3 proceedings list the paper at page 109, and modern Lucene documentation explicitly traces its BM25 implementation to that work. [NIST TREC-3 proceedings] [Lucene historical citation]

Robertson and Hugo Zaragoza’s 2009 monograph The Probabilistic Relevance Framework: BM25 and Beyond is the definitive retrospective treatment of the model’s theoretical lineage. It makes clear why modern BM25 is best understood as a practical scoring function developed from the probabilistic relevance framework rather than as a single closed-form theorem discovered at one instant. [Robertson & Zaragoza, 2009]

Who invented FTS5?

There is no corresponding research paper naming a single “inventor of FTS5”. FTS5 is an engineering component of the SQLite project. SQLite’s canonical release history records FTS5 at the project level rather than assigning the module to a sole individual. It was introduced as an experimental extension in SQLite 3.8.11 on 27 July 2015, then included in the amalgamation in SQLite 3.9.0 on 14 October 2015. [SQLite release history]

This distinction matters when writing historical material: D. Richard Hipp created SQLite itself, but SQLite’s official FTS5 documentation and release history do not designate a single person as the inventor of FTS5. Likewise, there is no IETF-style “FTS5 RFC”. The authoritative specification is effectively the combination of the FTS5 manual, SQLite release notes and the canonical source tree.

Timeline

timeline
    title BM25 and SQLite FTS5 development
    1976 : Robertson and Spärck Jones publish probabilistic relevance weighting
    1980s-1990s : Okapi retrieval research develops Best Match weighting schemes
    1994 : Okapi at TREC-3 documents the BM25 lineage
    2009 : Robertson and Zaragoza publish BM25 and Beyond
    2015 : SQLite 3.8.11 introduces experimental FTS5
         : SQLite 3.9.0 adds FTS5 to the amalgamation
    2016 : FTS5 detail option is added
    2020 : FTS5 gains trigram indexes
    2023 : secure-delete and contentless-delete capabilities evolve
    2024 : tokendata and locale-aware tokenizer APIs appear
    2026 : SQLite 3.53.4 is current on 10 September
         : BM25 remains Elasticsearch's default similarity

FTS5’s significant evolution after 2015

FTS5 was not frozen after its introduction. SQLite 3.34.0, released 1 December 2020, added trigram indexes, enabling indexed substring-oriented search patterns. SQLite 3.45.0, released 15 January 2024, added the tokendata option. The 21 October 2024 release added fts5_tokenizer_v2, locale-aware tokenisation through locale=1, and additional contentless-table functionality. FTS5 also continued receiving correctness, robustness and diagnostic fixes through the 2025–2026 SQLite release series. [SQLite release history]

BM25 from probabilistic relevance to a practical scoring function

The retrieval problem BM25 tries to solve

Given a query Q and document D, a ranking function needs to estimate which matching documents deserve to appear first. Raw term counts are insufficient: ten occurrences of a term do not normally make a document ten times as useful as one occurrence, ubiquitous words carry less evidence than rare words, and long documents have more opportunities to contain a query term merely by chance.

BM25 addresses these three effects with three interacting pieces: inverse document frequency, saturating within-document term frequency, and document-length normalisation. It remains a bag-of-words lexical model at heart: unless the query system adds phrase/proximity constraints separately, BM25 itself does not understand semantic equivalence or word order.

Probabilistic starting point: relevance odds

In the classical probabilistic relevance framework, documents can be ranked by their odds of relevance. For a term t, let p be the probability that a relevant document contains the term and u the probability that a non-relevant document contains it. The term’s log-odds contribution is:

With relevance judgements, define N as the number of documents, R as the number known relevant, n as the number containing term t, and r as the number of relevant documents containing it. The Robertson–Spärck Jones weight with 0.5 corrections is:

In the common first-pass case where no relevance judgements are available, setting R = r = 0 reduces the collection component to:

This is the classic probabilistic IDF form. It is important to understand that the rest of BM25 is not an inevitable algebraic consequence of this expression. BM25’s term-frequency and length components are practical modelling extensions rooted in the probabilistic/Okapi research programme. [Robertson & Zaragoza]

The modern BM25 equation

A widely used form for query terms q₁ … q_m is:

Here f_i is the frequency of query term q_i in the document, |D| is document length in tokens and avgdl is the collection’s average document length. k₁ controls term-frequency saturation and b controls the strength of document-length normalisation. Lucene’s current API describes these roles exactly and defaults to k1=1.2, b=0.75. [Lucene BM25Similarity]

Term-frequency saturation derived step by step

Define a length-dependent constant:

The term-frequency component becomes:

This is a rectangular-hyperbola-like saturation curve. Its first derivative is:

and the second derivative is:

For positive K, the first derivative is positive and the second is negative: additional occurrences always help, but each additional occurrence helps less than the previous one. As f → ∞:

This is BM25’s central defence against keyword stuffing within a document. Repeating a matching word indefinitely cannot make its frequency contribution grow without bound.

At average document length, |D|/avgdl = 1, so K = k₁. Consequently, when f = 1:

With k1=1.2, an average-length document therefore receives a frequency multiplier of exactly 1 for one occurrence, approximately 1.375 for two occurrences, approximately 1.774 for five occurrences, and can never exceed 2.2 regardless of repetition.

What k1 actually does

k1 sets the shape of the saturation curve. Lower values make BM25 approach its ceiling faster. At the limiting case k1 = 0, every positive term frequency receives essentially the same within-document factor: occurrence becomes almost binary presence. Higher k1 allows repeated occurrences to continue affecting the score for longer.

There is therefore no universal “optimal” k1. Short metadata fields often need little reward for repetition, whereas longer natural-language passages can sometimes benefit from slower saturation. Defaults around 1.2 are strong starting points, not laws of nature.

What b actually does

The b parameter scales length normalisation:

At b=0, document length disappears from the denominator. At b=1, length is normalised fully in proportion to |D|/avgdl. For a document longer than average, increasing b increases the denominator and suppresses its term-frequency contribution. For a shorter-than-average document, the reverse occurs.

Length normalisation is particularly consequential when an index mixes radically different kinds of content. A 12-token title and a 5,000-token abstract/article body should usually not be treated as one undifferentiated field if field-specific ranking behaviour is important.

IDF variants: they are not interchangeable

That final detail is subtle because SQLite’s prose documentation presents the classical expression, while the current source code explicitly guards against negative IDF. In ext/fts5/fts5_aux.c, SQLite calculates log((nRow-nHit+0.5)/(nHit+0.5)) and replaces any result less than or equal to zero with 1e-6. [SQLite FTS5 source]

Lucene takes a different route: log(1 + (docCount-docFreq+0.5)/(docFreq+0.5)). Consequently, “BM25 score” is not a portable absolute quantity. Two engines can both correctly describe themselves as BM25 implementations while producing different numeric scores and, occasionally, different orderings. [Lucene IDF implementation]

A complete numerical example

Suppose:

The classical RSJ IDF is:

The length-adjusted denominator constant is:

The TF factor is:

So this term contributes approximately:

A multi-term query simply adds corresponding contributions, subject to the engine’s exact IDF convention, query parsing, boosting and any additional ranking stages.

Query term frequency and the k3 factor

Historical BM25 formulations sometimes include an additional query-side saturation factor:

Modern search queries are often short enough that explicit query-term frequency is not useful, and major implementations commonly omit this factor. Neither Lucene’s standard BM25Similarity API nor SQLite FTS5’s built-in bm25() exposes a k3 parameter.

BM25 scoring flow

SQLite FTS5 internals: tokenisation, inverted indexes, phrases and ranking

FTS5 is an inverted-index virtual table

FTS5 is a SQLite virtual-table module for full-text search. At the conceptual level it converts text into tokens and builds a mapping from each token to the places where that token occurs. Instead of scanning every document for every query, FTS5 looks up the token’s postings and works from the much smaller candidate set. [SQLite FTS5 documentation]

The shadow-table architecture

An FTS5 virtual table is backed by SQLite “shadow tables”. Depending on its configuration, these include structures conventionally named %_data, %_idx, %_config, %_docsize and %_content. The index itself is stored primarily as compact binary records, not as a normal one-row-per-posting SQL table. [FTS5 data structures]

Segment-based index maintenance

Internally, FTS5 maintains its searchable index as a series of segment B-tree-like structures. Writes can introduce new segments rather than rewriting one enormous monolithic postings tree immediately. Segments are subsequently merged according to FTS5’s merge policies. This is usefully thought of as LSM-like behaviour, although FTS5’s format and merge machinery are its own. [FTS5 index format]

This design makes index updates practical, but it introduces the familiar trade-off between write cost and read amplification: many small outstanding segments can make searches do more merging work. FTS5 therefore exposes maintenance controls such as automerge, merge and optimize. [FTS5 special INSERT commands]

Tokenisers

The tokenizer determines what the search engine considers a token. Tokenisation is not a cosmetic preprocessing choice: it changes document frequency, positions, phrase semantics, index size and therefore ranking.

FTS5 also exposes APIs for registering custom tokenizers. Recent SQLite versions extended these APIs with locale-aware tokenizer support. [FTS5 tokenizer documentation] [SQLite release history]

Prefix indexes

A normal FTS5 term lookup can go directly to a complete token. A query such as robert*, however, potentially represents a range of dictionary terms. FTS5 can therefore create separate prefix indexes of configured lengths. For example:

CREATE VIRTUAL TABLE papers_fts USING fts5(
    title,
    authors,
    abstract,
    prefix='2 3 4'
);

This is a classic space-versus-speed trade-off: prefix indexes consume more index space and add work during ingestion, but can avoid dictionary range scans for common prefix lengths. [FTS5 prefix indexes]

Phrase and proximity queries

With the default detail=full, postings contain positional information. A phrase such as "probabilistic relevance" can therefore be evaluated by finding rows containing both tokens and confirming that their token positions are adjacent in the required order.

FTS5 additionally supports prefix tokens, column filters, Boolean combinations and NEAR(...) expressions. [FTS5 query syntax]

-- Exact phrase
SELECT rowid, title
FROM papers_fts
WHERE papers_fts MATCH '"probabilistic relevance"';

-- Prefix
SELECT rowid, title
FROM papers_fts
WHERE papers_fts MATCH 'robert*';

-- Column-specific phrase
SELECT rowid, title
FROM papers_fts
WHERE papers_fts MATCH 'title : "information retrieval"';

-- Boolean
SELECT rowid, title
FROM papers_fts
WHERE papers_fts MATCH 'bm25 AND sqlite NOT postgres';

-- Proximity
SELECT rowid, title
FROM papers_fts
WHERE papers_fts MATCH 'NEAR("dense retrieval" benchmark, 8)';

The detail option and positional information

For a bibliographic library where quoted titles and names matter, detail=full is normally worth keeping unless storage pressure is severe. [FTS5 detail option]

How FTS5 implements BM25

FTS5’s built-in bm25() is not just “roughly BM25”. Its source contains a recognisable implementation of the familiar formula, with hard-coded:

However, there are several SQLite-specific details that matter in production.

First, FTS5 decomposes the query into phrases. Its implementation computes document frequency and phrase frequency at the phrase level. A quoted multi-token phrase is therefore not necessarily scored as though each constituent token were an independent query term. [FTS5 bm25()] [FTS5 bm25 source]

Second, FTS5 floors non-positive IDF to 1e-6. This keeps very common phrases from contributing a negative weight. [Source implementation]

Third, FTS5 negates the conventional score. In most BM25 descriptions, larger positive values mean better matches. FTS5 multiplies the result by -1 so that the best result is numerically smallest, which makes natural ascending SQL ordering convenient:

SELECT rowid, title, bm25(papers_fts) AS score
FROM papers_fts
WHERE papers_fts MATCH :query
ORDER BY score
LIMIT 20;

A highly relevant FTS5 match may therefore have a score such as -12.7, while a weaker match might have -3.1. Do not mistakenly use DESC. [FTS5 scoring order]

Column weighting

Additional arguments to bm25() are column weights, not alternate values of k1 and b. For a four-column table:

SELECT
    rowid,
    title,
    bm25(papers_fts, 8.0, 5.0, 1.0, 10.0) AS score
FROM papers_fts
WHERE papers_fts MATCH :query
ORDER BY score
LIMIT 20;

This might mean title weight 8, author weight 5, abstract weight 1 and identifier-text weight 10. Internally, FTS5 forms the phrase frequency as a weighted sum of occurrences in columns before applying its BM25 saturation. The document-length component remains based on the total row token count. [FTS5 column weights]

The hidden rank column

Every FTS5 table has a hidden rank column. During a full-text query its default value corresponds to bm25(table). SQLite’s documentation specifically notes that sorting by rank may be faster than explicitly sorting by bm25(), particularly when result consumption is abandoned early or a LIMIT is present. [FTS5 rank column]

SELECT rowid, title, rank
FROM papers_fts
WHERE papers_fts MATCH :query
ORDER BY rank
LIMIT 20;

A weighted configuration can be attached to the rank expression:

SELECT rowid, title, rank
FROM papers_fts
WHERE papers_fts MATCH :query
  AND rank MATCH 'bm25(8.0, 5.0, 1.0, 10.0)'
ORDER BY rank
LIMIT 20;

Custom ranking functions

FTS5 exposes a public extension API that can register custom auxiliary functions through fts5_api.xCreateFunction(). Such functions receive an Fts5ExtensionApi context that can inspect row counts, token counts, query phrases, positions and matching instances. [Custom FTS5 functions]

This is the route to a genuinely custom BM25 variant — for example, alternative k1/b, recency priors, per-field length models or an entirely different lexical score. The built-in bm25() does not let SQL callers override its hard-coded 1.2/0.75.

Python’s ordinary sqlite3.Connection.create_function() can register standard SQL scalar functions, but it does not by itself provide the FTS5 extension context required to inspect postings and phrase instances. For a true FTS5 auxiliary ranker, use a compiled SQLite extension or perform a second-stage rerank in Python.

FTS5 architecture from ingestion to ranking

Practical implementation for a local bibliographic library

Use exact metadata and full-text retrieval together

A reference library should not place DOI, ISBN and other canonical identifiers exclusively inside the full-text index. Store their normalised forms in ordinary indexed SQLite columns and use FTS5 for the parts that genuinely require retrieval ranking: titles, authors, abstracts, keywords and citation text.

A robust base schema is:

PRAGMA foreign_keys = ON;
PRAGMA journal_mode = WAL;

CREATE TABLE works (
    id          INTEGER PRIMARY KEY,
    title       TEXT NOT NULL,
    authors     TEXT NOT NULL DEFAULT '',
    abstract    TEXT NOT NULL DEFAULT '',
    identifiers TEXT NOT NULL DEFAULT '',
    year        INTEGER,
    doi_norm    TEXT,
    isbn13      TEXT,
    source      TEXT,
    UNIQUE (doi_norm)
);

CREATE INDEX works_isbn13_idx ON works(isbn13);
CREATE INDEX works_year_idx   ON works(year);

CREATE VIRTUAL TABLE works_fts USING fts5(
    title,
    authors,
    abstract,
    identifiers,
    content='works',
    content_rowid='id',
    tokenize='unicode61 remove_diacritics 2',
    prefix='2 3 4',
    detail=full,
    columnsize=1
);

This uses an external-content FTS5 table. The authoritative text lives once in works; FTS5 stores its index rather than maintaining a second content copy. [External-content tables]

Keep the external-content index synchronised

SQLite explicitly warns that applications are responsible for keeping an external-content FTS index consistent with its content table. Triggers are a conventional solution:

CREATE TRIGGER works_ai AFTER INSERT ON works BEGIN
  INSERT INTO works_fts(
      rowid, title, authors, abstract, identifiers
  )
  VALUES (
      new.id, new.title, new.authors, new.abstract, new.identifiers
  );
END;

CREATE TRIGGER works_ad AFTER DELETE ON works BEGIN
  INSERT INTO works_fts(
      works_fts, rowid, title, authors, abstract, identifiers
  )
  VALUES (
      'delete', old.id, old.title, old.authors, old.abstract, old.identifiers
  );
END;

CREATE TRIGGER works_au AFTER UPDATE ON works BEGIN
  INSERT INTO works_fts(
      works_fts, rowid, title, authors, abstract, identifiers
  )
  VALUES (
      'delete', old.id, old.title, old.authors, old.abstract, old.identifiers
  );

  INSERT INTO works_fts(
      rowid, title, authors, abstract, identifiers
  )
  VALUES (
      new.id, new.title, new.authors, new.abstract, new.identifiers
  );
END;

For an existing populated table, build or repair the external index with:

INSERT INTO works_fts(works_fts) VALUES('rebuild');

Weighted bibliographic search

Bibliographic relevance usually deserves stronger weights for title, authors and identifiers than for an abstract. A starting configuration might be:

SELECT
    w.id,
    w.title,
    w.authors,
    w.year,
    w.doi_norm,
    works_fts.rank AS fts_score
FROM works_fts
JOIN works AS w ON w.id = works_fts.rowid
WHERE works_fts MATCH :query
  AND works_fts.rank MATCH 'bm25(8.0, 5.0, 1.0, 10.0)'
ORDER BY works_fts.rank
LIMIT 25;

Those weights are a starting hypothesis, not universal optimum values. Tune them against a judgement set drawn from real reference-search queries.

Exact identifier lookup before ranked retrieval

-- DOI: normalise before insertion and query.
SELECT id, title, authors, year, doi_norm
FROM works
WHERE doi_norm = :doi
LIMIT 1;

-- ISBN-13:
SELECT id, title, authors, year, isbn13
FROM works
WHERE isbn13 = :isbn13
LIMIT 1;

This is both faster and semantically stronger than asking BM25 whether one identifier is “more relevant” than another. An identifier is normally an equality predicate, not a fuzzy relevance signal.

Snippets and highlighting

SELECT
    w.id,
    w.title,
    snippet(
      works_fts,
      2,              -- abstract column
      '<mark>',
      '</mark>',
      ' … ',
      24
    ) AS context,
    works_fts.rank
FROM works_fts
JOIN works AS w ON w.id = works_fts.rowid
WHERE works_fts MATCH :query
ORDER BY works_fts.rank
LIMIT 10;

FTS5’s snippet() and highlight() functions use match information to produce result context. [FTS5 snippet()]

Inspecting the vocabulary with fts5vocab

fts5vocab is invaluable for diagnosing tokenisation, document-frequency surprises and search behaviour: [fts5vocab documentation]

CREATE VIRTUAL TABLE works_vocab
USING fts5vocab(works_fts, 'row');

SELECT term, doc, cnt
FROM works_vocab
WHERE term IN ('bm25', 'sqlite', 'retrieval')
ORDER BY term;

Here doc tells you how many rows contain a token, which is precisely the type of collection statistic that governs IDF.

Python integration

from __future__ import annotations

import re
import sqlite3
from dataclasses import dataclass
from pathlib import Path


@dataclass(frozen=True)
class SearchHit:
    work_id: int
    title: str
    authors: str
    year: int | None
    doi: str | None
    score: float


def normalise_doi(value: str) -> str:
    value = value.strip().lower()
    value = re.sub(r"^https?://(?:dx\.)?doi\.org/", "", value)
    value = re.sub(r"^doi:\s*", "", value)
    return value.strip()


class LibrarySearch:
    def __init__(self, path: str | Path) -> None:
        self.conn = sqlite3.connect(path)
        self.conn.row_factory = sqlite3.Row

        # Useful for read-heavy local applications.
        self.conn.execute("PRAGMA foreign_keys = ON")
        self.conn.execute("PRAGMA journal_mode = WAL")

        try:
            self.conn.execute(
                "CREATE VIRTUAL TABLE IF NOT EXISTS __fts5_test "
                "USING fts5(value)"
            )
            self.conn.execute("DROP TABLE __fts5_test")
        except sqlite3.OperationalError as exc:
            raise RuntimeError(
                "This SQLite build does not provide FTS5"
            ) from exc

    def by_doi(self, doi: str) -> SearchHit | None:
        row = self.conn.execute(
            """
            SELECT id, title, authors, year, doi_norm
            FROM works
            WHERE doi_norm = ?
            LIMIT 1
            """,
            (normalise_doi(doi),),
        ).fetchone()

        if row is None:
            return None

        return SearchHit(
            work_id=row["id"],
            title=row["title"],
            authors=row["authors"],
            year=row["year"],
            doi=row["doi_norm"],
            score=0.0,
        )

    def search(self, query: str, limit: int = 20) -> list[SearchHit]:
        if not query.strip():
            return []

        rows = self.conn.execute(
            """
            SELECT
                w.id,
                w.title,
                w.authors,
                w.year,
                w.doi_norm,
                works_fts.rank AS score
            FROM works_fts
            JOIN works AS w
              ON w.id = works_fts.rowid
            WHERE works_fts MATCH ?
              AND works_fts.rank MATCH
                  'bm25(8.0, 5.0, 1.0, 10.0)'
            ORDER BY works_fts.rank
            LIMIT ?
            """,
            (query, limit),
        ).fetchall()

        return [
            SearchHit(
                work_id=row["id"],
                title=row["title"],
                authors=row["authors"],
                year=row["year"],
                doi=row["doi_norm"],
                score=row["score"],
            )
            for row in rows
        ]

    def optimise(self) -> None:
        self.conn.execute(
            "INSERT INTO works_fts(works_fts) VALUES('optimize')"
        )
        self.conn.commit()

The SQL value is parameterised, which protects the SQL statement itself. Remember, however, that a parameter supplied to MATCH is still parsed as an FTS5 query language. If your interface promises literal text rather than Boolean/phrase syntax, implement an explicit query-escaping policy rather than silently exposing operators.

Bulk loading

Bulk ingestion should normally happen inside an explicit transaction rather than one commit per document:

with conn:
    conn.executemany(
        """
        INSERT INTO works(
            title, authors, abstract, identifiers,
            year, doi_norm, isbn13, source
        )
        VALUES (?, ?, ?, ?, ?, ?, ?, ?)
        """,
        records,
    )

# After a large one-off load:
conn.execute(
    "INSERT INTO works_fts(works_fts) VALUES('optimize')"
)
conn.commit()

Whether optimize is worth running after every batch depends on update frequency and latency requirements. It is better viewed as controlled index maintenance than as a compulsory per-write operation.

Contentless indexes

Where every indexed string can be regenerated or is stored elsewhere, contentless modes can reduce duplication. They come with behavioural restrictions, so they should be selected intentionally rather than merely because “smaller is better”. [Contentless FTS5 tables]

Changing the default rank mapping

FTS5 permits persistent rank configuration. For example:

INSERT INTO works_fts(works_fts, rank)
VALUES('rank', 'bm25(8.0, 5.0, 1.0, 10.0)');

After this, queries using the hidden rank column can use the configured weighting without repeating the expression each time. [FTS5 rank configuration]

Performance, accuracy and scalability

Why inverted indexes are fast

A naive full-text query examines every token of every document. An inverted index changes the shape of the problem: it first locates the postings lists for query terms, then works primarily with documents that contain those terms.

For a selective term, lookup cost is dominated by locating its dictionary entry plus traversing its postings. A conjunctive query can intersect postings lists, usually beginning with selective evidence. Phrase search requires additional positional checks after candidate rows and shared term occurrences are identified.

Exact asymptotic notation alone is not a useful latency forecast because compression, cache locality, page size, storage medium, term distribution, phrase frequency, segment count and result ranking all matter. A five-million document index containing short titles behaves very differently from five million books containing complete OCR text.

What determines index size?

SQLite concurrency is a separate scaling dimension

FTS5’s search algorithm may scale comfortably for a read-heavy local library long before SQLite’s application-level concurrency model becomes relevant. But the two concerns should not be confused. SQLite supports many concurrent readers, while database writes are serialised. WAL mode improves reader/writer coexistence but does not transform SQLite into a distributed multi-writer search cluster. [SQLite WAL documentation] [When to use SQLite]

For a desktop reference manager, local knowledge base or personal research library this is frequently an advantage: there is no service to operate. For a web-scale ingestion system with many simultaneous writers, replication, sharding and high availability requirements, the operational problem is different even if BM25 remains an appropriate ranking function.

Ranking cost and candidate count

A term that occurs in 30 documents is inexpensive to score compared with one that occurs in three million. Stop-word-like query phrases also have tiny IDF and poor selectivity. Good query design therefore affects both ranking effectiveness and work performed.

The hidden rank column is worth preferring when ordering FTS5 results because SQLite documents an execution advantage over a direct ORDER BY bm25(table) in cases where not all candidate rows need to be consumed. [FTS5 rank sorting]

Measuring retrieval quality

Performance in milliseconds is only half the evaluation. A search engine that returns an irrelevant answer instantly is still poor. Build a judgement set with real queries and record which library records are relevant.

Recommended experimental procedure

For reference checking, divide test queries into at least three behavioural groups: known-item queries containing title/author fragments; identifier queries containing DOI/ISBN-style strings; and semantic topic queries. Tune lexical column weights on one subset and report final metrics on a held-out subset. This prevents a deceptively good overall score caused by overfitting the exact queries used to choose weights.

Library-size guidance

The following size boundaries are engineering starting points, not SQLite hard limits or benchmark results. Actual feasibility depends far more on bytes per record, text length, update rate, SSD performance, concurrency and query distribution.

BM25 and FTS5 in the 2026 retrieval landscape

BM25 has not been displaced by neural retrieval

Neural retrieval changed the state of the art, but it did not make lexical retrieval obsolete. The BEIR benchmark evaluated lexical, sparse neural, dense, late-interaction and reranking architectures across heterogeneous retrieval datasets and described BM25 as a robust baseline. Its authors found that reranking and late-interaction approaches achieved the strongest average zero-shot effectiveness among the evaluated systems, but at higher computational cost. [BEIR, Thakur et al.]

The continuing industrial role is also observable directly in current search software: Elasticsearch still documents BM25 as its default similarity, and Lucene continues to expose a first-class BM25Similarity implementation. [Elasticsearch] [Lucene]

Dense retrieval

Dense retrieval embeds queries and documents into continuous vector spaces, then searches for nearby representations. Dense Passage Retrieval (DPR) was an influential demonstration: on the open-domain question-answering datasets used in that paper, its learned dual encoder improved top-20 passage retrieval accuracy by 9–19 percentage points over the strong Lucene-BM25 baseline used by the authors. [Dense Passage Retrieval]

That result should not be universalised into “dense always beats BM25”. Dense models depend on training distribution, embedding model quality, chunking and similarity-index settings. They can excel when a query and relevant document use different vocabulary; they can also struggle with exact identifiers, rare names, numerical strings and newly coined terms that lexical search preserves naturally.

Learned sparse retrieval

Systems such as SPLADE retain sparse representations compatible with inverted-index ideas while using neural models to learn term importance and expansion. SPLADE v2 reported substantial effectiveness improvements, including more than a 9% nDCG@10 gain on TREC DL 2019 over its compared predecessor configuration, while retaining desirable sparse-retrieval properties. [SPLADE v2]

Learned sparse retrieval is conceptually attractive for applications that want lexical interpretability and inverted-index execution while mitigating vocabulary mismatch. The trade-off is a model-dependent indexing pipeline and potentially expanded postings.

Late interaction

ColBERT-style retrieval keeps token-level vector representations instead of collapsing an entire passage into one vector. ColBERTv2 combines this late-interaction architecture with residual compression and reported a 6–10× reduction in its late-interaction space footprint compared with the prior approach while maintaining strong retrieval quality across evaluated benchmarks. [ColBERTv2]

The result is a useful middle point between one-vector-per-document dense search and a fully expensive cross-encoder: richer interactions are available during retrieval, but the index and serving path are substantially more complex than FTS5.

Rerankers

A reranker changes the economics of neural relevance modelling. Instead of scoring every document with an expensive model, BM25 or a dense retriever selects perhaps 50–500 candidates. A cross-encoder or sequence-to-sequence ranker then applies richer query-document reasoning only to those candidates.

This multi-stage pattern is especially compelling for a local research library: lexical candidate generation can remain instantaneous and deterministic, while expensive semantic reasoning is bounded by a small result set.

Hybrid retrieval

Hybrid systems run lexical and semantic retrieval in parallel and fuse their rankings. Reciprocal Rank Fusion (RRF) is popular because it combines ranks without requiring raw BM25 scores and cosine similarities to share a numeric scale.

Raw-score addition is usually a poor default because BM25 scores and vector similarities have unrelated distributions. Rank fusion side-steps that scale mismatch.

from collections import defaultdict


def reciprocal_rank_fusion(
    rankings: list[list[int]],
    k: int = 60,
) -> list[tuple[int, float]]:
    """
    rankings: one ordered document-id list per retriever.
    Returns documents ordered by fused RRF score.
    """
    scores: dict[int, float] = defaultdict(float)

    for ranking in rankings:
        for rank, doc_id in enumerate(ranking, start=1):
            scores[doc_id] += 1.0 / (k + rank)

    return sorted(
        scores.items(),
        key=lambda item: item[1],
        reverse=True,
    )

SQLite-local hybrid retrieval is an active 2026 direction

Two very recent 2026 preprints are notable specifically because they combine SQLite FTS5 with vector retrieval rather than replacing it. The scrydb paper, posted in August 2026, describes lexical, semantic and hybrid retrieval in SQLite using FTS5 together with sqlite-vec. vstash, posted earlier in 2026, likewise studies a local-first FTS5/vector/RRF architecture. [scrydb preprint, 2026] [vstash preprint, 2026]

These are recent preprints rather than grounds for claiming a settled production standard. Their relevance here is architectural: current research continues to explore FTS5 plus semantic retrieval, which is evidence against the simplistic notion that lexical retrieval has become obsolete.

Pros and cons of BM25/FTS5 in 2026

Recommended architectures, migration strategies and final assessment

Best design for checking references in a local library

For the specific problem of checking references against a local library, the strongest architecture is layered. Retrieval and verification should be separate operations.

This approach exploits what each component is best at. A DOI equality match is stronger evidence than any relevance score. A title-and-author BM25 match is highly discriminative for damaged or incomplete citations. Dense retrieval is valuable when a user remembers what a paper was about but not what it was called.

A staged migration path

Hybrid candidate generation example

def hybrid_search(query: str, limit: int = 20):
    # First-stage lexical retrieval.
    lexical = fts_search(query, limit=100)

    # Independent semantic retrieval.
    query_vector = embed(query)
    semantic = vector_search(query_vector, limit=100)

    lexical_ids = [hit.id for hit in lexical]
    semantic_ids = [hit.id for hit in semantic]

    fused = reciprocal_rank_fusion(
        [lexical_ids, semantic_ids],
        k=60,
    )

    candidate_ids = [doc_id for doc_id, _ in fused[:50]]

    # Optional expensive second stage.
    reranked = cross_encoder_rerank(
        query=query,
        document_ids=candidate_ids,
    )

    return reranked[:limit]

The key engineering property is that each stage has a bounded job: FTS5 and the vector index maximise recall cheaply; fusion protects candidates that only one retriever sees; the expensive reranker examines only a small set.

Configuration by size and budget

“No stated budget constraint” does not mean “use the most complex system”. Complexity itself has a cost. The no-constraint recommendations below assume retrieval quality justifies the additional engineering and compute.

Recommended FTS5 defaults for reference data

The numeric field weights in this table are intentionally presented as starting values, not empirical universal optima. Tune them using held-out relevance judgements from the target library.

When not to add vectors

Dense embeddings are not automatically an upgrade. Do not add them merely because they are modern. If nearly all user tasks are DOI lookup, title matching, author matching and precise technical terminology, vectors may add storage, model lifecycle management, approximate-nearest-neighbour tuning and harder-to-debug ranking without materially improving retrieval.

Before augmentation, inspect FTS5 failures. Many apparent “semantic search” problems are actually tokenisation, metadata, query parsing, spelling or weighting problems. Fixing the lexical layer is cheaper and improves the hybrid system too.

When vectors are justified

Add semantic retrieval when relevance judgements show genuine vocabulary mismatch. Typical examples are natural-language questions against terse paper titles, conceptual searches such as “papers about making retrieval robust to paraphrases”, or searching across translations and terminology shifts. A dense model can retrieve candidates whose words do not overlap the query at all — exactly the case BM25 cannot solve by itself.

When reranking is justified

Add a reranker when candidate recall is already high but ordering is the problem. A reranker cannot recover a document that neither BM25 nor the dense retriever supplied. This is why candidate-set recall should be measured before investing in a more expensive second stage.

Final assessment

BM25 remains one of the best baseline ranking functions available in 2026. Its longevity is not accidental. It encodes three durable pieces of retrieval evidence — rarity, within-document frequency with diminishing returns, and length normalisation — using a tiny amount of computation and corpus statistics. Its continuing presence as the default similarity in Elasticsearch and as a core Lucene class demonstrates that modern search infrastructure still treats BM25 as a production algorithm, not merely an academic baseline. [Elasticsearch] [Lucene]

SQLite FTS5 remains an unusually capable implementation choice when search belongs inside a local application. It supplies an inverted index, positional phrase/proximity matching, Unicode-oriented tokenisation, prefix and trigram options, snippets, built-in BM25 ranking, configurable column weights, vocabulary introspection and a C extension API without requiring a separate search daemon. [SQLite FTS5]

Its built-in ranking does have constraints: k1=1.2 and b=0.75 are fixed, IDF behaviour differs from Lucene, column weighting is not the same as BM25F, and an embedded SQLite database is not a substitute for distributed search infrastructure when the application actually needs that operational model.

For checking references in a local library, those constraints are rarely a reason to reject it. Exact identifiers should be normal SQL fields; FTS5 should search titles, authors, abstracts and citation strings; phrase support should remain enabled; and relevance should be evaluated with known-item and semantic query sets. Only after measuring lexical misses should semantic retrieval be added.

Primary and authoritative sources

Research note: recent 2026 preprints are clearly labelled as such and should not be treated as equivalent in evidential maturity to SQLite’s canonical documentation or the foundational peer-reviewed information-retrieval literature. Generic latency claims have deliberately been avoided because corpus composition, hardware, cache state, index configuration and workload can change FTS5 performance by orders of magnitude; benchmark the target library rather than extrapolating from unrelated systems.