Vector Similarity Search with DuckDB: A Practical Guide to the VSS Extension

DuckDB's official vss extension brings HNSW-based approximate nearest neighbor search directly to its SQL engine, allowing developers to run vector similarity queries without a separate vector database. The extension supports multiple distance metrics, index hyperparameters, and integrates with DuckDB's native ARRAY type, making it a lightweight option for RAG pipelines and analytics workloads.

Most people reach for a dedicated vector database — Pinecone, Qdrant, Milvus, pgvector — the moment a project needs embedding search. But if you are already using DuckDB for analytics or building a lightweight RAG pipeline, there is a good chance you do not need another moving part. DuckDB ships an official vss extension that adds HNSW-based approximate nearest neighbor search directly on top of its native ARRAY type. This article walks through what the extension does, how to use it, and where its limits are. vss is an experimental core extension that adds indexing support to accelerate similarity search over DuckDB's fixed-size ARRAY columns. It implements HNSW Hierarchical Navigable Small Worlds , the same graph-based ANN algorithm used by most production vector search engines. In practice, this means you can store embeddings as a normal column, build an index on it, and run ORDER BY ... LIMIT queries that DuckDB will automatically route through the index instead of a full scan. Because it is embedded, there is no separate service to run, no network hop, and no extra infrastructure to operate — the vector index lives in the same process as the rest of your SQL engine. Installation follows DuckDB's usual extension pattern: INSTALL vss; LOAD vss; Then create a table with a fixed-size ARRAY column and build an HNSW index on it: CREATE TABLE my vector table vec FLOAT 3 ; INSERT INTO my vector table SELECT array value a, b, c FROM range 1, 10 ra a , range 1, 10 rb b , range 1, 10 rc c ; CREATE INDEX my hnsw index ON my vector table USING HNSW vec ; Note the dimensionality FLOAT 3 here must be fixed at table-creation time — DuckDB's ARRAY type is size-constrained, unlike the variable-length LIST type. Once the index exists, DuckDB will use it automatically whenever a query orders by a supported distance function against a constant vector and limits the result set: SELECT FROM my vector table ORDER BY array distance vec, 1, 2, 3 ::FLOAT 3 LIMIT 3; You can confirm the index is actually being used by checking the query plan for an HNSW INDEX SCAN node: EXPLAIN SELECT FROM my vector table ORDER BY array distance vec, 1, 2, 3 ::FLOAT 3 LIMIT 3; For one-shot nearest-neighbor lookups, the overloaded min by col, arg, n aggregate is also index-accelerated when arg matches a supported distance function, and it conveniently returns the full matched row as a struct: SELECT min by my vector table, array distance vec, 1, 2, 3 ::FLOAT 3 , 3 ORDER BY vec AS result FROM my vector table; By default, HNSW indexes use l2sq squared Euclidean distance , matching array distance . You can choose a different metric at index-creation time: CREATE INDEX my hnsw cosine index ON my vector table USING HNSW vec WITH metric = 'cosine' ; | Metric | Function | Description | |---|---|---| l2sq | array distance | Euclidean distance | cosine | array cosine distance | Cosine similarity distance | ip | array negative inner product | Negative inner product | For most embedding models used in RAG or semantic search OpenAI, Sentence-Transformers, etc. , cosine similarity is the natural choice. You can also build multiple indexes on the same column with different metrics, or index multiple columns independently — each HNSW index applies to exactly one column. Index quality and search speed are controlled by a handful of hyperparameters, all familiar to anyone who has tuned HNSW before: | Option | Default | Effect | |---|---|---| ef construction | 128 | Candidate vertices considered while building the index. Higher = more accurate, slower build. | ef search | 64 | Candidate vertices considered per query. Higher = more accurate, slower search. | M | 16 | Max neighbors per graph vertex. Higher = more accurate, slower build. | M0 | 2 × M | Base connectivity at the zero-th graph level. | ef search can also be overridden per connection at runtime without rebuilding the index: SET hnsw ef search = 128; -- ...run queries... RESET hnsw ef search; This is useful when you want to dial accuracy up or down depending on the query, without paying the cost of a full reindex. This is the biggest practical caveat. By default, HNSW indexes can only be created on in-memory databases. If you want the index to persist in a disk-backed .duckdb file, you must explicitly opt in: SET hnsw enable experimental persistence = true; It is locked behind this flag because WAL write-ahead log recovery is not yet fully implemented for custom extension indexes. If DuckDB crashes or is killed while there are uncommitted changes to an HNSW-indexed table, the index can end up corrupted or lose data. The docs are explicit that this is not recommended for production use. If you do enable it and hit an unexpected shutdown, recovery is possible by starting DuckDB separately, loading vss , and then ATTACH ing the database file before letting WAL replay run — this makes the HNSW functionality available during recovery. When persistence is enabled, the entire index is serialized to disk on every checkpoint no incremental updates and deserialized back into memory on the next access after restart — which is still generally faster than dropping and rebuilding it from scratch. For a local RAG prototype or an in-memory analytical session, this is a non-issue. For anything that needs durable, crash-safe vector storage in production, treat this as a hard constraint and plan accordingly — or keep the source-of-truth embeddings elsewhere and rebuild the index on startup. The index supports mutation after creation, with two practical notes: To reclaim this, run: PRAGMA hnsw compact index 'my hnsw index' ; or periodically drop and recreate the index if the table sees heavy churn. vss join and vss match Beyond single-query nearest-neighbor search, the extension ships two table macros for matching two sets of vectors against each other — useful for deduplication, entity resolution, or batch retrieval: CREATE TABLE haystack id INT, vec FLOAT 3 ; CREATE TABLE needle search vec FLOAT 3 ; INSERT INTO haystack SELECT row number OVER , array value a, b, c FROM range 1, 10 ra a , range 1, 10 rb b , range 1, 10 rc c ; INSERT INTO needle VALUES 5, 5, 5 , 1, 1, 1 ; SELECT FROM vss join needle, haystack, search vec, vec, 3 res; vss match offers the same brute-force k-NN matching but as a lateral join, grouping results per left-table row: SELECT FROM needle, vss match haystack, search vec, vec, 3 res; Important: neither macro uses the HNSW index — they perform brute-force search. They are convenience utilities for correctness, not performance, though the docs note they may become index-accelerated in the future. FLOAT vectors are supported today — no DOUBLE , no quantized/int8 vectors. memory limit setting, so it is easy to overshoot available memory without warning. vss join / vss match never use the index, regardless of whether one exists on the underlying columns. vss is a strong fit when: ORDER BY array cosine distance ... in a single statement, without shipping data to another system.It is a weaker fit if you need durable, crash-safe indexes at scale in a multi-writer production environment — for that, a dedicated vector database or an extension like pgvector on a durable RDBMS is currently the safer choice. The vss extension turns DuckDB into a capable, embedded ANN engine: INSTALL / LOAD the extension, store embeddings in a fixed-size ARRAY column, CREATE INDEX ... USING HNSW , and query with ORDER BY array distance ... LIMIT k . It supports L2, cosine, and inner-product metrics, exposes the usual HNSW tuning knobs, and even offers brute-force join macros for batch matching. The one thing to plan around carefully is persistence — it is opt-in and explicitly experimental, so treat durability as something you design for rather than assume.