Move vectors into an indexed table

Applies to VAST Cluster 5.5.

How to move vectors into an indexed VAST DataBase table: from a table without an index, from pgvector, from a hosted vector database such as Pinecone, and from a Spark or Trino pipeline.

From a table without an index

You can't add a vector index to an existing table, including one created before VAST Cluster 5.5. To index vectors you already have, copy the rows into a new table that has one:

Create a new table with VECTOR CLUSTER BY on the vector column, with the same dimension. Choose the metric, quantization and columns now: you can't change them later (design a vector table, create an index).
Copy the rows: INSERT INTO <new> SELECT * FROM <old>, or export to Parquet and import it (ingest).
Wait until the new table's clustering grade is A or A+ (check the index first).
Point your searches at the new table. The SQL distance functions stay the same.

This example does all four steps on a small table. It reads the Query Engine endpoint from QE_ENDPOINT, your keys from AWS_ACCESS_KEY_ID and AWS_SECRET_ACCESS_KEY, and your database from VASTDB_BUCKET.

import os

import adbc_driver_manager.dbapi as dbapi
import adbc_driver_vastdb

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,
)
cur = conn.cursor()
schema = f'"{os.environ["VASTDB_BUCKET"]}/vs_ops"'
cur.execute(f"CREATE SCHEMA IF NOT EXISTS {schema}")

# The existing table: a vector column, no index.
cur.execute(f"CREATE TABLE IF NOT EXISTS {schema}.docs_old (id INTEGER, embedding VECTOR(FLOAT, 4))")
cur.execute(f"""
    INSERT INTO {schema}.docs_old VALUES
    (10, [1.0, 1.0, 1.0, 1.0]), (20, [2.0, 2.0, 2.0, 2.0]), (30, [7.0, 7.0, 7.0, 7.0])""")

# 1. The new table, with the index.
cur.execute(f"""
    CREATE TABLE IF NOT EXISTS {schema}.docs_new (id INTEGER, embedding VECTOR(FLOAT, 4))
    VECTOR CLUSTER BY embedding (metric = 'l2sq')""")

# 2. Copy the rows.
cur.execute(f"INSERT INTO {schema}.docs_new SELECT * FROM {schema}.docs_old")
print("copied:", cur.fetchall()[0][0])

copied: 3

The rows are searchable straight away, before clustering finishes:

cur.execute(f"""
    SELECT id, array_distance(embedding::FLOAT[4], [1.0, 1.0, 1.0, 1.0]::FLOAT[4]) AS dist
    FROM {schema}.docs_new
    ORDER BY dist
    LIMIT 2""")
for row_id, dist in cur.fetchall():
    print(row_id, round(dist, 3))

10 0.0
20 2.0

Clean up:

for table in ("docs_old", "docs_new"):
    cur.execute(f"DROP TABLE IF EXISTS {schema}.{table}")
cur.execute(f"DROP SCHEMA IF EXISTS {schema}")
cur.close()
conn.close()
print("dropped")

dropped

From pgvector

VAST DataBase doesn't speak the PostgreSQL protocol. Rewrite your clients to use ADBC (or the Python SDK), and map the pgvector pieces like this:

pgvector	VAST DataBase 5.5
`vector(d)`	`VECTOR(FLOAT, d)`
`<->` (L2 distance)	`array_distance`, on a table with `metric = 'l2sq'`
`<=>` (cosine distance)	`array_cosine_distance`, with `metric = 'cosine'`. Write the function: `<->` and `<=>` run, but don't use the index
`<#>` (negative inner product)	`array_negative_inner_product`, with `metric = 'ip'`
`CREATE INDEX ... USING hnsw` or `ivfflat`	`VECTOR CLUSTER BY` in `CREATE TABLE`. The index can only be defined when the table is created.
`SET ivfflat.probes`	The probe settings, passed as ADBC connection options (search session settings). SQL `SET` isn't supported.

From Pinecone and similar services

In the service	In VAST DataBase
Namespace or index	A database (bucket), schema and table
Metadata filters	SQL `WHERE` clauses on ordinary columns (filters)
Upsert of a vector	`DELETE` the row, then `INSERT` it again. Updating the vector column isn't supported.
The service's REST API	ADBC and SQL, or the Python SDK. There's no Pinecone-compatible API.

From Spark or Trino pipelines

Keep Spark (or Trino) for preparing data: write the embeddings to Parquet, import the files into an indexed table, and search with ADBC. The Trino and Spark connectors don't use the vector index (release notes), so search through the VAST Query Engine.

Move vectors into an indexed table

From a table without an index

From pgvector

From Pinecone and similar services

From Spark or Trino pipelines

See also