Create a vector index

Applies to VAST Cluster 5.5. Examples use the VAST DataBase Python SDK 2.1.0.

In VAST DataBase, a vector is a column type, and a vector index is something you declare on one vector column when you create the table. This guide shows how to create a vector-indexed table with SQL through the VAST Query Engine and with the Python SDK, which options you have to choose, and which mistakes the cluster rejects.

Decide before you create the table. You can't add a vector index to an existing table, remove it, or change its distance metric later (5.5.0 release notes), and you can't change its quantization either. If you chose the wrong metric or quantization, create a new table and load the data again. Design a vector table walks through every create-time decision.

Run the code blocks in order in one Python session. Below each block is the output you should see.

What you choose

Before you start

You need a Query Engine endpoint, an access key pair and permissions for the VAST Query Engine. See setup, which also defines the environment variables these guides read:

export QE_ENDPOINT="http://<query-engine-vip>:9090"   # a Query Engine VIP, with the cluster's S3 port
export AWS_S3_ENDPOINT_URL="http://<endpoint>:9090"   # for the Python SDK
export AWS_ACCESS_KEY_ID="<access key>"
export AWS_SECRET_ACCESS_KEY="<secret key>"
export VASTDB_BUCKET="<database name>"

Install the SDK with its ADBC extra: pip install "vastdb[adbc]==2.1.0". See ADBC for the driver.

Create the table with SQL

Connect to the Query Engine and create a schema for this guide:

import os
import re
import adbc_driver_manager.dbapi as dbapi
import adbc_driver_vastdb

BUCKET = os.environ["VASTDB_BUCKET"]
SCHEMA = f'"{BUCKET}/vs_guide_create"'

conn = dbapi.connect(
    driver=adbc_driver_vastdb.get_driver_path(),
    db_kwargs={
        "vast.db.endpoint": os.environ["QE_ENDPOINT"],
        "vast.db.access_key": os.environ["AWS_ACCESS_KEY_ID"],
        "vast.db.secret_key": os.environ["AWS_SECRET_ACCESS_KEY"],
    },
    autocommit=True,
)

def sql(query):
    with conn.cursor() as cur:
        cur.execute(query)
        return cur.fetchall()

sql(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
print("ready")

ready

Pass autocommit=True. The DB-API wrapper adbc_driver_manager.dbapi.connect() turns autocommit off by default, and DDL that isn't committed before the connection closes is lost.

Now create the table. VECTOR CLUSTER BY names the vector column and sets the index options:

sql(f"""
CREATE TABLE IF NOT EXISTS {SCHEMA}.items (
    id BIGINT,
    title VARCHAR,
    embedding VECTOR(FLOAT, 8)
)
VECTOR CLUSTER BY embedding (
    metric = 'cosine',
    cluster_quantization = 'scalar_8bit',
    search_quantization = 'scalar_8bit'
)
""")
for row in sql(f"DESCRIBE {SCHEMA}.items"):
    print(row[:2])

('id', 'BIGINT')
('title', 'VARCHAR')
('embedding', 'VECTOR(FLOAT, 8)')

The general form is:

CREATE TABLE "<bucket>/<schema>".<table> (
    ...,
    <vec_col> VECTOR(FLOAT, <d>)
)
VECTOR CLUSTER BY <vec_col> (metric = '...', cluster_quantization = '...', search_quantization = '...');

• metric is required.
• You may leave out either or both quantization properties. The server then uses 'scalar_8bit'.
• No other properties are accepted. Anything else, including a property given twice, is an error.
• Property values are lower case. 'SCALAR_8BIT' is rejected.

DESCRIBE shows the column type but not the index. To see the index settings, read the table with the Python SDK, as in the next section.

Create the table with the Python SDK

create_table() takes a VectorIndexSpec. The vector column is an Arrow fixed-size list whose items are not nullable: pa.list_(pa.field("item", pa.float32(), nullable=False), <d>), or pa.float16() for half precision.

import pyarrow as pa
import vastdb
from vastdb import VectorIndexSpec

session = vastdb.connect()
DIM = 8
columns = pa.schema([
    ("id", pa.int64()),
    ("title", pa.utf8()),
    ("embedding", pa.list_(pa.field("item", pa.float32(), nullable=False), DIM)),
])

with session.transaction() as tx:
    schema = tx.bucket(BUCKET).create_schema("vs_guide_create", fail_if_exists=False)
    table = schema.create_table(
        "items_sdk",
        columns,
        vector_index=VectorIndexSpec(
            column="embedding",
            distance_metric="l2sq",            # "l2sq", "ip" or "cosine"
            cluster_quantization="scalar_8bit",
            search_quantization="scalar_8bit",
        ),
        fail_if_exists=False,
    )
    print(table.vector_index)

VectorIndex(column='embedding', distance_metric='l2sq', sql_distance_function='array_distance', cluster_quantization='scalar_8bit', search_quantization='scalar_8bit')

If you leave out the quantization arguments, the server defaults apply. The SDK 2.1.0 docstring lists only "l2sq" and "ip" as metrics, but the cluster also accepts "cosine" through the SDK. Prefer explicit values, so that the code says what the table is.

The SDK reads the index settings of any vector-indexed table, including one created with SQL. Here is the SQL table from the previous section, and one created with only a metric:

sql(f"CREATE TABLE IF NOT EXISTS {SCHEMA}.items_default (id BIGINT, embedding VECTOR(FLOAT, 8)) "
    "VECTOR CLUSTER BY embedding (metric = 'l2sq')")

with session.transaction() as tx:
    schema = tx.bucket(BUCKET).schema("vs_guide_create")
    for name in ("items", "items_default"):
        print(name, schema.table(name).vector_index)

items VectorIndex(column='embedding', distance_metric='cosine', sql_distance_function='array_cosine_distance', cluster_quantization='scalar_8bit', search_quantization='scalar_8bit')
items_default VectorIndex(column='embedding', distance_metric='l2sq', sql_distance_function='array_distance', cluster_quantization='scalar_8bit', search_quantization='scalar_8bit')

sql_distance_function is the SQL function the SDK uses when it searches this table. For ip tables it's the wrong one; see Python SDK before you call vector_search() on one.

Mistakes the cluster rejects

The cluster checks the index definition when you create the table. This helper runs a statement and returns the server's short message:

def try_sql(query):
    try:
        sql(query)
        return "OK"
    except Exception as e:
        msg = str(e).replace('\\"', '"')
        m = re.search(r'failed with: (.*?)", request_id', msg, re.S) or re.search(r"internal error: ([^)]*)\)", msg)
        return m.group(1) if m else msg

cases = {
    "index on a non-vector column":
        "CREATE TABLE {s}.bad1 (name VARCHAR) VECTOR CLUSTER BY name (metric = 'l2sq')",
    "metric given twice":
        "CREATE TABLE {s}.bad2 (id INT, embedding VECTOR(FLOAT, 4)) VECTOR CLUSTER BY embedding (metric = 'l2sq', metric = 'ip')",
    "unknown metric":
        "CREATE TABLE {s}.bad3 (id INT, embedding VECTOR(FLOAT, 4)) VECTOR CLUSTER BY embedding (metric = 'euclidean')",
    "no metric":
        "CREATE TABLE {s}.bad4 (id INT, embedding VECTOR(FLOAT, 4)) VECTOR CLUSTER BY embedding ()",
    "unknown property":
        "CREATE TABLE {s}.bad5 (id INT, embedding VECTOR(FLOAT, 4)) VECTOR CLUSTER BY embedding (metric = 'l2sq', cluster_num_bits = '4')",
    "upper-case quantization":
        "CREATE TABLE {s}.bad6 (id INT, embedding VECTOR(FLOAT, 4)) VECTOR CLUSTER BY embedding (metric = 'l2sq', cluster_quantization = 'SCALAR_8BIT')",
    "column named vector":
        "CREATE TABLE {s}.bad7 (id INT, vector VECTOR(FLOAT, 4)) VECTOR CLUSTER BY vector (metric = 'l2sq')",
    "vector index and a sort key":
        "CREATE TABLE {s}.bad8 (id INT, embedding VECTOR(FLOAT, 4), CONSTRAINT k SORT KEY (id)) VECTOR CLUSTER BY embedding (metric = 'l2sq')",
}
for label, statement in cases.items():
    print(f"{label}: {try_sql(statement.format(s=SCHEMA))}")

index on a non-vector column: Binder Error: Vector index column "name" must be a vector column, but has type: VARCHAR
metric given twice: Parser Error: Duplicate attribute: 'metric'
unknown metric: Invalid Argument
no metric: Parser Error: syntax error at or near ")"
unknown property: Invalid Argument
upper-case quantization: Invalid Argument
column named vector: Parser Error: syntax error at or near "vector"
vector index and a sort key: Invalid Argument

Invalid Argument is all the cluster says for a bad metric, property or quantization value, so check the definition against the table in What you choose. The helper's regular expression extracts the server's message from the driver's error text; errors explains the format and lists every message.

The Python SDK checks the same rules. One SDK message is different: create_table() with a vector index and a sort key, or with partitioning, fails with NotImplemented (501) and the message vector indexing cannot be combined with elysium sorting or ... with partitioning (see VAST DataBase limits). "Elysium" means sorted tables.

Create the table in the VAST Web UI or with the VMS REST API

Admin task: send to your platform team if you don't create tables yourself. In the VAST Web UI, a vector index is an Additional Capability of a new table, called Vector indexing; see Configuring Vector Indexing in the KB. Tell them:

• the table's columns, and which one is the vector column, with its element type and dimension;
• the distance metric your embedding model needs;
• the quantization, if you don't want the default scalar_8bit for both settings. For none, create the table with SQL, the Python SDK or the REST API instead.

To create the table from a pipeline with the VMS REST API, vastpy or Terraform, see CI/CD and automation. Unlike SQL and the SDK, the REST API has no default quantization: it needs both quantization fields.

What a vector index rules out

A vector-indexed table can't also be sorted or partitioned, among other things. What each create-time choice rules out is in design a vector table.

Clean up

for name in ("items", "items_default", "items_sdk", "bad1", "bad2", "bad3", "bad4", "bad5", "bad6", "bad7", "bad8"):
    sql(f"DROP TABLE IF EXISTS {SCHEMA}.{name}")
sql(f"DROP SCHEMA IF EXISTS {SCHEMA}")
conn.close()
print("cleaned up")

cleaned up

Next steps

• Design a vector table: the create-time decisions in one place.
• Ingest: load vectors into the table.
• Quantization: choose the quantization settings.
• Query design: write a search that uses the index.
• SQL reference and the Python SDK reference.