DuckDBVectorSearch
DuckDB Vector Search is the practice of storing embeddings alongside source documents in DuckDB and performing vector similarity search using SQL, without a dedicated vector database. The technique uses DuckDB's native FLOAT[N] array column type, the array_inner_product function for cosine/inner-product similarity, and the optional vss extension for HNSW approximate nearest-neighbour indexing. It was documented in a June 2024 article by Sebastian Brunk demonstrating end-to-end embedding storage, UDF-based model integration, and similarity search against a Wikipedia corpus.
Embeddings are stored as array columns in a standard DuckDB table alongside document content:
CREATE TABLE embeddings (doc_id INTEGER, embedding FLOAT[768]);A local embedding model is registered as a DuckDB UDF, making embed($text) callable directly in SQL. Similarity search is then a single query:
WITH top_k AS (
FROM embeddings
SELECT * ORDER BY array_inner_product(embedding, embed($q)) LIMIT 5
)
FROM top_k JOIN documents ON documents.id = top_k.doc_id
SELECT title, array_inner_product(embedding, embed($q)) AS similarity
ORDER BY similarity DESC;For larger corpora, the vss extension adds an HNSW index that accelerates approximate nearest-neighbour lookup:
INSTALL vss; LOAD vss;
CREATE INDEX emb_idx ON embeddings USING HNSW (embedding) WITH (metric = 'ip');DuckDB's native Parquet integration allows loading source data directly from local files or remote datasets (including HuggingFace Hub) without a separate ingestion step.
As of DuckDB 1.0, the HNSW index cannot be combined with a WHERE filter clause in the same query — top-K retrieval must be a CTE, with filtering applied to the results. HNSW persistence for on-disk databases is experimental; indexes should be recreated on load or used in-memory for production until the extension stabilises.
Placed in Techniques / Assess / inner.
The primary value is infrastructure elimination. RAG pipelines that use a dedicated vector database (Qdrant, Chroma, pgvector, Weaviate) introduce an additional service to deploy, operate, and keep in sync with the document store. DuckDB Vector Search collapses the document store and vector index into a single .db file, reducing the operational surface to zero for small-to-medium corpora.
The technique is directly composable with other blips on this radar: Ollama (local embedding model as the UDF backend), open-parse (PDF chunking before embedding), and RAG Chunking Strategies (chunk size affects embedding quality). Together they form a complete local RAG stack with no external services.
Inner position reflects pip install duckdb as the only dependency, no infrastructure provisioning, and direct applicability to any Python project building a RAG pipeline that doesn't yet justify a dedicated vector DB.
Trial gate: a working RAG pipeline where DuckDB stores both the source chunks and their embeddings, with vector similarity search driving retrieval for at least one query set, and results evaluated against a baseline chunking strategy.
