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 minuteDuckDB’s VSS extension adds an experimental HNSW index for approximate nearest-neighbor search on fixed-size ARRAY columns. It can speed up eligible top-k distance queries, but it is not a general-purpose accelerator for every vector expression: the index must fit in RAM, query shape matters, and persistent indexes remain experimental. This guide shows how to create and query an index, choose a distance metric, and assess whether VSS fits your workload.
What DuckDB VSS does—and what it does not
The DuckDB VSS extension documentation describes VSS as experimental indexing support for vector similarity search using DuckDB’s fixed-size ARRAY type. Its index is based on HNSW, a graph-based approximate nearest-neighbor method.
As an Amazon Associate I earn from qualifying purchases.
The documented path is for fixed-dimension arrays with FLOAT elements. An HNSW index targets a particular query pattern: order rows by a supported distance function between the indexed column and a constant query vector, then limit the results to the nearest k. Other expressions or query shapes are not guaranteed to use the index. Confirm the plan on your DuckDB version and data rather than assuming that the presence of an index makes a query indexed.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
“Approximate” matters: HNSW trades exactness for search efficiency. Compare its recall and latency with an exact scan on representative data before deciding that the trade-off is acceptable.
#1 Best Overall
Install the extension and create an HNSW index
Install and load VSS in the DuckDB session, then create a table whose array dimension matches the embeddings you store. The dimension of three below is illustrative, not a recommended embedding size.
INSTALL vss;
LOAD vss;
CREATE TABLE my_vector_table (vec FLOAT[3]);
CREATE INDEX my_hnsw_index
ON my_vector_table
USING HNSW (vec);Use the actual fixed dimension of your vectors in both the table definition and query vectors. The documented HNSW path indexes one array column at a time. With no metric specified, the index uses l2sq.
Choose a metric that matches your embedding workflow
VSS documents three metric options. The SQL distance function in the ordering expression must match the metric used to build the index.
Recommended Free Tools
Rank #2
| Index metric | DuckDB distance function | How to interpret the ordering |
|---|---|---|
l2sq (default) |
array_distance |
Squared L2/Euclidean distance, as described by the VSS documentation; smaller distances rank first. |
cosine |
array_cosine_distance |
Cosine distance; smaller distances rank first. This function returns distance, not a similarity score. |
ip |
array_negative_inner_product |
Negative inner product; use the documented function as the ordering distance. |
For cosine distance, create an index explicitly with the matching metric:
CREATE INDEX my_hnsw_cosine_index
ON my_vector_table
USING HNSW (vec)
WITH (metric = 'cosine');Metric choice is part of the meaning of the search, not just a tuning switch. Use the distance semantics your embedding model and application expect; do not substitute a function simply because its name sounds similar.
Write an eligible top-k query and verify index use
A documented cosine-distance query orders by the indexed vector’s distance from a constant, dimension-compatible query vector and limits the output:
SELECT *
FROM my_vector_table
ORDER BY array_cosine_distance(vec, [0.1, 0.2, 0.3]::FLOAT[3])
LIMIT 10;Adapt the vector dimension and type to the table. For the default l2sq metric, use array_distance; for ip, use array_negative_inner_product. Keep the indexed column and query expression in the supported form. A transformed column, computed query vector, or different query structure may fall outside the documented index path.
Use EXPLAIN with the actual query to inspect the plan on your DuckDB release. DuckDB’s October 2024 VSS update reported optimizer improvements, additional distance functions, and optimization for some eligible min_by(col, arg, n) top-k aggregates when the ordering argument is a matching distance function. It also discussed normalization of some cosine-similarity ordering expressions. Those are dated project notes, not a guarantee for every current version or query; inspect the plan before relying on a rewrite. See DuckDB’s October 2024 VSS update.
Tune construction and search against your workload
DuckDB’s current VSS documentation lists these HNSW controls and defaults. They are configuration defaults, not performance benchmarks or universal recommendations.
| Option | Documented default | What it controls |
|---|---|---|
ef_construction |
128 | Candidate count considered during index construction. Increasing it can improve accuracy while increasing build time. |
ef_search |
64 | Candidate count considered during search. Increasing it can improve accuracy while increasing search time. |
M |
16 | Graph connectivity control for the index; higher connectivity can improve accuracy at additional cost. |
M0 |
2 * M |
Connectivity control with a default derived from M. |
The runtime setting hnsw_ef_search can override the search-time value for a connection:
SET hnsw_ef_search = 100;Evaluate settings with the same representative queries and data you expect in use. Measure recall or answer quality alongside query latency, index build time, and memory footprint. There is no single setting established here as best for all datasets.
Account for RAM and persistence constraints
Index memory is outside DuckDB’s configured limit
The HNSW index is not buffer-managed and must fit in RAM. Its memory is not counted against DuckDB’s configured memory_limit. Plan capacity for the index separately; a database process configured with a memory limit may still need additional RAM for VSS.
Disk persistence is experimental
By default, the documentation says HNSW indexes can only be created on in-memory databases. Disk-backed persistence requires explicitly enabling the experimental setting:
SET hnsw_enable_experimental_persistence = true;DuckDB warns that recovery of custom indexes from the write-ahead log is not properly implemented. An unexpected shutdown with uncommitted changes can risk index or data loss or corruption, and the documentation recommends against using this feature in production. Persistent index serialization occurs at checkpoint, writes the full index rather than incremental updates, and loads the index into memory on access after restart. Treat the setting as experimental, not as a production durability guarantee.
Know which vector operations do not use HNSW
The vss_join and vss_match macros can simplify vector joins or fuzzy matching, but the current documentation says they do not require or use the HNSW index. Do not assume that a vector join is accelerated just because an HNSW index exists.
Quick wins for a faster PC:
Repair Windows errors before they cause bigger problemsFix Now →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →DuckDB’s October 2024 update also cautioned that single-vector lookups may incur latency relative to other solutions even when HNSW lookup itself is fast. That is a project-reported caution, not a current comparative benchmark; test the end-to-end workload that matters to your application.
Decide whether VSS fits your workload
VSS is most suitable when your workload can use the documented fixed-array, top-k distance pattern and you can keep the index in available RAM. Compare options using these practical criteria:
- Exactness: If exact ordering is required, or the dataset is small enough that a scan is acceptable, an exact scan may be simpler. This is workload-dependent, so verify against your data.
- Query shape: Check that the query uses a supported distance function, a compatible constant query vector, and a top-k limit; inspect the plan on the target version.
- Operational needs: Account for index build and memory costs, update patterns, persistence and recovery requirements, and whether the experimental status is acceptable.
- Scale and service model: When comparing DuckDB with a separate vector-search service, weigh operational simplicity against dataset scale, update frequency, and service requirements. There is no universal winner established by the documentation.
Finally, version matters. VSS remains experimental, and optimizer behavior and extension constraints may change. Consult the documentation for the DuckDB release you deploy, then validate recall and latency with representative data before relying on index selection or tuning assumptions.
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.
Free tools Windows power users keep installed
One-click scans. No signup required.
