Do these 3 things before closing this tab:
1Fix the driver behind crashes, sound loss and screen glitches2Repair Windows errors before they cause bigger problems3Scan for outdated or missing drivers - takes under a minuteiTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
DuckDB’s VSS extension adds approximate nearest-neighbor search through HNSW indexes on fixed-size ARRAY columns. To use it, install and load the experimental extension, create an index on a FLOAT array column, and query with a supported distance function followed by a top-k limit. Whether that index is actually used depends on the query shape and DuckDB version, so check the plan and measure results on your data.
What DuckDB VSS does—and when it fits
The DuckDB Vector Similarity Search (VSS) extension adds HNSW indexing to accelerate eligible vector similarity queries over DuckDB’s fixed-size ARRAY type. The documented index path uses arrays with FLOAT elements. HNSW is an approximate nearest-neighbor method: it searches a graph to find likely close vectors rather than guaranteeing the same result as an exhaustive exact scan.
VSS is experimental, and its behavior and limitations can change. Match the documentation to your DuckDB release and confirm index selection with EXPLAIN. For a small dataset, an exact scan may be simpler; if exact ordering is essential, verify the result against an exact method. Which approach is appropriate depends on dataset size, acceptable recall, latency, memory, and update and recovery needs.
Free tools Windows power users keep installed
One-click scans. No signup required.
Install the extension and create an HNSW index
The following example uses three dimensions only to make the SQL concrete. Replace that dimension with the fixed size of the embeddings in your table.
#1 Best Overall
-
Install and load VSS in the DuckDB session:
INSTALL vss; LOAD vss; -
Create a table whose vector column is a fixed-size float array:
CREATE TABLE my_vector_table (vec FLOAT[3]); -
Create an HNSW index on that column:
CREATE INDEX my_hnsw_index ON my_vector_table USING HNSW (vec);
The index is defined on one indexed column. The default distance metric is l2sq. Choose a different supported metric at index creation if it matches how the embeddings and query should be compared.
Choose a metric that matches the query
The VSS documentation maps index metrics to DuckDB distance functions as follows:
| Metric | Distance function | Meaning for ordering |
|---|---|---|
l2sq (default) |
array_distance |
Euclidean/L2 distance as described by the extension; smaller distance ranks first. |
cosine |
array_cosine_distance |
Cosine distance; smaller distance ranks first. This is distance, not a similarity score where larger is better. |
ip |
array_negative_inner_product |
Negative inner product, used so nearest-neighbor ordering can be expressed as ascending distance. |
For example, create a cosine index with:
CREATE INDEX my_hnsw_cosine_index
ON my_vector_table
USING HNSW (vec)
WITH (metric = 'cosine');The metric selected for the index and the distance expression in the query need to correspond. Use the metric that reflects the intended ranking for your embedding model, rather than assuming cosine is always preferable.
Write a top-k query that can use the index
The documented HNSW query pattern orders rows by a supported distance function applied to the indexed vector and a constant query vector, then limits the number of results. With the cosine index above:
SELECT *
FROM my_vector_table
ORDER BY array_cosine_distance(vec, [0.1, 0.2, 0.3]::FLOAT[3])
LIMIT 10;Use the real embedding dimension and a type-compatible query vector in your application. An arbitrary expression or a different query shape is not guaranteed to use the HNSW index. Check the plan for the actual query and installed DuckDB version:
Rank #3
EXPLAIN
SELECT *
FROM my_vector_table
ORDER BY array_cosine_distance(vec, [0.1, 0.2, 0.3]::FLOAT[3])
LIMIT 10;DuckDB’s October 2024 VSS update reported optimizer improvements, additional distance functions, and support for optimizing eligible min_by(col, arg, n) top-k aggregates when the ordering argument is a matching distance function. It also described normalization of some cosine-similarity ordering expressions. These are version-sensitive behaviors: inspect the plan rather than relying on a rewrite described in that dated update. The same update cautioned that single-vector lookups can incur latency relative to other solutions; that statement is not a current comparative benchmark.
Tune HNSW by measuring your workload
DuckDB documents these HNSW controls and defaults in its current VSS documentation:
| Option | Documented default | What it controls |
|---|---|---|
ef_construction |
128 | Candidate count used while constructing the index; increasing it can improve accuracy at greater index-build cost. |
M |
16 | Graph connectivity; greater connectivity can improve accuracy with additional resource cost. |
M0 |
2 * M |
Connectivity control whose default is twice M. |
ef_search |
64 | Search-time candidate count; increasing it can improve accuracy while increasing query work. |
These are configuration defaults, not performance guarantees or recommended settings for every dataset. The runtime setting hnsw_ef_search can override the search-time value for a connection; for example:
SET hnsw_ef_search = 100;Choose settings by comparing recall or answer quality, query latency, index build time, and RAM use on representative vectors and queries. A higher search candidate count can trade speed for retrieval quality, while construction and connectivity choices affect index creation and resource demands. There is no universal setting that can be inferred without testing the target workload.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Plan for RAM and treat disk persistence as experimental
The index must fit in RAM. DuckDB documents that HNSW memory is not buffer-managed and does not count toward the configured DuckDB memory_limit; therefore, the database memory limit alone does not describe the total RAM an indexed workload may require.
Recommended Free Tools
By default, the current documentation says HNSW indexes can only be created on in-memory databases. Disk-backed persistence is opt-in through SET hnsw_enable_experimental_persistence = true, but the documentation warns against using this feature in production. Custom-index WAL recovery is not properly implemented, creating a risk of index or data loss or corruption after an unexpected shutdown with uncommitted changes. Index serialization occurs at checkpoint and serializes the full index rather than incrementally updating it; after a restart, the index loads into memory when accessed.
Enable the experimental persistence setting only when its recovery risks are acceptable for your use case, and do not treat it as a production durability guarantee. The May 2024 announcement also discussed the in-memory restriction and experimental persistence, but the current documentation is the appropriate source for present operating guidance.
Know which VSS features do not use HNSW
The vss_join and vss_match macros can simplify vector joins and fuzzy matching, but the current documentation says these macros do not require or use the HNSW index. Do not assume that a join or matching macro receives the acceleration of an indexed top-k query.
Before adopting VSS, consider the workload as a whole: supported query shape, recall requirements, latency, index build time, RAM footprint, update pattern, and persistence and recovery requirements. A separate vector-search system may be worth evaluating when those operational needs or dataset scale exceed what an experimental in-process index suits; the right choice is workload-specific.
The Tool Desk
Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
