Skip to main content
Last updated on

Vector optimization on Unity Catalog managed tables

Beta

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, 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:

SQL
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

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:

SQL
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:

SQL
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:

SQL
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

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:

SQL
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:

SQL
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.

On this page