Skip to content

SYSTEM Cited by 1 source

NEAREST BY

NEAREST BY is a SQL join syntax in the Databricks Runtime that makes batch vector search a first-class relational operation — a top-k ranking join. For each row on the left (the query side), it returns the k nearest rows on the right (the base/searched side), ranked by a pluggable scalar scoring expression. It is the engine-native replacement for the earlier VECTOR_SEARCH SQL function, which federated each query row to an external real-time serving endpoint and so capped throughput at endpoint sizing rather than cluster size.

NEAREST BY exists because "a batch vector search is not a million small searches. It is one large query" — entity resolution, deduplication, semantic tagging, classification, enrichment, and batch recommendations run millions of queries against millions-to-billions of base vectors on a schedule, measured by whether the job finishes within SLA at reasonable cost, not by single-lookup latency. Executing enormous joins is what the runtime engine already excels at. (Source: sources/2026-10-05-databricks-nearest-by-join-scaling-vector-search-in-databricks-runtime)

Syntax semantics

  • Asymmetric join (like LATERAL): the left side drives, the right side is searched.
  • Explicit ranking direction: BY SIMILARITY (descending) or BY DISTANCE (ascending).
  • LEFT OUTER keeps query rows that have no candidates.
  • Pluggable BY expression: any orderable scalar over both sides — other scoring expressions can reuse the same clause.
  • APPROX vs EXACT is a semantic contract. EXACT guarantees the true top-k by exhaustive evaluation; APPROX lets the optimizer substitute an approximate strategy such as an IVF index. Because approximation is opt-in, creating or dropping an index can never silently change query results — only APPROX queries consent to it.

Interface-shape contrast

Engine Shape Weakness
Postgres + pgvector, Snowflake distance operators + ORDER BY … LIMIT batch needs a LATERAL subquery per driving row; fragile KNN/ANN fast-path recognition
BigQuery table-valued function batch is first-class, but column refs are unvalidated strings
NEAREST BY native top-k ranking join binary relational op; APPROX/EXACT contract; validated columns

Execution architecture

NEAREST BY parses into a logical join node that the optimizer lowers onto standard relational operators — tag each query row with a generated id, score every (query, base) pair, keep the k best per id with a grouped top-k, inline the kept rows out. Because every operator is ordinary, the plan distributes, spills, and retries like any other on Spark — correctness and fault tolerance come for free. Two primitives carry all runtime:

  1. SIMD distance kernels — the ARRAY<FLOAT> similarity/distance functions run as native Photon FMA kernels (portable ISA-specific clones, zero-copy column reads, relaxed float semantics, nothing scalar in the hot loop).
  2. Bounded top-k aggregate — a max_by/min_by overload with a K parameter: an O(k)-state bounded heap with one-comparison admission, late materialization (stores indices, not copies), and a partial/merge contract that makes the distributed result exact. A canonical custom hot-path data structure.

Fused operator + blocked GEMM

A single fused Photon operator replaces the cross join, the distance projection, and optionally the partial top-k, scoring query-by-base tiles with a custom blocked GEMM kernel. The roofline model is the reason: the pairwise cross join has no data reuse (AI = 1/4, DRAM-pinned at 0.8% of peak), while the fused GEMM buffers Q and streams B (AI = nq/2), crossing the ridge at nq = 64 and riding the compute roof. The full nq × nb score matrix never reaches DRAM — tiles land in a recycled score buffer consumed in place by the streaming top-k.

APPROX via the IVF index

For APPROX, NEAREST BY uses an IVF index stored as an ordinary Delta table liquid-clustered by centroid id — k-means centroids, each base row assigned to its nearest centroid, each query scoring only its nprobe nearest clusters (≤ 0.1% of the corpus at billions scale). The APPROX rewrite probes centroids with the same top-k join, equi-joins on centroid id, scores + top-k, and unions a brute-force compensation branch over files added since the last refresh — so a stale index prunes less but never degrades quality (patterns/cheap-approximator-with-expensive-fallback). IVF was chosen over graph indexes because cluster scans parallelize across executors and the layout is columnar, whereas graph traversal is a serial lookup chain (concepts/sequential-vs-random-io).

Measured results

On canonical customer workloads with indexed ANN at ≥96% recall@K (base 100K–5B vectors, query batches up to 10M): 10M-record self-dedup in minutes; 10M × 100K classification in under a minute; 1M × 10M recommendation refresh in minutes; 1M × 1B entity resolution in minutes.

Seen in

Last updated · 854 distilled / 2,448 read