---
title: "Design a vector table"
slug: "dev-vector-search-guides-table-design"
description: "Plan your vector-indexed VAST DataBase table effectively with our guide, covering essential decisions on metrics, columns, and embedding models."
updated: 2026-10-30T18:19:06Z
published: 2026-10-30T18:19:06Z
canonical: "docs.vastdata.com/dev-vector-search-guides-table-design"
---

> ## Documentation Index
> Fetch the complete documentation index at: https://docs.vastdata.com/llms.txt
> Use this file to discover all available pages before exploring further.

# 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](/develop/docs/dev-database-guides-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](/develop/docs/dev-vector-search-guides-table-design#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](/develop/docs/dev-vector-search-guides-table-design#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](/develop/docs/dev-vector-search-guides-table-design#metric-element-type-and-quantization).

![Before you create a vector-indexed table, decide five things, because each is fixed once the table exists. One embedding model per table: a table has one indexed vector column. Every column you'll filter on: you can't add or drop columns later, so include vastdb_s3_path_auth now if you'll filter by S3 path. The metric your embedding model was trained for: l2sq, cosine or ip, which can't be changed. FLOAT or FLOAT16 elements and the dimension: FLOAT16 takes 2 bytes per element instead of 4, the dimension is fixed, and the column shouldn't be named vector. Quantization: the default is scalar_8bit for both settings, and it can't be changed. A vector index rules out sorted tables, partitioned tables, user-defined row IDs, replication, adding or dropping columns, renaming the vector column, and updating a vector value (delete and insert instead). Still possible: UPDATE of other columns, DELETE, renaming other columns, and WHERE filters on any column. To change any of the fixed choices, create a new table and load the data into it.](https://cdn.us.document360.io/89b91113-5dc6-49db-86ec-57b16dc8ce56/Images/Documentation/vs-table-design.svg?sv=2026-02-06&spr=https&st=2026-11-08T08%3A55%3A25Z&se=2026-11-08T09%3A55%3A25Z&sr=c&sp=r&sig=kDk318IEEjcGKZs0QSJwJ0f%2BWD9bT9c9nVSlx88YFWs%3D)

*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](/develop/docs/dev-vector-search-reference-limits).

| Decision | When you make it | Can you change it later? |
| --- | --- | --- |
| Give the table a vector index | When you create the table | No. You can't add an index to an existing table, or remove one. The VMS REST API's add-columns call can return success without creating one ([CI/CD and automation](/develop/docs/dev-vector-search-operations-ci-cd#you-cant-add-an-index-to-an-existing-table)). |
| Which column is indexed | When you create the table | No. There's one indexed vector column per table, and it can't be renamed. |
| The distance metric | When you create the table | No. |
| The quantization settings | When you create the table | No. A change through the VMS REST API is accepted but ignored ([limits](/develop/docs/dev-vector-search-reference-limits#index-rules)). |
| The columns | When you create the table | No. You can't add or drop columns in a vector-indexed table. Other columns can be renamed. |
| The `vastdb_s3_path_auth` column | When you create the table | No. It can't be added, changed or dropped later. |
| A row's vector | On insert | Not in place. Delete the row and insert it again; see [ingest](/develop/docs/dev-vector-search-guides-ingest#change-a-rows-embedding). |

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](/develop/docs/dev-vector-search-guides-ingest#keep-the-embedding-contract)).
- 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](/develop/docs/dev-vector-search-guides-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](/develop/docs/dev-vector-search-guides-filters#filter-with-where)).
- **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](/develop/docs/dev-vector-search-operations-security#row-filter-by-s3-path)). 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](/docs-5-5/docs/vast-cluster-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](/develop/docs/dev-vector-search-guides-query-design#metric-and-function)). For `ip`, read the [Python SDK](/develop/docs/dev-vector-search-integrations-python-sdk#inner-product-indexes-search-with-sql) 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](/develop/docs/dev-vector-search-operations-performance#space)). Both can be indexed. The dimension is fixed too: inserting a vector of another length fails ([the vector index](/develop/docs/dev-vector-search-concepts-index#objects)). Don't name the column `vector`: it's a reserved word.
- **Quantization:** start with the default, `scalar_8bit` for both settings. [Quantization](/develop/docs/dev-vector-search-guides-quantization) explains the two settings, the [allowed combinations](/develop/docs/dev-vector-search-guides-quantization#allowed-combinations) and how to find out what quantization costs you in recall.

The syntax for all three is in [create a vector index](/develop/docs/dev-vector-search-guides-create-an-index#what-you-choose).

## What you give up

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

| Structure | With a vector index |
| --- | --- |
| Sorted table | Not allowed |
| Partitioned table | Not allowed |
| User-defined row IDs | Not allowed, including a `vastdb_rowid` column |
| Replication | Not allowed: a vector-indexed table can't be replicated, and can't be created in a replicated path. |

The full matrix, with the errors the cluster returns, is in the VAST DataBase [limits](/develop/docs/dev-database-reference-limits#combining-table-structures). [Table structures](/develop/docs/dev-database-concepts-architecture-table-structures) explains what each layout is, and [data modeling](/develop/docs/dev-database-guides-data-modeling#vector-indexed-tables) 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](/develop/docs/dev-vector-search-concepts-index#lifecycle), [filters](/develop/docs/dev-vector-search-guides-filters#filter-with-where)).

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.

| Table | Holds | Rules |
| --- | --- | --- |
| **Vector table** | The ID, the embedding, the filter columns your searches need inside the search (such as language or tenant), and `vastdb_s3_path_auth` if you filter by permissions | Has the vector index, so it's fixed at creation |
| **Content table** | The same ID, the chunk text, and the columns that change or that you add later: titles, tags, model metadata, anything a downstream job writes | An ordinary table: you can add and drop columns, update rows and sort it. It can also be partitioned, but the VAST Query Engine can't read a partitioned table in 5.5, so read it with the Python SDK, Trino or Spark. |

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](/develop/docs/dev-database-guides-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](/develop/docs/dev-vector-search-reference-limits#index-rules))?
- Are you happy with the default quantization? If not, have you planned a comparison ([quantization](/develop/docs/dev-vector-search-guides-quantization#choose-a-setting))?
- 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](/develop/docs/dev-vector-search-guides-table-design#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](/develop/docs/dev-vector-search-guides-create-an-index): create the table.
- [Ingest](/develop/docs/dev-vector-search-guides-ingest): load the vectors.
- [Limits](/develop/docs/dev-vector-search-reference-limits): the evidence for every rule on this page.