Design a vector table

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

This guide helps you plan a vector-indexed VAST DataBase table before you create it. Almost everything about a vector table is decided at CREATE TABLE: the index, its metric and quantization, and the columns. Most of it can't be changed later, and a vector index rules out other table structures. So decide from the searches you expect to run, then create the table once.

It collects the create-time decisions from the other Vector Search pages in one place, and links to them for the details. For the same kind of decision on any VAST DataBase table, see data modeling.

Start from your searches

Answer these before you write the CREATE TABLE statement:

• Which embedding model? One table holds the vectors of one model. See one table per embedding model.
• Which columns will you filter on? Language, date, tenant, document ID, model version: every column a WHERE clause will use has to be in the table from the start. See columns to plan up front.
• Who may see which rows? If users may see only the chunks of documents they can read, the table needs a vastdb_s3_path_auth column, and that too can only be added at creation.
• Which metric does the model need? It's fixed for the life of the table. See metric, element type and quantization.

Decide these before CREATE TABLE: each is fixed once the table exists.

Decisions you can't undo

Important to note: you make each of these decisions when you create the table, and you can't undo it later. The evidence for each is in limits.

If you need to change any of these, create a new table with the new design and load the data into it again.

One table per embedding model

A table has one indexed vector column, and distances between vectors from different models mean nothing. So:

• Keep the vectors of one model, model version and dimension in one table, and use the same model for the query vectors (ingest).
• Record the model name and version, in a column or in your deployment configuration.
• To move to a new model, create a new table, load the new embeddings into it, and switch your searches over when it's ready. The old table keeps serving until then.

Columns to plan up front

Because you can't add or drop columns, plan every column the application will need:

• Filter columns: everything your WHERE clauses use (filters).
• A tenant column, if one table serves several tenants. If nearly every search filters on one tenant, consider a table per tenant instead (filters).
• The model version, so you can tell which embeddings a row has.
• vastdb_s3_path_auth, if you'll filter results by the user's permission to read the source documents (security). A vector-indexed table can have it.
• Payload columns your application returns with each result, such as a title or a chunk of text.

Here's what the cluster rejects on an existing vector-indexed table. Set up a table first:

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_table_design"'

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()

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

sql(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
sql(f"""CREATE TABLE IF NOT EXISTS {SCHEMA}.chunks (
          id BIGINT, chunk_id INTEGER, language VARCHAR, published DATE, title VARCHAR,
          embedding VECTOR(FLOAT, 4))
        VECTOR CLUSTER BY embedding (metric = 'cosine')""")
sql(f"""INSERT INTO {SCHEMA}.chunks VALUES
    (1, 0, 'en', DATE '2026-01-10', 'apples', [0.9, 0.1, 0.0, 0.0]::FLOAT[4])""")
print(sql(f"SELECT count(*) FROM {SCHEMA}.chunks"))

[(1,)]

Then try to change it:

print("add column:          ", try_sql(f"ALTER TABLE {SCHEMA}.chunks ADD COLUMN url VARCHAR"))
print("drop column:         ", try_sql(f"ALTER TABLE {SCHEMA}.chunks DROP COLUMN title"))
print("rename vector column:", try_sql(f"ALTER TABLE {SCHEMA}.chunks RENAME COLUMN embedding TO emb"))
print("rename other column: ", try_sql(f"ALTER TABLE {SCHEMA}.chunks RENAME COLUMN title TO heading"))

add column:           Invalid Argument
drop column:          Invalid Argument
rename vector column: Tried to drop a column which cannot be dropped
rename other column:  OK

Adding and dropping columns is a known limitation in the 5.5.0 release notes. Renaming a column other than the vector column works.

Metric, element type and quantization

• Metric: use the one your embedding model was trained for: l2sq (Euclidean), cosine or ip (inner product). Each has its own distance function in SQL (query design). For ip, read the Python SDK note before you plan to search with vector_search().
• Element type: VECTOR(FLOAT, <d>) stores 4 bytes per element and VECTOR(FLOAT16, <d>) stores 2, so FLOAT16 halves the space the vectors take (performance and sizing). Both can be indexed. The dimension is fixed too: inserting a vector of another length fails (the vector index). Don't name the column vector: it's a reserved word.
• Quantization: start with the default, scalar_8bit for both settings. Quantization explains the two settings, the allowed combinations and how to find out what quantization costs you in recall.

The syntax for all three is in create a vector index.

What you give up

A vector index rules out other table structures, for the life of the table:

The full matrix, with the errors the cluster returns, is in the VAST DataBase limits. Table structures explains what each layout is, and data modeling how to choose between them.

You can still UPDATE the other columns, DELETE rows, rename columns other than the vector column, and filter on any column with WHERE (the vector index, filters).

If a workload needs both nearest-neighbour search and, say, a sorted table for range scans, keep them in two tables, as the next section describes.

Split the data over two tables

Because a vector-indexed table can't gain or lose columns and can't be sorted or partitioned, a common design keeps only what the search needs in the vector table, and everything else in a second, ordinary table. The two tables share an ID column.

Use an ordinary column, such as id BIGINT, as the link. User-defined row IDs (vastdb_rowid) aren't allowed on a vector-indexed table.

Retrieve in two steps. Joins aren't supported in the VAST Query Engine in 5.5. A JOIN, or IN (SELECT …) with a subquery, fails with The operator 'HASH_JOIN' type=32 is currently not supported. So run the search on the vector table, then fetch the matching rows from the content table by ID:

q = "[...]::FLOAT[128]"                       # the query embedding
with conn.cursor() as cur:
    cur.execute(f"""SELECT id, array_distance(e::FLOAT[128], {q}) AS dist
                    FROM {SCHEMA}.vectors ORDER BY dist LIMIT 10""")
    hits = cur.fetchall()                     # [(id, dist), ...], nearest first
    ids = [row_id for row_id, _ in hits]
    cur.execute(f"SELECT id, chunk FROM {SCHEMA}.content WHERE "
                + " OR ".join(f"id = {i}" for i in ids))
    chunks = dict(cur.fetchall())
results = [(i, d, chunks.get(i)) for i, d in hits]   # keep the search order

Write the lookup as id = … OR id = …. Through the Query Engine in 5.5, WHERE id IN (…) with five or more values fails with the same HASH_JOIN error. From the Python SDK, table.select(predicate=table["id"].isin(ids)) works with any number of IDs.

Put the filters the search must satisfy on the vector table. A WHERE clause in the search keeps reading clusters until it has enough rows that pass, so you get the full LIMIT. A filter applied afterwards to the content table only removes rows from the results you already have. With a filter that one row in ten passes, ten results per search become about one on average. Fetching 30 times as many candidates from the vector table makes up the difference, at the cost of a larger search. So keep any condition that results must meet, including tenant and permissions, in the vector table, and use content-table columns for what you return or for filters that don't need to fill the LIMIT.

Write both tables in one transaction. One Python SDK transaction can insert into both tables. If it fails, neither table keeps the rows (transactions in practice).

What it costs: two queries per search instead of one, and your application, not the cluster, keeps the two tables in step: delete from both, and when a vector changes, delete and reinsert it in the vector table only.

Before you create the table

• Is this table for one embedding model, model version and dimension?
• Do you know the metric the model needs?
• Is every column your filters and results will use in the table definition, including a tenant or model-version column if you need one?
• If users may see only some documents, is the vastdb_s3_path_auth column in the definition?
• FLOAT or FLOAT16? Is the dimension within the maximum for your cluster quantization (limits)?
• Are you happy with the default quantization? If not, have you planned a comparison (quantization)?
• Does the data need to be sorted, partitioned or replicated, or will you add columns later? If so, keep that part in a second table (split the data over two tables).
• Is the vector column named something other than vector?

Clean up

sql(f"DROP TABLE IF EXISTS {SCHEMA}.chunks")
sql(f"DROP SCHEMA IF EXISTS {SCHEMA}")
conn.close()
print("cleaned up")

cleaned up

Next steps

• Create a vector index: create the table.
• Ingest: load the vectors.
• Limits: the evidence for every rule on this page.