DuckDB Introduces ACORN‑1 Filtered HNSW Vector Search Extension

The DuckDB‑HNSW‑ACORN community extension enables fast, high‑recall vector similarity search with built‑in filter predicates using the ACORN‑1 algorithm, eliminating the “post‑filter” limitation of the original DuckDB‑VSS extension.

Introduction: Why This Extension Matters

Data engineers, data scientists, and developers constantly seek a single‑pane‑of‑glass solution for storing, indexing, and querying high‑dimensional vectors. DuckDB, the in‑process analytical database, already supports vector types via its ARRAY column, but its original vector similarity search (VSS) extension applied WHERE filters only after the HNSW index returned candidates. This caused many filtered queries to return fewer rows than requested, especially when filter selectivity was low.

The new DuckDB‑HNSW‑ACORN extension pushes filter predicates directly into the HNSW graph traversal using the ACORN‑1 algorithm. The result is a predictable LIMIT‑based result set with high recall, regardless of filter selectivity. In short, you now get exactly the number of results you ask for, filtered correctly, and delivered at lightning speed.

DuckDB and Vector Search: A Quick Overview

DuckDB is an embeddable, columnar SQL engine designed for analytical workloads. Since version v0.10.0, it supports fixed‑size FLOAT[ N ] arrays, making it a natural fit for dense vector embeddings generated by LLMs, recommendation systems, and computer‑vision models.

Vector search is the process of finding the nearest neighbors of a query vector under a distance metric (e.g., Euclidean, cosine). Traditional brute‑force scans are O(N) and become prohibitive at scale. Hierarchical Navigable Small World (HNSW) graphs provide sub‑linear query time while preserving high recall, and they have become the de‑facto standard for approximate nearest neighbor (ANN) search.

By embedding HNSW directly into DuckDB, users can run SELECT … ORDER BY array_distance(vec, …) LIMIT k queries without leaving the SQL environment, enabling seamless pipelines that combine relational analytics with vector similarity.

Understanding HNSW and ACORN‑1 Pre‑Filtering

HNSW (Hierarchical Navigable Small World) builds a multi‑layer graph where each node (vector) connects to a limited set of neighbors. During a query, the algorithm descends from the top layer, greedily moving to closer nodes until it reaches the base layer, where a final local search yields the nearest neighbors.

The original DuckDB‑VSS extension performed this traversal without considering any WHERE clause. After the HNSW returned a candidate set, DuckDB filtered the rows, often discarding many candidates and leaving the LIMIT unmet.

ACORN‑1 (Adaptive COntinuous Re‑ordering with Neighborhood) solves this by integrating filter predicates into the graph walk. It expands two‑hop neighbors only when the current neighborhood is insufficiently connected, following Lucene’s “90 % rule”. The algorithm dynamically switches strategies based on filter selectivity:

• ≥ 60 % selectivity → simple post‑filter (fallback to original behavior).
• 1 % – 60 % selectivity → ACORN‑1 filtered traversal.
• < 1 % selectivity → brute‑force exact scan.

These thresholds are configurable via SET hnsw_acorn_threshold and SET hnsw_bruteforce_threshold, giving you fine‑grained control over performance vs. accuracy trade‑offs.

Key Features of the duckdb‑hnsw‑acorn Extension

1. Integrated Filtered Search

No special syntax is required. The optimizer automatically detects patterns like WHERE category = 'X' ORDER BY array_distance(vec, …) LIMIT k and activates ACORN‑1. This means existing SQL codebases can adopt filtered vector search with a single CREATE INDEX … USING HNSW statement.

2. Configurable Strategy Thresholds

SET hnsw_acorn_threshold = 0.6;   -- 60% selectivity cutoff
SET hnsw_bruteforce_threshold = 0.01; -- 1% cutoff

3. Support for Multiple Distance Metrics

While Euclidean (L2) is default, you can create cosine or inner‑product indexes via the WITH (metric = 'cosine') clause. This flexibility aligns with the most common embedding similarity measures used in LLM‑driven applications.

4. Seamless Index Management

Indexes can be rebuilt, compacted, or dropped using standard DuckDB PRAGMA commands. The PRAGMA hnsw_compact_index('my_idx') function removes stale entries after deletions, keeping memory usage optimal.

5. Compatibility with UBOS AI Platform

If you’re already leveraging the UBOS platform overview for AI‑driven workflows, the duckdb‑hnsw‑acorn extension can be called from Workflow automation studio pipelines, enabling end‑to‑end vector search without leaving the UBOS ecosystem.

Real‑World Use Cases & Performance Benchmarks

Use Case 1: Personalized Content Recommendation

A streaming service stores 2 M movie embeddings (768‑dimensional Nomic vectors). Users request “similar movies” filtered by language or rating. With the original VSS, a query like WHERE language='Japanese' often returned 0‑2 rows because the filter was applied after the HNSW scan. The ACORN‑1 extension consistently returned the full LIMIT 10 set, preserving user experience.

Use Case 2: Fraud Detection in Financial Transactions

Financial institutions embed transaction histories into 256‑dimensional vectors and need to find nearest neighbors that also match a risk_score > 0.8 predicate. The filtered HNSW search reduces the candidate pool early, cutting query latency from ~120 ms (brute‑force) to ~15 ms while maintaining > 95 % recall.

Benchmark Summary (228 k movies, 768‑dim embeddings)

The benchmark demonstrates that ACORN‑1 restores full recall across low‑selectivity filters while keeping query latency comparable to the unfiltered HNSW path.

Getting Started: Installation & First Queries

Step 1: Build the Extension

# Clone the repo
git clone https://github.com/cigrainger/duckdb-hnsw-acorn.git
cd duckdb-hnsw-acorn

# Build (requires CMake & a C++ compiler)
make

# The binary includes the extension:
./build/release/duckdb

Step 2: Load the Extension in DuckDB

INSTALL extension hnsw_acorn;   -- if using DuckDB's extension manager
LOAD hnsw_acorn;

Step 3: Create a Table & Index

CREATE TABLE movies (
    id      BIGINT,
    title   VARCHAR,
    language VARCHAR,
    rating  DOUBLE,
    vec     FLOAT[768]   -- Nomic embedding
);

-- Populate the table (example using COPY or INSERT)
COPY movies FROM 's3://my-bucket/movies.parquet' (FORMAT 'parquet');

-- Build the HNSW index
CREATE INDEX movies_hnsw_idx ON movies USING HNSW (vec) WITH (metric='cosine');

Step 4: Run a Filtered Vector Search

SELECT id, title, language, rating
FROM movies
WHERE language = 'Japanese'
ORDER BY array_distance(vec, [0.12, -0.03, …]::FLOAT[768])
LIMIT 10;

The optimizer detects the pattern and automatically applies ACORN‑1, guaranteeing ten Japanese movies most similar to the query vector.

Step 5: Tune Thresholds (Optional)

SET hnsw_acorn_threshold = 0.55;   -- use ACORN‑1 for slightly higher selectivity
SET hnsw_bruteforce_threshold = 0.005; -- switch to exact scan only for <0.5% selectivity

Adjust these values based on your data distribution. For highly selective filters (e.g., user‑specific IDs), a lower brute‑force threshold may be more efficient.

Community Involvement: How You Can Contribute

The duckdb‑hnsw‑acorn project lives on GitHub under an MIT license, encouraging open collaboration. Contributors can:

• Submit bug reports for edge‑case filter predicates.
• Propose new distance metrics (e.g., Manhattan, Jaccard) via pull requests.
• Improve documentation with real‑world examples, especially for integration with the UBOS templates for quick start.
• Develop UBOS‑compatible wrappers that expose the extension through the Web app editor on UBOS, enabling non‑SQL users to run vector searches via a UI.

The community also benefits from the UBOS partner program, which offers co‑marketing opportunities for extensions that enhance the Enterprise AI platform by UBOS.

Conclusion: Unlock Faster, Filter‑Aware Vector Search Today

The DuckDB‑HNSW‑ACORN extension bridges a critical gap between high‑performance ANN search and real‑world filtering requirements. By embedding ACORN‑1 directly into the HNSW traversal, it guarantees that filtered queries return the exact number of results you request, with recall comparable to brute‑force scans.

Whether you’re building a recommendation engine, a fraud‑detection pipeline, or an AI‑enhanced search feature inside the UBOS homepage, this extension empowers you to keep all data and logic inside a single, embeddable SQL engine.

Ready to try it? Clone the repository, build the extension, and start experimenting with filtered vector search in minutes. Join the community, share your benchmarks, and help shape the next generation of AI‑native databases.