Migrations, Re-embedding, and Zero Downtime

~20 min · pgvector, operations, migrations

Level 0Scout

0 XP0/41 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

The two scenarios that make pgvector ops different

Schema migration on a table with a vector column. Postgres handles this fine — the same migration tools (Alembic, dbmate, sqlx) work. Just be aware that adding a vector(N) NOT NULL column to a populated table requires a backfill plan.
Re-embedding when you change models. The dimension changes, so you cannot ALTER COLUMN — you add a new column or table.

The zero-downtime re-embed pattern

ALTER TABLE chunks ADD COLUMN embedding_v2 vector(1024) NULL;
Backfill in batches: read text, embed, update embedding_v2 WHERE embedding_v2 IS NULL.
Build the v2 index.
Switch reads to embedding_v2 in code.
Drop embedding (the v1 column) one week later.

Backups

pg_dump and PITR work as usual. Vector columns serialize as text by default, which is verbose; for very large tables prefer base backups + WAL archiving over logical dumps.

Code

Add a parallel vector column·sql

ALTER TABLE chunks ADD COLUMN embedding_v2 vector(1024);

-- Optional: partial index until backfill finishes
CREATE INDEX chunks_embedding_v2_hnsw
    ON chunks USING hnsw (embedding_v2 vector_cosine_ops)
    WHERE embedding_v2 IS NOT NULL;

Batched backfill in Python·python

from itertools import islice

BATCH = 500
with conn.cursor() as cur:
    while True:
        cur.execute(
            'SELECT id, text FROM chunks WHERE embedding_v2 IS NULL LIMIT %s',
            (BATCH,),
        )
        rows = cur.fetchall()
        if not rows:
            break
        ids   = [r[0] for r in rows]
        texts = [r[1] for r in rows]
        new_vecs = new_model.encode(texts, normalize_embeddings=True).tolist()
        cur.executemany(
            'UPDATE chunks SET embedding_v2 = %s WHERE id = %s',
            list(zip(new_vecs, ids)),
        )
        conn.commit()
        print(f'backfilled {len(ids)} rows')

External links

Exercise

On a 10k-row test table, add a parallel vector column, backfill in batches of 500, build the v2 index, switch reads in your code, and drop the v1 column. Track total wall-clock time and any errors — that is your migration playbook for production.