Databricks Adds NEAREST BY SQL Join for Batch Vector Search
Databricks has introduced NEAREST BY, a SQL join that finds the k nearest rows by vector similarity or distance for every query row, running natively in Databricks Runtime. According to the company, it is built for batch workloads such as entity resolution and deduplication, where a whole job must finish within its SLA rather than answer a single lookup quickly.
- Databricks introduced NEAREST BY, a SQL join that returns the k nearest rows per query row by vector similarity or distance.
- EXACT guarantees the true top-k; APPROX allows an approximate strategy such as an ANN index, so indexes cannot silently change results.
- A fused Photon operator with a custom blocked GEMM kernel targets peak arithmetic throughput, according to Databricks.
- The IVF index is an ordinary liquid-clustered Delta table, so embeddings stay in the Lakehouse with no separate vector store.
- The design targets batch jobs such as entity resolution, deduplication and enrichment, where meeting an SLA at reasonable cost matters most.
Why batch vector search needs a different design
Databricks says vector search began as a serving problem: a chatbot or search bar sends one query embedding and expects the top-k nearest documents within tens of milliseconds. The company argues that a good share of the vector search workloads on its platform are instead batch-oriented, with nearest neighbors precomputed offline rather than looked up at request time.
It gives three customer-type examples. A payments company matches 100M+ daily transactions against 140M merchant embeddings for entity resolution. A data firm enriches tens of millions of historical records nightly. A quantitative fund runs million-query batches against a 50M-vector corpus for taxonomy tagging. Databricks lists deduplication, semantic tagging, classification, record enrichment and batch recommendations as further examples.
For such jobs, the company says, success is measured by whether the whole job completes within its SLA at a reasonable cost. Its stated requirements are aggregate throughput over per-request latency, scale on both sides of the join, elastic parallelism across hundreds to thousands of cores, near-peak arithmetic per core, and fault tolerance for jobs that run for hours.
From a row-by-row function to a top-k ranking join
The first version of the VECTOR_SEARCH SQL function sent requests to an external real-time Vector Search endpoint, one query row at a time. Every row meant a network request, a response to deserialize and possibly retries. Databricks says throughput was capped by the endpoint's sizing rather than the cluster size, leaving the runtime engine as a dispatcher.
The company now treats batch vector search as one large query: for each row on the left, find the k nearest rows on the right. NEAREST BY makes that a first-class relational operation. The join is asymmetric, similar to LATERAL, so the left side drives and the right side is searched. Ranking direction is explicit: BY SIMILARITY ranks descending and BY DISTANCE ascending. LEFT OUTER keeps query rows that have no candidates.
The syntax also separates two modes. According to Databricks, EXACT guarantees the true top-k by exhaustive evaluation, while APPROX lets the optimizer substitute an approximate strategy, such as an ANN index, where one applies. Because only queries that specify APPROX consent to approximation, creating or dropping an index cannot silently change query results.
Databricks contrasts this with other systems. Postgres with pgvector and Snowflake combine distance operators with ORDER BY … LIMIT, which needs a LATERAL subquery per driving row for batch use. BigQuery uses a table-valued function whose column references are strings the parser cannot validate.
How it runs in Photon
The optimizer lowers NEAREST BY onto standard relational operators. It tags each query row with a generated id, scores every query and base pair, keeps the k best per id with a grouped top-k, and inlines the kept rows back out. Since every operator is an ordinary relational one, Databricks says the plan distributes, spills and retries like any other, so correctness and fault tolerance come for free.
Databricks implemented every operator in this plan natively in Photon, its vectorized C++ query engine. It also added a fused operator built for vector search that collapses the middle of the plan using a custom blocked GEMM kernel, which the company says pushes distance scoring toward the peak arithmetic throughput of the hardware.
The building blocks are vector SQL functions over ARRAY<FLOAT> columns. Three handle scoring: vector_inner_product and vector_cosine_similarity, where higher means nearer, and vector_l2_distance, where lower means nearer. The company also shipped vector_norm and vector_normalize, plus the aggregates vector_sum and vector_avg, which support index construction through k-means centroids.
Photon runs these as native SIMD kernels. For portability across AWS, Azure and GCP and across x86 and ARM, each kernel is compiled into several ISA-specific clones, and the best one the CPU supports is chosen at run time.
What it means for users
Databricks says embeddings stay in Delta tables on the Lakehouse, so there is no separate vector store or sync pipeline to operate. The optional IVF index is an ordinary liquid-clustered Delta table that prunes away most partitions on read, letting APPROX queries score a fraction of the base vectors with the same kernels.
For teams running scheduled jobs such as entity resolution or enrichment, the pitch is that compute sizes itself to the job, scales out for the run and down to zero afterward, with the engine handling retries and spilling to disk instead of client-side rate limiting or retry loops.
Databricks: NEAREST BY Join: Scaling Vector Search in Databricks Runtime
