PixeltablePixeltable Logo

How to

How do I migrate from pgvector to an embedding index on the table?

pgvector stores vectors next to rows in Postgres. Moving off it means keeping those rows and letting an embedding index on the source column replace the vector column and the ANN query you wrote. You still need the original text or image. A COPY of the vector column without the source cannot rebuild search under a new model.

Updated · Part of How do I ... in Python?

Before you start

About 20 min.

  • Python 3.11+
  • pip install -U 'pixeltable[serve]'
  • pxt init in the project root
  • sentence-transformers for all-MiniLM-L6-v2

1. Copy the source column, not the vector column

SELECT the id, the text (or image URI), and filters. Leave the vector column behind. If you only have vectors, you cannot switch models later without the source.

import pixeltable as pxt
from pixeltable.functions.huggingface import sentence_transformer


TableModel = pxt.model_base()
text_embed = sentence_transformer.using(model_id='all-MiniLM-L6-v2')


class Notes(TableModel, name='notes'):
    pg_id: pxt.String
    content: pxt.String
    tenant: pxt.String | None

2. Replace the vector column with an embedding index

The index lives on content. cosine is the default metric. You do not declare a vector(n) column or write CREATE INDEX ON embedding USING ivfflat.

class Notes(TableModel, name='notes'):
    pg_id: pxt.String
    content: pxt.String
    tenant: pxt.String | None
    __indexes__ = [
        pxt.EmbeddingIndex(content, string_embed=text_embed, metric='cosine'),
    ]


# pxt schema update app.py pgvector_notes -f

3. Insert the copied rows

Load the SELECT result (CSV, Parquet, or dicts) and insert. Each row is embedded. Tenant stays a filter column, not a separate table.

notes = pxt.get_table('pgvector_notes.notes')
notes.insert([
    {
        'pg_id': '42',
        'content': 'Theodore Roosevelt won the 1904 US presidential election.',
        'tenant': 'history',
    },
])

4. Rank with similarity instead of an ANN SQL operator

where() restricts tenant. similarity(string=) replaces <=> or cosine_distance in SQL. Limit is the number of neighbors.

@pxt.query
def search_notes(query: str, tenant: str, limit: int = 5):
    sim = Notes.content.similarity(string=query)
    return (
        Notes.where(Notes.tenant == tenant)
        .order_by(sim, asc=False)
        .limit(limit)
        .select(Notes.pg_id, Notes.content, sim)
    )

pgvector column versus an index on the table

pgvector column versus an index on the table
Jobpgvector in PostgresIndex on the table
Vector storageA vector(n) column you writeEmbeddingIndex on the source column
InsertYou embed, then INSERT the arrayInsert the text; the index embeds
QueryORDER BY embedding <=> $1column.similarity(string=)
New modelRewrite the column and the indexA destructive schema update rebuilds
Keep it whenPostgres ANN is already the contractThe source row should own search

Where Pixeltable fits

Pixeltable vs Supabase covers pgvector as a Postgres feature, not a separate product page. Copy source text, declare EmbeddingIndex, and drop the vector column and the ANN SQL. Keep pgvector when that engine is already the serving contract. Coming from another stack is the docs hub.

import pixeltable as pxt
from pixeltable.functions.huggingface import sentence_transformer


TableModel = pxt.model_base()
text_embed = sentence_transformer.using(model_id='all-MiniLM-L6-v2')


class Notes(TableModel, name='notes'):
    pg_id: pxt.String
    content: pxt.String
    tenant: pxt.String | None
    __indexes__ = [
        pxt.EmbeddingIndex(content, string_embed=text_embed, metric='cosine'),
    ]


@pxt.query
def search_notes(query: str, tenant: str, limit: int = 5):
    sim = Notes.content.similarity(string=query)
    return (
        Notes.where(Notes.tenant == tenant)
        .order_by(sim, asc=False)
        .limit(limit)
        .select(Notes.pg_id, Notes.content, sim)
    )


# pxt schema update app.py pgvector_notes -f

Questions

Can I COPY the vector column across?

Do not. COPY the source text (and filters). The embedding index rebuilds vectors. A copied array still needs the original model at query time and cannot follow a model switch.

Does this use IVFFlat or HNSW in Postgres?

No. Those are pgvector index types you create in SQL. This page replaces that column with an embedding index on the source field.

When do I keep pgvector?

When Postgres ANN is already the serving contract and other SQL joins depend on that column. Pixeltable vs Supabase is the page for that engine.

Next step

Copy the commands into an agent, or run them locally with pip install pixeltable.

Get startedQuick Start