Vector Search in DuckDB

As an example. It’s free and cheap to explore. There are a plethora of VectorDBs out there.

Remember that you need to create embeddings for each one of your data rows. You will compare a query against these vectorizations/embeddings to look for similarity.

Creating Embeddings and Index

So if you have a table notes where the text column contains the actual text, you would (a) create an embedding column and (b) create an index on it like we discussed (the ANN-HNSW one, tho you can try others.)

$$\texttt{notes.text} \rightarrow \text{embedding model} \rightarrow \texttt{notes.embedding}$$

-- Fixed-length array. Using the OpenAI model that has
-- 1,536 dimensions.
ALTER TABLE notes ADD COLUMN embedding FLOAT[1536];

-- Optional but highly recommended: Which model made the vector.
-- Vectors from different models live in completely different spaces
-- and **cannot be compared**. The query vector must come from the
-- same model as your stored vectors!
ALTER TABLE notes ADD COLUMN embed_model TEXT;

INSTALL vss; LOAD vss;
CREATE INDEX notes_hnsw ON notes USING HNSW (embedding) WITH (metric = 'cosine');

Apples and Pineapples!

Worth noting again: Every model builds its own space with its own dimensions. A vector from one model compared to a vector from another is meaningless. Like comparing GPS coordinates to street addresses.

Sample python to load data

from openai import OpenAI

client = OpenAI()
rows = con.sql("SELECT note_id, text FROM notes").fetchall()
resp = client.embeddings.create(
    model="text-embedding-3-small",
    input=[t for _, t in rows],
)

# One vector per input, already length 1
for (note_id, _), item in zip(rows, resp.data):
    con.execute(
        "UPDATE notes SET embedding = ?, embed_model = ? WHERE note_id = ?",
        [item.embedding, "text-embedding-3-small", note_id],
    )

DuckDB has no built-in embedding function, but extensions add one (flock and quackformers, both experimental; or MotherDuck, which is hosted DuckDB):

INSTALL flock FROM community; LOAD flock;
CREATE SECRET (TYPE OPENAI, API_KEY '...');  -- keeps the key out of the query
SELECT note_id,
       llm_embedding({'model_name': 'text-embedding-3-small'},
                     {'context_columns': [{'data': text}]}) AS embedding
FROM notes;

Querying by Similarity

Built-in functions: array_cosine_similarity, array_distance (Euclidean), and array_inner_product.

The index only kicks in for queries shaped like ORDER BY <distance function> LIMIT k, and the metric must match the function you use (a 'cosine' index for cosine queries).

BM25 Keyword Search in DuckDB

INSTALL fts; LOAD fts;
PRAGMA create_fts_index('notes', 'note_id', 'text');  -- tokenize, stem, drop stop words
SELECT note_id, fts_main_notes.match_bm25(note_id, 'penicillin allergy') AS bm25
FROM notes WHERE bm25 IS NOT NULL ORDER BY bm25 DESC;

Sample results based on our Notes example.

note	bm25	text
N4	0.60	No known drug allergies. Denies penicillin allergy.
N3	0.51	Hives after amoxicillin in 2019. Penicillin allergy documented.
N5	0.47	Mother has a penicillin allergy; patient tolerates amoxicillin.
N6	0.47	Rule out penicillin allergy; skin testing scheduled.

The wrong note (N4, a denial) comes out on top. It’s short, and stemming (chopping words to their root) likely turns both “allergies” and “allergy” into the same root, so it matches “allergy” twice. N9 doesn’t show up at all.

Hybrid Search: Reciprocal Rank Fusion (RRF)

Keyword and vector search fail in different ways, so combine them. Problem: a BM25 score of 0.60 and a cosine of 0.99 are on totally different scales. It’s like adding a test score out of 100 to a mile time in minutes.

Solution: ignore the scores and use the ranks (1st, 2nd, 3rd…). Ranks are always comparable.

$$\text{RRF score} = \frac{1}{60 + \text{keyword rank}} + \frac{1}{60 + \text{vector rank}}$$

The 60 is a standard constant that keeps one first-place finish from dominating. If a note doesn’t show up in one list, that term is 0.

WITH kw  AS (SELECT note_id, rank() OVER (ORDER BY bm25 DESC) AS r FROM keyword_hits),
     vec AS (SELECT note_id, rank() OVER (ORDER BY sim DESC)  AS r FROM vector_hits)
SELECT note_id, kw.r, vec.r,
       coalesce(1 / (60 + kw.r), 0) + 1 / (60 + vec.r) AS rrf
FROM vec LEFT JOIN kw USING (note_id)
ORDER BY rrf DESC;

Here are the results for the Notes:

note	keyword rank	vector rank	RRF
N3	2	1	$\frac{1}{62} + \frac{1}{61} = 0.0325$
N4	1	4	$\frac{1}{61} + \frac{1}{64} = 0.0320$
N6	3	3	$\frac{1}{63} + \frac{1}{63} = 0.0317$
N9	—	2	$0 + \frac{1}{62} = 0.0161$

N3 wins because it does well on both lists.

Same Idea in Other Systems

System	Vector column	Nearest 3 to $q
DuckDB + vss	FLOAT[384]	ORDER BY array_cosine_distance(embedding, $q) LIMIT 3
PostgreSQL + pgvector	vector(384)	ORDER BY embedding <=> $q LIMIT 3
BigQuery	ARRAY<FLOAT64>	VECTOR_SEARCH(TABLE notes, 'embedding', …, top_k => 3)
turbopuffer	a vector field per document	ns.query(rank_by=("vector", "ANN", q), limit=3)

In PostgreSQL with the pgvector extension, <=> is cosine distance, <-> is Euclidean distance, <#> is negative inner product.

A dedicated vector database (like turbopuffer) stores vectors and some metadata. Joining with the rest of your tables happens in your own code, not in SQL.