Vector search (preview)

Store embeddings next to your data and find the nearest rows — "documents like this one", "customers similar to this profile" — with plain SQL, under the same privileges, row filters, masking, and audit as any other query.

Preview

Similarity functions are part of DuckDB itself and are stable. The HNSW index comes from DuckDB's vss extension, whose on-disk persistence is marked experimental by DuckDB. Treat indexes as rebuildable and read the limits below.

Store vectors

Use fixed-size FLOAT[n] arrays (one column per embedding model):

CREATE TABLE dok (id INTEGER, cabang VARCHAR, judul VARCHAR, emb FLOAT[3]);
INSERT INTO dok VALUES (1, 'JKT', 'kredit rumah', ARRAY[0.9, 0.1, 0.0]);

Write vector literals in write statements with standard SQL ARRAY[...]. DuckDB's short form [0.9, 0.1, 0.0] cannot be parsed by the server's write-target analysis and is rejected (fail-closed); it is fine in SELECT. Bulk loads through ADBC ingest or a materialized table need no literals at all.

Search (no extension needed)

DuckDB's array functions compute similarity directly:

Function	Meaning
array_cosine_similarity(a, b)	cosine similarity (1 = same direction)
array_cosine_distance(a, b)	1 − cosine similarity
array_distance(a, b)	Euclidean (L2) distance
array_inner_product(a, b) / array_negative_inner_product(a, b)	dot product

SELECT id, judul
FROM dok
ORDER BY array_cosine_distance(emb, [1.0, 0.0, 0.0]::FLOAT[3])
LIMIT 10;

This is an exact (brute-force) search over the rows the caller may read — fast enough for hundreds of thousands of rows on one node.

Speed it up with an HNSW index

For larger tables, an HNSW index answers ORDER BY <distance> LIMIT k without scanning every row. It needs the vss extension, which is bundled in the lake image variant (the static image cannot load extensions):

-- init.sql (--init-sql-file): load once at startup
LOAD vss;
SET GLOBAL hnsw_enable_experimental_persistence = true;   -- only for file-backed catalogs

-- admin only (see Governance below)
CREATE INDEX dok_hnsw ON dok USING HNSW (emb) WITH (metric = 'cosine');

Use the same metric in the index and in the query (cosine ↔ array_cosine_distance, l2sq ↔ array_distance, ip ↔ array_negative_inner_product). DuckDB then plans an HNSW_INDEX_SCAN.

Governance

Vector search is ordinary SQL, so everything applies:

• Privileges — searching needs read on the table, with or without the index; the index scan is resolved to its full table name before authorization, exactly like a table scan. Creating an HNSW index needs admin: DuckDB builds it with an extension operator, and the server only lets admins run operators it does not know (fail-closed). An ordinary CREATE INDEX without USING HNSW needs write as usual.
• Row filters are applied before the nearest-neighbour search: a branch user gets the nearest rows of their branch. (When a row filter or mask applies, DuckDB may fall back to the exact search instead of the index — results stay correct.)
• Masking — embeddings of personal data can reveal it. Tag such columns in classification.yaml. null or redact hides the vector; partial on a vector (or any list/struct) is treated as redact, because the last characters of a vector's text are part of its values.
• Audit and access history record the vector column like any other.

Limits

• Preview. HNSW persistence is experimental in DuckDB: keep the index rebuildable (for example in a schema migration or after a restore), or use it on in-memory catalogs.
• The index lives in memory while the server runs; size the instance for it.
• Embeddings are not generated by KarhutlaSQL — compute them in your pipeline (or an AI service) and load them as FLOAT[n].
• The vss extension needs the dynamic build / lake image.