Databricks Runtime Introduces NEAREST BY: Native SQL Vector Search at Scale

Vector search has become a foundational building block for retrieval-augmented generation (RAG), recommendation systems, semantic matching, and AI-powered data applications.

But there is an architectural detail that is easy to overlook: not every vector-search workload is an online serving problem.

Sometimes you need to match millions of query vectors against a large collection of embeddings in a scheduled job. You might be matching transactions to merchants, finding similar products, resolving duplicate records, or generating candidate pairs for a machine-learning pipeline.

In those cases, routing every query through a separate real-time vector-search endpoint can introduce an unnecessary dependency.

Databricks is addressing this problem with NEAREST BY, a native SQL join for batch vector search in Databricks Runtime. The implementation, described in a Databricks engineering article published on October 5, 2026, combines a fused Photon operator, a specialized General Matrix Multiply (GEMM) kernel, and an approximate nearest-neighbor index stored in a liquid-clustered Delta table.

The idea is straightforward: instead of treating every vector lookup as a remote service request, bring the search operation into the analytical query engine.

For data engineers and AI engineers, that could simplify an important class of workloads.

NEAREST BY on one page: query vectors and a Delta vector table meet in a SQL join, Photon’s fused GEMM scores them, and APPROX or EXACT decides how the top-k is found.

Why batch vector search matters

Databricks describes a payments company that matches more than 100 million daily transactions against 140 million merchant embeddings for entity resolution.

Or picture a nightly job that compares millions of new product embeddings against an existing catalog.

The conventional architecture might look like this:

Query vectors
     |
     v
Batch orchestration
     |
     v
External vector-search endpoint
     |
     v
Results returned to Spark
     |
     v
Downstream transformations

This can work well, particularly when the application needs low-latency online retrieval. But for large batch workloads, it introduces a separate service, request orchestration, network overhead, and another component to monitor.

With NEAREST BY, the architecture can instead look like this:

Query vectors                 Base vectors
      |                             |
      v                             v
  Query table                Delta vector table
      |                             |
      +-------------+---------------+
                    |
                    v
             NEAREST BY JOIN
                    |
                    v
          Top-k matching vectors
                    |
                    v
          Downstream SQL / Spark

Databricks describes this as bringing batch vector search directly into the lakehouse, with no separate vector-serving system required for the supported query pattern: “Your Lakehouse is also your vector store.”

The practical benefit is architectural simplicity: query vectors, indexed vectors, and downstream processing can participate in the same SQL workflow.

That doesn’t eliminate every external vector database use case. Interactive applications with strict online latency requirements may still need a dedicated serving architecture, such as the online AI Search path we covered in our Databricks lakehouse breakdown. The important distinction is choosing the right search architecture for the workload.

What is NEAREST BY?

NEAREST BY extends SQL joins with a top-k nearest-neighbor operation.

For each row on the left side of a join, it finds up to k matching rows on the right, ranked by a distance or similarity expression.

The syntax supports two key choices:

• EXACT: returns the exact top-k results according to the ranking expression.
• APPROX: allows approximate search strategies to return a top-k set that approximates the exact ranking.

The ranking expression can use vector similarity or distance functions, including cosine similarity, L2 distance, and inner product. A query can therefore express nearest-neighbor retrieval as a relational operation rather than manually orchestrating a table-valued function or an external API.

The official NEAREST BY SQL reference documents the join semantics and supported ranking expressions.

A simplified example illustrates the idea:

SELECT
    q.query_id,
    b.item_id,
    vector_cosine_similarity(
        q.embedding,
        b.embedding
    ) AS similarity
FROM query_vectors AS q
JOIN base_vectors AS b
    APPROX NEAREST 10
    BY SIMILARITY vector_cosine_similarity(
        q.embedding,
        b.embedding
    );

This example illustrates the intended query shape; check the current Databricks SQL reference and your runtime version for exact syntax and supported expressions before deploying it.

The important point is that top-k matching becomes part of the SQL plan. The runtime can choose how to distribute the work and execute the ranking, instead of requiring the application to issue one network request per query vector.

How Databricks makes batch vector search fast

The engineering work goes beyond adding a new SQL keyword.

Databricks describes three key pieces: the join operator, a fused Photon GEMM kernel, and an index that uses the lakehouse’s existing storage model.

1. NEAREST BY turns top-k search into a join operation

Traditional top-k queries can be expressed using window functions, ranking, and filtering. However, sorting every candidate and then retaining only the first k results can perform unnecessary work.

Databricks also extended the max_by and min_by aggregates with an optional third argument that returns up to a specified number of values. The implementation uses bounded selection state rather than fully sorting every candidate in each group. (max_by documentation)

This matters when a query generates a large number of candidate matches but needs only a small number of winners.

Instead of materializing and sorting every candidate, an implementation can maintain a bounded top-k selection as scores arrive.

2. Photon fuses the computation

Vector similarity calculations are dominated by repeated arithmetic over embedding dimensions.

For a batch of query vectors and a collection of base vectors, the system needs to calculate many pairwise scores. Much of that work can be represented as matrix multiplication.

Databricks describes a fused Photon operator built around a blocked GEMM kernel with two levels of blocking: packed base-vector panels are reused across query tiles to keep data in the CPU cache, and register blocking reuses each loaded value. Register-level selection avoids materializing the entire query-by-base score matrix in memory when k is small enough; when selection state becomes too large, the implementation falls back to a blocked GEMM followed by partial aggregation.

Why does that matter?

Consider the number of possible scores:

scores = query vectors × base vectors

For one million queries against 100 million base vectors, an exhaustive comparison would involve 10^14 (one hundred trillion) query-base pairs.

You generally don’t want to materialize that entire score matrix.

The fused operator is designed to compute scores efficiently and retain only the information needed for top-k selection, reducing unnecessary memory traffic.

There is also a useful batch-size insight in Databricks’ roofline analysis: the kernel’s arithmetic intensity grows with the number of queries in a batch, and on their reference machine it becomes compute-bound from about 64 queries, well below typical production batch sizes. In other words, batching queries is what lets the hardware’s arithmetic units, rather than memory bandwidth, set the speed limit.

3. The vector index lives in Delta tables

Databricks describes an inverted-file (IVF) approximate nearest-neighbor index implemented as an ordinary liquid-clustered Delta table.

The index helps the engine eliminate portions of the base collection that do not need to be scanned for a query. Rather than evaluating every vector, approximate search can focus on a smaller candidate set: Databricks says that at billions scale, a query probes 0.1% of the corpus or less.

The engineering advantage is that the index uses the lakehouse’s table storage and governance model rather than requiring a separate vector-serving database for this batch workflow.

That doesn’t mean the index requires no maintenance. Index construction, data freshness, clustering, and query configuration still matter. It means the index can be managed within the Delta-table architecture.

APPROX versus EXACT is an important contract

One of the most useful aspects of NEAREST BY is that approximation is explicit in the query.

With APPROX, the optimizer can use approximate index-based strategies when available.

With EXACT, the requested result is the exact top-k under the ranking expression. The optimizer cannot silently replace that requirement with an approximate result simply because an index exists.

That distinction matters in production.

Consider these workloads:

Workload	Likely approach	Why
Product recommendations	APPROX	Small ranking differences may be acceptable if recall and business metrics remain strong
Duplicate-record discovery	Depends on requirements	Approximation may help candidate generation, but final matching may need exact verification
Scientific similarity analysis	EXACT when required	The result may need to preserve exact ranking semantics
Large-scale candidate generation	APPROX	Reducing the candidate space can improve throughput

These are starting points, not universal rules. Whether approximate retrieval is acceptable depends on the consequences of missing a relevant candidate.

Approximation is a product and data-quality decision, not just a performance setting.

What Databricks reports about scale

Databricks reports evaluating the implementation on canonical customer workloads, with base collections ranging from 100,000 to five billion vectors and query batches of up to ten million rows, targeting at least 96% recall@K for indexed approximate search.

Its examples include a semantic-deduplication self-join of a 10-million-record table that completed in minutes, and 10 million classification queries against 100,000 reference vectors in under a minute.

These results are useful evidence of the workload sizes the team investigated. They should not be interpreted as universal performance guarantees: actual throughput and recall depend on vector dimensions, data distribution, hardware, query batch size, index configuration, and the value of k.

For engineering teams, the right next step is to benchmark their own embeddings and query distributions rather than assume that a published result will transfer directly to production.

What data and AI engineers should do

If you’re considering NEAREST BY, I’d evaluate it in a few deliberate steps.

1. Classify the workload first

Ask whether the search is part of a scheduled batch pipeline or a latency-sensitive application request.

Batch similarity matching
        |
        v
Evaluate NEAREST BY

Interactive retrieval
        |
        v
Evaluate online vector serving

A lakehouse-native batch join may be a better fit for nightly matching, large-scale enrichment, or offline candidate generation. A dedicated online service may remain appropriate for interactive retrieval with strict response-time requirements.

2. Decide whether approximation is acceptable

Start by defining what a good result means for your workload.

Measure recall@K against an exact baseline on a representative sample. Then evaluate the impact of missed candidates on downstream outcomes.

For example, a recommendation pipeline may tolerate a small recall loss if conversion metrics remain stable. A compliance or financial-matching workflow may require exact ranking or a separate exact verification step.

3. Validate the runtime and SQL semantics

Databricks documents the three-argument max_by aggregate for Databricks Runtime 18.1 and above, with a limit greater than zero and no larger than 100,000. Tied ordering values can produce non-deterministic results.

Check the current NEAREST BY documentation for the applicable runtime, SQL syntax, supported ranking expressions, and index requirements. Do not assume that every feature mentioned in the engineering article is available in every workspace or runtime configuration.

4. Benchmark the complete pipeline

Don’t measure only the vector-scoring operator.

Track:

• total job duration
• query throughput
• recall@K
• executor CPU and memory
• shuffle and spill
• index freshness
• cost per million queries
• downstream task quality

Compare the lakehouse-native approach with your current implementation using the same dataset, query distribution, and quality threshold.

5. Keep index maintenance in the design

An index stored in Delta tables is still an index.

Plan for data updates, index refreshes, clustering, schema changes, and the consistency requirements of the downstream pipeline. If the base vectors change frequently, the cost of keeping the index useful belongs in the total cost calculation.

Limitations and open questions

The reported evaluation range is substantial, but it doesn’t remove the need for workload-specific validation.

A few questions deserve particular attention:

• How does recall change as the index probes fewer or more candidates?
• How does throughput vary with embedding dimensions and increasing k?
• What is the cost of maintaining the index as the base collection changes?
• How does the exact path scale for collections beyond the published evaluation range?
• What happens to performance when many large batch jobs compete for the same cluster resources?
• How does the complete solution compare with a dedicated vector database under equivalent hardware, recall, and workload conditions?

The published engineering article explains the implementation and reports large-scale evaluations, but it does not establish a universal winner across all vector-search architectures.

That is the distinction engineers should preserve: a native SQL operator can simplify the architecture, but it doesn’t make benchmarking, indexing, and operational design unnecessary.

The engineering takeaway

The most interesting part of NEAREST BY isn’t simply that Databricks added another vector function.

It’s that batch vector search is becoming a first-class query-planning problem.

Instead of building a separate service for every vector-search use case, data teams can express nearest-neighbor matching as a SQL join and let the runtime optimize how the computation is distributed and executed.

Photon’s fused GEMM implementation targets the expensive numerical work. The IVF index reduces the candidate search space for approximate queries. Delta tables provide the storage foundation.

Together, these pieces offer a compelling option for workloads such as:

• large-scale entity resolution
• transaction-to-merchant matching
• product similarity
• offline recommendation generation
• embedding-based enrichment
• batch candidate retrieval for RAG and machine-learning pipelines

It won’t replace every online vector-search system. But it gives data engineers another option: keep batch similarity workloads close to the data and processing infrastructure they already operate.

The broader lesson is simple: choose the vector-search architecture around the workload, not around the assumption that every nearest-neighbor query needs a dedicated online endpoint.

Key takeaways

• NEAREST BY brings top-k vector search into SQL as a join operation in Databricks Runtime.
• APPROX and EXACT express different result requirements; approximation should be chosen deliberately.
• Photon’s fused, blocked GEMM operator targets computation and memory efficiency for large batches of vector comparisons, and becomes compute-bound from about 64 queries per batch on Databricks’ reference machine.
• The IVF index is an ordinary liquid-clustered Delta table, reducing the need for separate vector-serving infrastructure for supported batch workloads.
• Databricks reports evaluating up to five billion base vectors and ten million query rows, but actual results depend on workload and configuration.
• Runtime compatibility, recall, index maintenance, and total pipeline cost must be validated before production deployment.
• The best fit is likely to be large-scale analytical and batch-search workloads, while latency-sensitive interactive applications may still benefit from dedicated serving architectures.

Sources

• Databricks Blog: NEAREST BY Join: Scaling Vector Search in Databricks Runtime (October 5, 2026)
• Databricks SQL documentation: NEAREST BY clause
• Databricks SQL documentation: max_by aggregate function
• Databricks Runtime 18.1 release notes: max_by and min_by with optional limit