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, 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.