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

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. What the VSS Extension Actually Is 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. Setting It Up 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. Querying with the Index 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; Distance Metrics 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. Tuning the Index 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. Persistence: The Part You Need to Read Carefully 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. Inserts, Updates, Deletes The index supports mutation after creation, with two practical notes: - It is faster to build the index after bulk-loading data, since the initial build parallelizes better than incremental inserts. - Deletes are lazy: rows are marked deleted rather than removed from the graph immediately, which causes gradual quality and performance degradation. To reclaim this, run: PRAGMA hnsw_compact_index('my_hnsw_index'); or periodically drop and recreate the index if the table sees heavy churn. Bonus: Fuzzy Joins with 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. Limitations to Keep in Mind - Only 32-bit FLOAT vectors are supported today - noDOUBLE , no quantized/int8 vectors. - The index is not buffer-managed and must fit entirely in RAM. - Index memory does not count against DuckDB's memory_limit setting, so it is easy to overshoot available memory without warning. - Persistent indexes require the experimental flag discussed above. - vss_join /vss_match never use the index, regardless of whether one exists on the underlying columns. When This Makes Sense vss is a strong fit when: - You are building a local-first or embedded RAG system (for example, a FastAPI service backed by DuckDB and a local GGUF model) and do not want to run a separate vector database. - Your embedding volume is modest enough to fit in memory, and you are comfortable with in-memory or experimental-persistence trade-offs. - You want vector search to live in the same SQL surface as your relational and analytical queries - joining structured metadata filters with 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. Summary 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. Top comments (0)