Vector Search (kNN)

VectorkNNHNSWIVFFLAT

Stable On by default · production-ready

What it is

Vector search finds the rows whose embedding is closest to a query vector. Brute-force distance is fine for a few thousand rows, but at scale you build a vector index so the search is sub-linear. AetheriusDB supports two index types over a TENSOR(N) column:

• HNSW — graph-based, best recall at small-to-medium k.
• IVFFLAT — centroid-based, lower memory and faster build at very large N.

Both work with the same SQL — you only change the index you point a query at.

Why it matters

At a few thousand rows a brute-force scan is fine; past that you want a vector index so search cost stops growing with the table. AetheriusDB gives you two proven index types over the same TENSOR(N) column and the same SQL, so you tune recall-vs-memory by swapping the index — not by rewriting your queries or moving to another system.

• Sub-linear at scale — an index turns “scan every row” into “visit a few,” so latency stays flat as the table grows.
• Pick your trade-off — HNSW for best recall, IVFFLAT for lower memory and faster builds; same query either way.
• Filter first, then rank — an ordinary SQL predicate shrinks the candidate set before the distance math runs.

How fast it is

A kNN query over a built index is sub-linear — dramatically faster than brute-force distance once you’re past a few thousand rows — and the SQL pre-filter means the index only ranks the rows that already passed your WHERE clause.

Build an index

The index sits on a single TENSOR(N) column. Tune it with an optional WITH (…) clause — the option names are pgvector-compatible.

-- HNSW: best recall at small-to-medium k
CREATE INDEX products_vec_hnsw
  ON products (vec)
  USING HNSW
  WITH (m = 16, ef_construction = 64, ef_search = 40);

-- Or IVFFLAT: lower memory, faster build at very large N
CREATE INDEX products_vec_ivf
  ON products (vec)
  USING IVFFLAT
  WITH (lists = 100, probes = 8);

Index	Tuning options
HNSW	m (graph degree), ef_construction (build-time candidate width), ef_search (query-time candidate width)
IVFFLAT	lists (number of centroids), probes (centroids scanned per query)

Unknown option keys or non-positive values are rejected at CREATE INDEX time, before the catalog is touched — a typo fails fast rather than at first query.

Run a kNN query

Order by the distance to a query vector and take the top k. When the query is a single-table ORDER BY TENSOR_L2(col, :q) LIMIT k (or TENSOR_COSINE) over an indexed column, the planner serves it straight from the index:

-- Served from the HNSW/IVFFLAT index: k-nearest by cosine distance.
SELECT id, name, price
FROM   products
ORDER BY TENSOR_COSINE(vec, :q)
LIMIT 10;

-- Euclidean (L2) distance instead:
SELECT id, name
FROM   products
ORDER BY TENSOR_L2(vec, :q) ASC
LIMIT 20;

Above the index’s ANN threshold the result is approximate (the standard approximate-nearest-neighbour contract); below it, it is exact. The fast-path applies to a single-table query with exactly one ORDER BY distance key and a LIMIT — anything richer (WHERE, GROUP BY, joins, OFFSET, DISTINCT) runs the normal plan.

For small tables you can skip the index entirely and rank directly with TENSOR_COSINE(vec, :q) — see Tensor & Vector Types.

Pre-filter, then rank

The whole point of having vectors in the database is hybrid queries: narrow the candidate set with ordinary SQL predicates first, then rank what’s left by distance.

SELECT id, subject,
       TENSOR_COSINE(embedding, :query_vec) AS dist
FROM   support_tickets
WHERE  created_at >= '2026-05-01'
  AND  region = 'EU'
ORDER BY dist ASC
LIMIT 10;

Spatial pre-filter

If your table has a spatial-locality key (a USING HILBERT 2D column), add WHERE hilbert BETWEEN ? AND ? to narrow the candidate set before any distance math runs. For narrow queries this typically skips most of the table.

Choosing an index

	HNSW	IVFFLAT
Strength	Highest recall	Lower memory, faster build
Best for	Small-to-medium k, recall-sensitive search	Very large datasets, throughput-focused
Tuning	m / ef_construction / ef_search — good defaults out of the box	lists / probes — raise probes to trade speed for recall

Maintenance & freshness

Vector indexes stay current with your data automatically:

• INSERT keeps the index current incrementally — no full rebuild per row.
• DELETE / UPDATE are reflected exactly — results never include removed rows or stale versions, with no manual rebuild.
• Bulk COPY is picked up automatically — the index is current as soon as the load completes.
• Rows with a NULL / missing embedding are skipped, not errored.

Indexes are restart-safe: the definition and tuning are persisted in the catalog and the index rehydrates at boot.

Never served stale

Vector indexes are never served stale. If a write reached the table through any path the index didn’t observe, the query transparently falls back to a correct exact brute-force rank until the index is current again — an index a write bypassed is never served as if it were current.

Tensor & Vector Types →

Declare the TENSOR(N) column the index sits on.

Multimodal Fabric →

Where image/audio/video embeddings come from.

Quickstart →

Index and kNN query end-to-end.

Query Data →

The broader SQL and indexing surface.