Vector search in BigQuery

VECTOR_SEARCH finds the nearest embeddings in a table by Euclidean, cosine or dot product distance. Without an index it compares every row exactly. IVF and TreeAH indexes make it approximate, and on a small table they can cost more than brute force.

VECTOR_SEARCH takes a table with an ARRAY<FLOAT64> embedding column and a query vector, and returns the top_k rows whose vectors are closest. With no vector index it measures the distance to every row, so the answer is exact and the query reads the whole embedding column. With an IVF or TreeAH index it searches only some of the data, the answer becomes approximate, and whether that is cheaper depends on table size and how you set it up.

What the function returns

The base table must be a table, not a view or a WITH name. The query side can be a table, a query, or a single array through query_value. The examples here use two-dimensional vectors so you can check the distances by hand. Real embeddings have hundreds of dimensions.

CREATE TEMP TABLE animals AS
SELECT 'dog' AS id, 'mammal' AS kind, [1.0, 2.0] AS emb
UNION ALL SELECT 'wolf', 'mammal', [2.0, 4.0]
UNION ALL SELECT 'snake', 'reptile', [-2.0, 3.0]
UNION ALL SELECT 'lion', 'mammal', [2.0, -2.5]
UNION ALL SELECT 'tiger', 'mammal', [3.0, -2.0]
UNION ALL SELECT 'whale', 'mammal', [-5.0, -1.0];

SELECT query.q AS q, base.id AS neighbor, ROUND(distance, 3) AS distance
FROM VECTOR_SEARCH(
  TABLE animals, 'emb',
  (SELECT 'cat' AS q, [1.0, -1.0] AS emb UNION ALL SELECT 'dog', [1.0, 2.0]),
  top_k => 2)
ORDER BY q, distance

The output has a query struct (the query row, only for the batch form), a base struct (the matched row) and a distance column. Google’s reference says the order of the output isn’t guaranteed, so add ORDER BY. top_k defaults to 10, and a negative value returns every row:

CREATE TEMP TABLE animals AS
SELECT 'dog' AS id, 'mammal' AS kind, [1.0, 2.0] AS emb
UNION ALL SELECT 'wolf', 'mammal', [2.0, 4.0]
UNION ALL SELECT 'snake', 'reptile', [-2.0, 3.0]
UNION ALL SELECT 'lion', 'mammal', [2.0, -2.5]
UNION ALL SELECT 'tiger', 'mammal', [3.0, -2.0]
UNION ALL SELECT 'whale', 'mammal', [-5.0, -1.0];

-- top_k defaults to 10, so this returns all 6 rows; negative top_k returns every row too
SELECT base.id AS neighbor, ROUND(distance, 3) AS distance
FROM VECTOR_SEARCH(TABLE animals, 'emb', query_value => [0.0, 0.0])
ORDER BY distance, neighbor

Distance types

distance_type is EUCLIDEAN (default), COSINE or DOT_PRODUCT. Smaller is always closer, which has a consequence for dot product: the function returns the negated dot product, so the best match has the most negative distance.

CREATE TEMP TABLE animals AS
SELECT 'dog' AS id, 'mammal' AS kind, [1.0, 2.0] AS emb
UNION ALL SELECT 'wolf', 'mammal', [2.0, 4.0]
UNION ALL SELECT 'snake', 'reptile', [-2.0, 3.0]
UNION ALL SELECT 'lion', 'mammal', [2.0, -2.5]
UNION ALL SELECT 'tiger', 'mammal', [3.0, -2.0]
UNION ALL SELECT 'whale', 'mammal', [-5.0, -1.0];

SELECT metric, base.id AS neighbor, distance
FROM (
  SELECT 'EUCLIDEAN' AS metric, * FROM VECTOR_SEARCH(TABLE animals, 'emb', query_value => [1.0, 2.0], top_k => 3, distance_type => 'EUCLIDEAN')
  UNION ALL
  SELECT 'COSINE', * FROM VECTOR_SEARCH(TABLE animals, 'emb', query_value => [1.0, 2.0], top_k => 3, distance_type => 'COSINE')
  UNION ALL
  SELECT 'DOT_PRODUCT', * FROM VECTOR_SEARCH(TABLE animals, 'emb', query_value => [1.0, 2.0], top_k => 3, distance_type => 'DOT_PRODUCT')
)
ORDER BY metric, distance, neighbor

For the query [1, 2], wolf is [2, 4], twice as long and in the same direction. Cosine distance puts dog and wolf at 0. Euclidean puts wolf 2.24 away. Dot product ranks wolf first, at -10, because it is longer. Which one you want depends on how your embedding model was trained. Cosine ignores length, and most text embedding models are meant to be compared that way.

The same difference is visible without VECTOR_SEARCH, with plain functions:

-- magnitude matters to Euclidean distance but not to cosine distance
WITH docs AS (
  SELECT 'short note' AS id, [1.0, 1.0] AS emb
  UNION ALL SELECT 'long report, same topic', [10.0, 10.0]
  UNION ALL SELECT 'other topic', [1.0, 0.0]
)
SELECT id,
  ROUND(EUCLIDEAN_DISTANCE(emb, [2.0, 2.0]), 3) AS euclidean,
  ROUND(COSINE_DISTANCE(emb, [2.0, 2.0]), 3) AS cosine
FROM docs
ORDER BY id

EUCLIDEAN_DISTANCE, COSINE_DISTANCE and ML.DISTANCE(a, b, type) compute the distance between two arrays in a normal query, for example to re-rank results or compare two columns:

SELECT
  EUCLIDEAN_DISTANCE([1.0, 2.0], [4.0, 6.0]) AS euclidean,
  ML.DISTANCE([1.0, 2.0], [4.0, 6.0], 'MANHATTAN') AS manhattan,
  ROUND(COSINE_DISTANCE([1.0, 0.0], [0.0, 1.0]), 3) AS cosine_orthogonal,
  ROUND(COSINE_DISTANCE([1.0, 0.0], [-1.0, 0.0]), 3) AS cosine_opposite,
  ROUND(COSINE_DISTANCE([1.0, 2.0], [10.0, 20.0]), 3) AS cosine_same_direction

ML.DISTANCE is documented for EUCLIDEAN, MANHATTAN and COSINE. In our test it also accepted 'DOT_PRODUCT' and returned the positive dot product (16 for [1, 2] and [4, 6]), unlike VECTOR_SEARCH, which negates it. Don’t mix the two without checking the sign.

Filtering before or after the search

If the base table argument is a query, its WHERE clause runs before the search. A WHERE on the outer query runs after. The two give different answers when the filter removes some of the nearest rows:

CREATE TEMP TABLE animals AS
SELECT 'dog' AS id, 'mammal' AS kind, [1.0, 2.0] AS emb
UNION ALL SELECT 'wolf', 'mammal', [2.0, 4.0]
UNION ALL SELECT 'snake', 'reptile', [-2.0, 3.0]
UNION ALL SELECT 'lion', 'mammal', [2.0, -2.5]
UNION ALL SELECT 'tiger', 'mammal', [3.0, -2.0]
UNION ALL SELECT 'whale', 'mammal', [-5.0, -1.0];

SELECT base.id AS neighbor, base.kind, ROUND(distance, 3) AS distance
FROM VECTOR_SEARCH(
  (SELECT * FROM animals WHERE kind = 'mammal'), 'emb',
  query_value => [-2.0, 2.0], top_k => 2)
ORDER BY distance

CREATE TEMP TABLE animals AS
SELECT 'dog' AS id, 'mammal' AS kind, [1.0, 2.0] AS emb
UNION ALL SELECT 'wolf', 'mammal', [2.0, 4.0]
UNION ALL SELECT 'snake', 'reptile', [-2.0, 3.0]
UNION ALL SELECT 'lion', 'mammal', [2.0, -2.5]
UNION ALL SELECT 'tiger', 'mammal', [3.0, -2.0]
UNION ALL SELECT 'whale', 'mammal', [-5.0, -1.0];

-- the filter runs after the top 2 are chosen: the snake is nearest, a mammal filter leaves one row
SELECT base.id AS neighbor, base.kind, ROUND(distance, 3) AS distance
FROM VECTOR_SEARCH(TABLE animals, 'emb', query_value => [-2.0, 2.0], top_k => 2)
WHERE base.kind = 'mammal'
ORDER BY distance

The post-filter gets only one row because the top 2 were the snake and the dog, and the snake isn’t a mammal. Only SELECT, FROM and WHERE are allowed in the base query, and Google advises against filtering on the embedding column itself. With an index, a pre-filter on columns that are stored in the index (STORING (...) in CREATE VECTOR INDEX) is applied before the index search. A filter on any other column is applied afterwards, and Google notes the result can then contain fewer than top_k rows, even none.

Bad vectors fail the whole query

Every vector has to have the same length, and the error comes from ML.DISTANCE, which VECTOR_SEARCH uses underneath:

CREATE TEMP TABLE animals AS
SELECT 'dog' AS id, [1.0, 2.0] AS emb UNION ALL SELECT 'wolf', [2.0, 4.0];

SELECT base.id
FROM VECTOR_SEARCH(TABLE animals, 'emb', query_value => [1.0, 2.0, 3.0], top_k => 2)

A row whose embedding is NULL fails in the same way, with the same message about array lengths, not a message about NULL:

CREATE TEMP TABLE animals AS
SELECT 'dog' AS id, [1.0, 2.0] AS emb
UNION ALL SELECT 'wolf', [2.0, 4.0]
UNION ALL SELECT 'ghost', CAST(NULL AS ARRAY<FLOAT64>);

SELECT base.id
FROM VECTOR_SEARCH(TABLE animals, 'emb', query_value => [1.0, 2.0], top_k => 5)

A NULL element inside an array ([1.0, NULL]) can’t be stored in a table (“Array cannot have a null element”) and is rejected by the distance functions (“Unexpected NULL element in input array”). Filter out failed embeddings before you search, or before you build an index. Cosine distance against a zero vector is an error too (“Cannot compute cosine distance against zero vector”). A single all-zero row in the base table fails a COSINE search.

SELECT EUCLIDEAN_DISTANCE([1.0, NULL], [4.0, 6.0]) AS d

SELECT COSINE_DISTANCE([1.0, 0.0], [0.0, 0.0]) AS d

Brute force versus an index

Without an index, BigQuery reads the whole embedding column and computes every distance. That is exact. It is the only mode that always gives the true neighbors. Our 200,000 row test below took 2.6 seconds of slot time this way.

CREATE VECTOR INDEX offers two types:

CREATE VECTOR INDEX docs_idx
ON mydataset.docs(embedding)
OPTIONS (index_type = 'IVF', distance_type = 'COSINE', ivf_options = '{"num_lists": 100}');

CREATE VECTOR INDEX docs_idx
ON mydataset.docs(embedding)
OPTIONS (index_type = 'TREE_AH', distance_type = 'COSINE', tree_ah_options = '{"leaf_node_embedding_count": 1000}');

IVF clusters the vectors into lists with k-means and searches the lists nearest to the query. TreeAH compresses the vectors with product quantization (ScaNN), finds candidates from the compressed form and re-scores them with the exact vectors. Google recommends IVF for small query batches and TreeAH for batches of hundreds of query vectors. The index is built in the background, and queries run without it until coverage_percentage in INFORMATION_SCHEMA.VECTOR_INDEXES reaches 100. Indexing needs a table of at least 10 MB. A smaller table gets no index, and the search says BASE_TABLE_TOO_SMALL.

What we measured

We built a table of 200,000 rows with 64-dimensional random vectors (105.6 MB) and ran top_k => 10 queries with Euclidean distance. The data has no cluster structure, which is the hardest case for an approximate index, so treat the recall numbers as a floor. Recall is the share of the exact top 10 that the index also returned, averaged over 20 queries.

On a table this size the index doesn’t save anything: the indexed queries processed 5 to 14% more bytes than brute force, because on-demand billing counts the base table, the index and the query, and they used more slot time. Google says gains grow with table size, and 200,000 rows is small. The default search width was a bad trade on this data. The IVF default found 16% of the true neighbors, and reading half the lists found 93% at the same bytes. Set fraction_lists_to_search and measure recall against a brute-force run on a sample of your own queries. TreeAH ignored the index for a single query. The job statistics said ESTIMATED_PERFORMANCE_GAIN_TOO_LOW, and the query ran as brute force.

An index also takes space. The IVF index was 28.9 MB and the TreeAH index 4.1 MB, both billed as active storage.

Check whether the index was used

VECTOR_SEARCH never tells you in the result. Look at the job’s vectorSearchStatistics.indexUsageMode (FULLY_USED, PARTIALLY_USED or UNUSED) and indexUnusedReasons:

bq show --format=prettyjson -j JOB_ID

The reasons we saw: PENDING_INDEX_CREATION (the index exists but isn’t populated yet), INDEX_SUPPRESSED_BY_FUNCTION_OPTION (we passed use_brute_force), and ESTIMATED_PERFORMANCE_GAIN_TOO_LOW. Passing use_brute_force together with fraction_lists_to_search is an error:

CREATE TEMP TABLE animals AS
SELECT 'dog' AS id, 'mammal' AS kind, [1.0, 2.0] AS emb
UNION ALL SELECT 'wolf', 'mammal', [2.0, 4.0]
UNION ALL SELECT 'snake', 'reptile', [-2.0, 3.0]
UNION ALL SELECT 'lion', 'mammal', [2.0, -2.5]
UNION ALL SELECT 'tiger', 'mammal', [3.0, -2.0]
UNION ALL SELECT 'whale', 'mammal', [-5.0, -1.0];

SELECT base.id FROM VECTOR_SEARCH(TABLE animals, 'emb', query_value => [1.0, 2.0], top_k => 3, options => '{"use_brute_force": true, "fraction_lists_to_search": 0.5}')

What it costs

On-demand pricing bills the bytes of the base table, the index and the query, as for any query. Building and refreshing an index is free up to a per-organization size limit, after which you need your own reservation. Index storage is billed as active storage. Google lists VECTOR_SEARCH as not accelerated by BI Engine. The project that runs the query must be the project that holds the base table.

Generating the embeddings is a separate cost: AI.GENERATE_EMBEDDING and AI.EMBED call a Vertex AI model, billed there. See AI functions. VECTOR_SEARCH itself calls no model, so the examples above are free to run. If you want a keyword match instead of a semantic one, that is SEARCH.