Transformation: consolidating a standalone vector database into PostgreSQL with pgvector

#Is it a good fit?

Test with your own data and query load before committing.

#1. Enable pgvector

CREATE EXTENSION IF NOT EXISTS vector;
SELECT extversion FROM pg_extension WHERE extname = 'vector';

On Azure Flexible Server, add VECTOR to the azure.extensions server parameter first. On RDS/Aurora it's available in supported engine versions.

#2. Table design

CREATE TABLE kb_chunks (
  id          text PRIMARY KEY,           -- keep the source vector DB ids
  doc_id      text NOT NULL,
  tenant_id   text NOT NULL,
  content     text NOT NULL,
  metadata    jsonb NOT NULL DEFAULT '{}',
  updated_at  timestamptz NOT NULL DEFAULT now(),
  embedding   vector(1536) NOT NULL
);
CREATE INDEX ON kb_chunks (tenant_id);
CREATE INDEX ON kb_chunks (doc_id);

Dimension limits: HNSW indexes on vector support up to 2,000 dimensions; for larger embeddings use halfvec (up to 4,000) or reduced dimensions if your model supports it.

#3. Export from the source

Export id, vector, payload/metadata and text in pages, writing CSV or Parquet. Pinecone example pattern:

for ids in index.list(namespace="kb"):          # paginated id listing (serverless)
    res = index.fetch(ids=ids, namespace="kb")
    for vid, v in res.vectors.items():
        writer.writerow([vid, v.metadata["doc_id"], v.metadata["tenant"],
                         v.metadata["text"], json.dumps(v.metadata), "[" + ",".join(map(str, v.values)) + "]"])

Qdrant: scroll with with_vectors=True. Weaviate/Chroma/Milvus have equivalent iterators. If text isn't stored in the source, re-join it from your document store.

#4. Bulk load, then index

Load first, build the index after (much faster):

\copy kb_chunks (id, doc_id, tenant_id, content, metadata, embedding) FROM 'export.csv' WITH (FORMAT csv)

SET maintenance_work_mem = '8GB';               -- size to your server; the graph should fit
SET max_parallel_maintenance_workers = 7;
CREATE INDEX kb_chunks_hnsw ON kb_chunks USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);
ANALYZE kb_chunks;

Use the operator class that matches your source metric: cosine vector_cosine_ops (<=>), inner product vector_ip_ops (<#>), L2 vector_l2_ops (<->).

#5. Queries

SET hnsw.ef_search = 100;
SET hnsw.iterative_scan = relaxed_order;   -- pgvector 0.8+: keeps scanning when filters remove results

SELECT id, content, 1 - (embedding <=> $1) AS similarity
FROM kb_chunks
WHERE tenant_id = $2
ORDER BY embedding <=> $1
LIMIT 10;

Filtered queries were the classic pgvector pain: without iterative scans, a selective filter could return fewer than 10 rows. On older versions, raise ef_search or use partial indexes / partitions per large tenant.

#6. Verify

• Row counts and a sample of ids match the source.
• Recall: for 200 test queries, compare top-10 ids against exact search (SET enable_indexscan = off on a copy, or brute force in Python) and against the old system. Aim for ≥ 0.95 at your ef_search.
• Latency p95 under load with realistic concurrency (pgbench custom script or locust).
• RAG evaluation set: answer quality unchanged.

#7. Cut over

1. Dual-write new/updated chunks to both stores from the ingestion pipeline.
2. Shadow reads: query both, log overlap of top-10.
3. Switch reads to PostgreSQL behind a config flag; keep the old store for the rollback window.
4. Decommission the old vector DB and its credentials.

#Operate

• Vectors make tables large: plan storage, autovacuum on high-churn tables, and REINDEX CONCURRENTLY after big reloads.
• HNSW index builds and memory: watch maintenance_work_mem and build time when re-embedding.
• Backups and replicas now include vectors. Check restore time.

Warning

Keep the distance metric identical to what the embedding model and the old store used. A cosine-trained index queried with L2 returns plausible-looking but worse results, and nothing errors.

#Checklist

• Fit tested with real data and load
• Extension enabled, table and dimension type chosen
• Bulk load, then HNSW build with correct opclass
• Recall, latency and RAG quality verified
• Dual-write, shadow read, flag switch, decommission