SQL reference for VAST Vector Search

Applies to VAST Cluster 5.5.

This page lists the SQL that VAST Vector Search accepts in VAST Cluster 5.5: the vector column types, the CREATE TABLE ... VECTOR CLUSTER BY grammar, the distance functions, the query shape that uses the index, and the errors you get when a statement isn't allowed. You send this SQL to the VAST Query Engine through the ADBC driver.

For the rest of the SQL the VAST Query Engine supports, see the VAST DataBase SQL reference.

Vector column types

A vector is a column type on VAST DataBase tables. Its length (the dimension) is fixed when you create the table.

Rules for vector values:

• The value's length must match the column's dimension. An insert with the wrong length fails.
• FLOAT[] and INTEGER[] lists aren't vector columns, and you can't use them with VECTOR CLUSTER BY.
• Trino and Spark have no type for a vector column; see limits.

-- fails: wrong length (the table's vector column is VECTOR(FLOAT, 8))
INSERT INTO "bucket/schema".docs VALUES (5, 'e', [1,2,3,4,5,6,7]::FLOAT[7]);
-- Conversion Error: Cannot cast array of size 7 to array of size 8

CREATE TABLE with a vector index

You create the vector index when you create the table, with VECTOR CLUSTER BY. This is the grammar VAST Cluster 5.5 accepts:

CREATE [OR REPLACE] TABLE [IF NOT EXISTS] "bucket/schema".name (
    col1 TYPE,
    embedding VECTOR(FLOAT, d),
    ...
)
VECTOR CLUSTER BY embedding (
    metric = 'l2sq' | 'cosine' | 'ip',
    cluster_quantization = 'none' | 'scalar_8bit',
    search_quantization  = 'none' | 'scalar_8bit'
);

Rules:

• There's no CREATE VECTOR INDEX, DROP VECTOR INDEX or ALTER VECTOR INDEX. To remove the index, drop the table.
• You can index one vector column per table. The table can have other, un-indexed vector columns.
• metric is required, and you can give it only once. A second metric gives Parser Error: Duplicate attribute: 'metric'.
• The column in VECTOR CLUSTER BY must be a VECTOR(...) column, not VARCHAR, INTEGER or a list.
• metric, cluster_quantization and search_quantization are the only properties. Any other property is rejected.
• If search_quantization is 'none', cluster_quantization must be 'none' too. Quantization has the table of allowed pairs.
• You can't change the metric or the quantization after you create the table. ALTER TABLE ... ALTER COLUMN ... SET DATA TYPE on the indexed column fails with This functionality is not implemented yet. The full list of create-time rules is on limits.
• vector is a reserved word in the VAST Query Engine, so don't name your vector column vector. Use a name such as embedding.

For the step-by-step version, see create an index.

DESCRIBE and SHOW

DESCRIBE "bucket/schema".name;
SHOW "bucket/schema".name;

Both list the columns. The vector column shows as VECTOR(FLOAT, d) (or VECTOR(HALF_FLOAT, d)), but nothing shows the index itself: its metric and quantization aren't listed. There's no SHOW VECTOR INDEX.

To read a table's index settings, use the Python SDK's table.vector_index. It returns the column, metric, distance function and both quantization settings. The same settings are in the table's Arrow schema as the SDK returns it (table.arrow_schema.metadata), under the key VAST:vector_index_spec. The ADBC driver's adbc_get_table_schema() doesn't return them.

Changing data (DML)

Use the standard INSERT, UPDATE and DELETE commands of the VAST Query Engine. On a vector-indexed table:

• You can update the other (metadata) columns, and delete rows.
• You can't update the vector column. To change a vector, delete the row and insert it again with the new vector.
• You can't add or drop columns (ALTER TABLE ... ADD COLUMN and DROP COLUMN fail with Invalid Argument).
• You can't rename the indexed vector column. It fails with Tried to drop a column which cannot be dropped.

Distance functions

The operators <-> and <=> compute the same distances as array_distance and array_cosine_distance, but a search written with them doesn't use the index. <#> isn't accepted.

All four functions work on any vector column, indexed or not. The index is used only when the function in ORDER BY matches the table's metric, as in the "Use with metric" column. The examples here cast the column to a fixed-size array of the same dimension (embedding::FLOAT[8]), as the Python SDK does.

Inner product: for an ip table, order by array_negative_inner_product. array_inner_product returns the plain inner product, so ORDER BY ... ASC puts the least similar rows first, and the index isn't used. The Python SDK's vector_search() makes this mistake on ip tables; see Python SDK.

Query shape that uses the index

The VAST Query Engine uses the vector index only for a query of this shape:

SELECT id, title,
       array_distance(embedding::FLOAT[8], [1,1,1,1,1,1,1,1]::FLOAT[8]) AS dist
FROM "bucket/schema".docs
WHERE title <> 'x'
ORDER BY dist
LIMIT 10;

• ORDER BY has exactly one key: the distance expression, in ascending order (the default).
• LIMIT k is present.
• There's no OFFSET. A query with OFFSET still runs and returns results, but it doesn't use the index.
• The query reads one vector-indexed table. WHERE filters and extra columns are allowed.

Any other shape runs as a full scan of the table and returns exact results, which can be slow on a large table. EXPLAIN shows which you have: the plan of a query that uses the index contains a VAST_VEC_INDEX_SCAN step, while a full scan shows only TOP_N over a VAST_SCAN of the table. Query design explains the shape rules, with the rewrites for queries that don't use the index. For filters, see filters.

Aggregating the search results (GROUP BY, count(DISTINCT ...)) runs into the Query Engine's aggregate restrictions; see the VAST DataBase SQL reference.

Errors you'll see

These are the errors VAST Cluster 5.5 returns for statements that aren't allowed.

-- not a vector column
CREATE TABLE t (name VARCHAR) VECTOR CLUSTER BY name (metric = 'l2sq');
-- Binder Error: Vector index column "name" must be a vector column, but has type: VARCHAR

-- a list, not a vector
CREATE TABLE t (arr FLOAT[]) VECTOR CLUSTER BY arr (metric = 'l2sq');
-- Binder Error: Vector index column "arr" must be a vector column, but has type: FLOAT[]

-- a fixed-size array column
CREATE TABLE t (arr INTEGER[4]) VECTOR CLUSTER BY arr (metric = 'l2sq');
-- Binder Error: ARRAY type is not supported in table definitions. Column "arr" has type: INTEGER[4]

-- metric given twice
CREATE TABLE t (e VECTOR(FLOAT, 4)) VECTOR CLUSTER BY e (metric = 'l2sq', metric = 'ip');
-- Parser Error: Duplicate attribute: 'metric'

-- no property list
CREATE TABLE t (e VECTOR(FLOAT, 4)) VECTOR CLUSTER BY e;
-- Parser Error: syntax error at end of input

-- no such statement
CREATE VECTOR INDEX ix ON t (e);
SHOW VECTOR INDEX t;
-- Parser Error: syntax error at or near "VECTOR"

-- no such function (use array_cosine_distance)
SELECT id, cosine_similarity(e, [1,0,0,0]::FLOAT[4]) AS sim FROM t ORDER BY sim DESC LIMIT 10;
-- Catalog Error: Scalar Function with name cosine_similarity does not exist!

-- ORDER BY a distance without LIMIT
SELECT id FROM t ORDER BY array_distance(e::FLOAT[4], [1,0,0,0]::FLOAT[4]);
-- The operator 'ORDER_BY' type=1 is currently not supported for this usage.

The cluster returns a bare Invalid Argument (for example internal error: Invalid Argument) when:

• the metric is missing or isn't l2sq, cosine or ip
• a quantization value isn't none or scalar_8bit
• the quantization pair isn't allowed (scalar_8bit/none)
• there's an unknown property in VECTOR CLUSTER BY
• you update the vector column, or add or drop a column

The message doesn't say which of these it is, so check your statement against the rules on this page. For more errors and fixes, see the error index.

See also

• Python SDK reference: vector index
• Configuration: session settings for search
• Limits
• Cheat sheet