Vector optimization on Unity Catalog managed tables

Important

This feature is in Beta.

Vector optimization accelerates similarity searches over vector columns in Unity Catalog managed tables. When you run a vector search query with APPROX NEAREST BY, Azure Databricks can use vector optimization to narrow the search to data likely to contain the nearest matches. This reduces the amount of data scanned, especially for large tables.

To use vector optimization, create a vector index on a column and specify a scoring metric: cosine similarity, inner product, or L2 distance. Predictive Optimization automatically refreshes the index as your data changes. Eligible queries using the indexed column and matching comparison function use the optimization; other queries use brute-force search. For query syntax and examples, see NEAREST BY clause.

Requirements

To create a vector index on a table:

• Use Databricks Runtime 19 or above.
• You must have SELECT and MODIFY permissions on the table, USE CATALOG on its catalog, and USE SCHEMA on its schema.
• The table must be a Unity Catalog managed Delta Lake or Iceberg table.
• Create the index in the same catalog and schema as the table.
• Predictive Optimization must be enabled on the table to automatically refresh the index.
• Row tracking must be enabled before you create the index. See Row tracking in Databricks.
• The table must contain data when you create the index.
• The vector column must have the data type ARRAY<FLOAT>, and all vectors must have the same number of elements.

To use vector optimization in an APPROX NEAREST BY join, place the table with the vector index on the right and use the comparison function that matches the index's scoring metric. The query-vector table on the left must also have row tracking enabled.

Create a vector index

Use CREATE VECTOR INDEX to create an index for a column and scoring metric. For example, this statement creates an index with cosine similarity for the embedding column:

CREATE VECTOR INDEX main.analytics.documents_embedding_cosine_idx
ON main.analytics.documents (embedding)
OPTIONS (scoring_metric = 'cosine_similarity');

You can create multiple vector indexes on the same column to support different scoring metrics.

Each scoring metric corresponds to a query function:

Scoring metric	Query function
cosine_similarity	vector_cosine_similarity
inner_product	vector_inner_product
l2_distance	vector_l2_distance

Important

Use the query function that matches the index's scoring metric in your APPROX NEAREST BY query. If no matching index exists, the query runs without vector optimization.

Query data with vector optimization

Place the table with the vector index on the right side of an APPROX NEAREST BY join. You can filter the query-vector table on the left while still using vector optimization. The following batch join returns up to 10 documents per query vector:

SELECT
  q.query_id,
  d.document_id
FROM main.analytics.query_vectors AS q
INNER JOIN main.analytics.documents AS d
  APPROX NEAREST 10 BY SIMILARITY
    vector_cosine_similarity(q.embedding, d.embedding);

Index maintenance and status

After you create a vector index, Predictive Optimization automatically refreshes it as the table changes. Queries include newly appended rows immediately, even before those rows have been indexed.

To find the indexes associated with a table, run DESCRIBE EXTENDED:

DESCRIBE EXTENDED main.analytics.documents;

The # Indexes section of the output shows each index's name and fully qualified name. For example:

# Indexes
documents_embedding_cosine_idx   main.analytics.documents_embedding_cosine_idx

To inspect the index, use its fully qualified name:

DESCRIBE INDEX main.analytics.documents_embedding_cosine_idx;

The output includes information about the indexed column and scoring metric. For example:

property	value
Name	documents_embedding_cosine_idx
Columns	embedding
Scoring Metric	cosine_similarity

Predictive Optimization refreshes the index but doesn't automatically retrain it. If the distribution of vectors in your table changes significantly, retraining can help maintain search quality.

To retrain the index, drop and recreate it with the same column and scoring metric. For example:

DROP INDEX main.analytics.documents_embedding_cosine_idx;

CREATE VECTOR INDEX main.analytics.documents_embedding_cosine_idx
ON main.analytics.documents (embedding)
OPTIONS (scoring_metric = 'cosine_similarity');

Drop a vector index

Use DROP INDEX to remove a vector index:

DROP INDEX main.analytics.documents_embedding_cosine_idx;

After you drop the index, queries that relied on it run without that index's acceleration.

Note

Before you rename, drop, or change the type of an indexed column, drop every vector index on that column.

Limitations

• When you create the index, the table can have up to 1 billion rows, and vectors can contain up to 4,096 elements.

• In the following cases, queries use brute-force search instead of vector optimization:

  • The query uses time travel on the indexed table.
  • The table has row filters, column masks, or attribute-based access control (ABAC) policies.
  • The table is a OpenSharing source or target.

  This applies even if you add these policies or start sharing the table after you create the index.