Bytes, DuckDB, Hybrid search with FTS and VSS

What?

Piece	Gives you	How
`fts` extension	Keyword search ranked by BM25, with stemming and stopwords	`PRAGMA create_fts_index(...)`, then `match_bm25()`
`vss` extension	An HNSW approximate nearest-neighbour index over `FLOAT[n]` array columns	`CREATE INDEX ... USING HNSW`
Reciprocal rank fusion (RRF)	One ranking from both	A few lines of SQL

Both extensions are official. DuckDB downloads them on first INSTALL. Embeddings come from a local Ollama model (nomic-embed-text).

Why?

DuckDB is an analytical database: columnar, fast at scans and joins, and it reads Parquet, CSV and JSON in place. That makes it a good fit when search sits next to analysis. Examples: ranking passages and then grouping hits by source, joining search results to metadata in a Parquet file, or scoring an evaluation set in the same SQL.

Compared with SQLite:

	SQLite + FTS5 + sqlite-vec	DuckDB + fts + vss
Vector search	Brute force (exact)	HNSW index (approximate), or brute force without the index
Keyword index updates	Live, kept in step by triggers	A snapshot. Rebuild after changes
Stemming and stopwords	Opt-in tokenizer (`porter`), no stopwords	Built in: `porter` and `english` by default
Vectors live in	A separate virtual table	An ordinary `FLOAT[768]` column on the table
Best for	Apps, many small writes, embedding in a product	Analysis, batch-built indexes, joining to files

How?

1. Prerequisites

brew install uv ollama
ollama pull nomic-embed-text:v1.5

2. The script

Save as duckdb_search.py:

# /// script
# dependencies = ["duckdb==1.5.0"]
# ///
import json, sys, urllib.request
import duckdb
 
DOCS = [
    ("FR-012", "Retries", "A failed request is retried three times with exponential backoff, then the job is marked failed."),
    ("FR-013", "Timeouts", "Each request times out after 30 seconds."),
    ("FR-020", "Citations", "Every answer must cite the document it came from."),
    ("FR-021", "Search index", "The search index is never the source of record; it can be rebuilt from the documents."),
    ("FR-030", "Retrieval threshold", "Retrieval is not built until the corpus passes 200,000 tokens."),
    ("FR-031", "Stale records", "Withdrawn and superseded records stay in the archive but are excluded from answers."),
    ("FR-040", "Backups", "The database is backed up nightly and copies are kept for 30 days."),
    ("FR-041", "Logging", "Errors are logged with the request id so a failure can be traced."),
]
MODEL, DIMS = "nomic-embed-text:v1.5", 768
 
 
def embed(texts):
    req = urllib.request.Request(
        "http://localhost:11434/api/embed",
        data=json.dumps({"model": MODEL, "input": texts}).encode(),
        headers={"Content-Type": "application/json"},
    )
    return json.load(urllib.request.urlopen(req))["embeddings"]
 
 
db = duckdb.connect("search.duckdb")
for ext in ("fts", "vss"):
    db.install_extension(ext)
    db.load_extension(ext)
# HNSW indexes in a database file are still experimental (in-memory needs no flag)
db.execute("SET hnsw_enable_experimental_persistence = true")
 
db.execute(f"""
CREATE OR REPLACE TABLE docs (
  id INTEGER PRIMARY KEY, code VARCHAR, title VARCHAR, body VARCHAR,
  embedding FLOAT[{DIMS}]
)""")
vectors = embed(["search_document: " + f"{t}. {b}" for _, t, b in DOCS])
db.executemany(
    "INSERT INTO docs VALUES (?, ?, ?, ?, ?)",
    [(i, c, t, b, v) for i, ((c, t, b), v) in enumerate(zip(DOCS, vectors), start=1)],
)
 
# Keyword index. A snapshot: rebuild it (overwrite := 1) after the table changes.
# The default ignore pattern strips digits too, which would turn "FR-012" into "fr".
db.execute(r"""
PRAGMA create_fts_index('docs', 'id', 'code', 'title', 'body',
  stemmer = 'porter', stopwords = 'english',
  ignore = '(\.|[^a-z0-9])+', overwrite = 1)
""")
 
# Vector index: HNSW, kept up to date on insert, used for ORDER BY array_cosine_distance(...) LIMIT n
db.execute("CREATE INDEX docs_hnsw ON docs USING HNSW (embedding) WITH (metric = 'cosine')")
 
HYBRID = f"""
WITH
fts AS (
  SELECT id, row_number() OVER (ORDER BY score DESC) AS r
  FROM (SELECT id, fts_main_docs.match_bm25(id, $q) AS score FROM docs)
  WHERE score IS NOT NULL
  ORDER BY score DESC LIMIT $k
),
vec AS (
  SELECT id, row_number() OVER (ORDER BY dist) AS r
  FROM (SELECT id, array_cosine_distance(embedding, $v::FLOAT[{DIMS}]) AS dist
        FROM docs ORDER BY dist LIMIT $k)
)
SELECT d.code, d.title, fts.r AS bm25_rank, vec.r AS vec_rank,
       coalesce(1.0 / (60 + fts.r), 0) + coalesce(1.0 / (60 + vec.r), 0) AS rrf
FROM docs d
LEFT JOIN fts USING (id)
LEFT JOIN vec USING (id)
WHERE fts.id IS NOT NULL OR vec.id IS NOT NULL
ORDER BY rrf DESC
LIMIT 4
"""
 
for q in sys.argv[1:]:
    v = embed(["search_query: " + q])[0]
    print(f"\n{q!r}")
    print(f"  {'code':8} {'title':20} {'bm25':>4} {'vec':>4} {'rrf':>7}")
    for code, title, br, vr, s in db.execute(HYBRID, {"q": q, "v": v, "k": 10}).fetchall():
        print(f"  {code:8} {title:20} {br or '-':>4} {vr or '-':>4} {s:7.4f}")

The setup:

The embedding is just a column. FLOAT[768] is a fixed-size array, which is what both array_cosine_distance and the HNSW index need. A variable-length FLOAT[] list won’t do.
create_fts_index builds a schema called fts_main_docs holding the inverted index, with a match_bm25(id, query) macro that returns a score, or NULL for no match. Higher is better, the opposite of SQLite’s bm25().
The HNSW index is used automatically when a query has ORDER BY array_cosine_distance(embedding, <constant>) LIMIT n and the metric matches the index’s. Check with EXPLAIN: the plan shows HNSW_INDEX_SCAN.
The hybrid query has the same shape as the SQLite one: two ranked lists, numbered with row_number(), fused by $\sum \frac{1}{60 + rank} .$

3. Run it

uv run duckdb_search.py "FR-012" "how many attempts before giving up" "can the index be rebuilt"

'FR-012'
  code     title                bm25  vec     rrf
  FR-012   Retries                 1    2  0.0325
  FR-030   Retrieval threshold     7    1  0.0313
  FR-021   Search index            6    3  0.0310
  FR-013   Timeouts                3    6  0.0310
 
'how many attempts before giving up'
  code     title                bm25  vec     rrf
  FR-012   Retries                 -    1  0.0164
  FR-013   Timeouts                -    2  0.0161
  FR-030   Retrieval threshold     -    3  0.0159
  FR-040   Backups                 -    4  0.0156
 
'can the index be rebuilt'
  code     title                bm25  vec     rrf
  FR-021   Search index            1    1  0.0328
  FR-030   Retrieval threshold     -    2  0.0161
  FR-012   Retries                 -    3  0.0159
  FR-031   Stale records           -    4  0.0156

The top results match the SQLite version. One difference: for FR-012, BM25 matches every document. DuckDB’s match_bm25 has no phrase queries, so FR-012 becomes two separate terms, fr and 012. fr is in every code, so it adds almost nothing, but it still makes each document a match. 012 is rare, so FR-012 still ranks first. That’s IDF doing its job.

The stopwords that tripped up FTS5 (“up” in “giving up”) are filtered here by stopwords = 'english'.

4. Gotchas

The default ignore pattern deletes digits. It’s (\.|[^a-z])+, which turns FR-012 into just fr. Without the ignore override in the script, the same query ranks FR-012 last of eight:

default ignore: FR-013 0.029, FR-020 0.029, FR-040 0.026, ... FR-012 0.021

Pass ignore = '(\.|[^a-z0-9])+' for any corpus with ids, versions or numbers.

The FTS index doesn’t update. Rows inserted after create_fts_index are invisible to match_bm25 until you rerun it with overwrite = 1. Rebuild in batches, after loading, not after every write.

HNSW in a file is experimental. Without SET hnsw_enable_experimental_persistence = true, CREATE INDEX ... USING HNSW on a database file fails with “HNSW indexes can only be created in in-memory databases”. Once it’s on:

The index must fit in RAM. It isn’t paged like the rest of DuckDB.
Every checkpoint rewrites the whole index to disk, and a restart reads the whole thing back in.

That’s fine for an index you build once and can always rebuild from the table. Otherwise, run in memory, or skip the index and let ORDER BY array_cosine_distance(...) LIMIT n scan. A scan is exact and fast enough well past 100,000 rows.

HNSW is approximate. It can miss a true nearest neighbour. Tune with ef_search (SET hnsw_ef_search = ...) if recall matters more than speed, and measure with Recall@K against a brute-force run.

Stemming cuts both ways. Porter maps retried and retries to retri, which helps. It also merges unrelated words that share a stem.

Use the model’s prefixes. nomic-embed-text expects search_document: on indexed text and search_query: on questions.

Bytes, SQLite, Hybrid search with FTS5 and sqlite-vec: the same setup in SQLite
DuckDB
Algos, Information Retrieval, BM25: what match_bm25 computes
Algos, Information Retrieval, Hit@K, Algos, Information Retrieval, MRR: measuring whether hybrid helps
DuckDB full-text search extension, DuckDB vss extension