HNSW approximate search

Create, query, and tune HNSW indexes for fast approximate nearest-neighbor search.

Version
Latest
v0.1.0 · latest 2 min read
On this page
  1. Create and query
  2. Practical behavior
  3. Transactions and durability
  4. When not to use HNSW

HNSW (Hierarchical Navigable Small World) is the default approximate index for dense vectors over L2, cosine, and inner-product metrics. It answers top-k queries by traversing a layered graph instead of scanning every row — much faster on large tables, with a small, tunable chance of missing a true neighbor.

Status: Supported for dense numeric vectors × L2 / cosine / inner-product.

Create and query#

sqlsource
-- Autocommit context (not inside BEGIN ... COMMIT)
CREATE INDEX docs_emb_hnsw ON docs USING hnsw (embedding);
 
SELECT id, label, embedding <-> [1.0, 0.0, 0.0] AS dist
FROM docs
ORDER BY embedding <-> [1.0, 0.0, 0.0]
LIMIT 5;

No query rewrite is needed: the same ORDER BY distance LIMIT k pattern automatically considers the index. Confirm with EXPLAIN (see Indexes).

Practical behavior#

  • Small tables ignore the index on purpose. With hundreds of rows, a scan is faster than graph traversal. The planner reports Index: usable with an exact scan and moves on. This is correct behavior, not a misconfiguration.
  • The automatic threshold is around 4096 rows (vector.auto_ann_min_rows). Below it, expect exact scans; above it, expect the index to engage where eligible.
  • Recall vs speed is controlled by ef_search (candidate list size) and candidate oversampling: raise them for better recall at higher latency, lower them for speed. Start with defaults and only tune against measured recall on your data.
  • Filtered queries (WHERE + vector ordering) are served by the exact path in v0.1.0 even when an HNSW index exists — see Filtering.

Transactions and durability#

  • Create the index in autocommit. Creation inside an explicit transaction is refused.
  • Index builds observe a consistent snapshot: rows committed after the build starts are merged safely, and a cancelled build never publishes a half-built index.
  • Indexes are derived caches over committed rows. They survive restarts and are rebuilt from rows after a backup restore (the restore verification in Backup and restore covers re-querying vector data post-restore).
  • Repeatable-read snapshots decline ANN and run exact scans to preserve snapshot semantics.

When not to use HNSW#

  • Tables under a few thousand rows (scan is already fast).
  • Evaluation baselines where recall must be exactly 100% (SET vector.ann_enabled = off instead).
  • Binary / sparse / multivector columns (ANN falls back to exact — see Limitations).

See Exact vs approximate for the decision framework and Performance for measuring honestly.

Was this page helpful?