---
title: "Move vectors into an indexed table"
slug: "dev-vector-search-guides-migrate"
description: "Learn how to move vectors into an indexed VAST DataBase table from various sources, including pgvector and hosted databases like Pinecone."
updated: 2026-10-28T02:14:59Z
published: 2026-10-28T02:14:59Z
canonical: "docs.vastdata.com/dev-vector-search-guides-migrate"
---

> ## 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.

# 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:

1. 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](/develop/docs/dev-vector-search-guides-table-design), [create an index](/develop/docs/dev-vector-search-guides-create-an-index)).
2. Copy the rows: `INSERT INTO <new> SELECT * FROM <old>`, or export to Parquet and import it ([ingest](/develop/docs/dev-vector-search-guides-ingest)).
3. Wait until the new table's clustering grade is A or A+ ([check the index first](/develop/docs/dev-vector-search-operations-performance#check-the-index-first)).
4. 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](/develop/docs/dev-vector-search-reference-configuration)). 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](/develop/docs/dev-vector-search-guides-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](/develop/docs/dev-vector-search-release-notes#known-limitations)), so search through the VAST Query Engine.

## See also

- [Release notes](/develop/docs/dev-vector-search-release-notes): what 5.5 added.
- [Design a vector table](/develop/docs/dev-vector-search-guides-table-design), [create an index](/develop/docs/dev-vector-search-guides-create-an-index) and [ingest](/develop/docs/dev-vector-search-guides-ingest).
- [Limits](/develop/docs/dev-vector-search-reference-limits): what can't be changed after creation.