Pinecone to pgvector: a migration guide

Moving vectors from Pinecone to pgvector can simplify a stack that already depends on PostgreSQL. It can also move indexing, capacity, backups, and incident response onto your team.

There is no honest universal savings percentage or latency win. Compare both systems with your corpus, query mix, region, availability target, and staff time. Then migrate only if the operational trade makes sense.

In brief

• Build the cost case from current invoices and measured database capacity.
• Keep the embedding model, dimensions, distance function, and chunk IDs unchanged during the move.
• Backfill first, then dual-write and compare retrieval on a labeled query set.
• Keep a tested rollback path until the new store has survived the agreed observation window.

Decide why you are moving

A migration needs a specific reason. Common reasons include:

• Vectors and relational metadata must change in one transaction
• The team wants one backup and access-control system
• Network hops between application data and vector data are causing measured problems
• Current vendor spend exceeds the cost of extra PostgreSQL capacity and operations

Staying with Pinecone can be the better decision when the team wants managed scaling, separate failure domains, or less database work. pgvector is an extension, not a managed operations team.

Build a cost worksheet

Use actual monthly figures instead of a headline percentage.

Current monthly cost = vector service + transfer + backups + support

Projected pgvector cost = added database compute
 + added storage and replicas
 + backup growth
 + engineering and on-call time

Run the projection at current usage and at a plausible high case. Include index build space and replica lag during backfills. If the result changes with a modest growth assumption, the decision is not settled.

Inventory the data contract

Record these values before writing migration code:

• Embedding model and exact version
• Vector dimensions
• Distance function, such as cosine or inner product
• Namespace and workspace boundaries
• Vector ID format
• Metadata fields and filters used by every query
• Deletion, update, and retry behavior
• Expected result count and tie-breaking rules

The migration should change one major variable at a time. If you also change the embedding model or chunking, you will not know whether a relevance change came from storage or content.

Design the PostgreSQL schema

Install the pgvector extension and use the dimension required by your embedding model.

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE knowledge_chunks (id text PRIMARY KEY,
 workspace_id text NOT NULL,
 source_id text NOT NULL,
 content text NOT NULL,
 content_hash text NOT NULL,
 embedding vector(1536) NOT NULL,
 metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
 updated_at timestamptz NOT NULL DEFAULT now());

CREATE INDEX knowledge_chunks_workspace_idx
 ON knowledge_chunks (workspace_id);

CREATE INDEX knowledge_chunks_embedding_hnsw_idx
 ON knowledge_chunks
 USING hnsw (embedding vector_cosine_ops);

The 1536 dimension is an example. Match it to the vectors you actually store. Match the operator class to the distance function used in the old system.

Keep frequently filtered fields in typed columns. A large JSON filter is harder to constrain and inspect than workspace_id = $1.

Write the same query contract

The new query must preserve authorization and metadata filters.

SELECT id, source_id, content, embedding <=> $2 AS distance
FROM knowledge_chunks
WHERE workspace_id = $1
ORDER BY embedding <=> $2, id
LIMIT $3;

Add deterministic tie-breaking if downstream tests depend on stable order. Inspect the query plan with representative workspace sizes. Do not approve a query that drops the workspace filter to gain speed.

Backfill with checkpoints

Export records in stable ID order. Upsert each batch so rerunning it is safe.

INSERT INTO knowledge_chunks (id, workspace_id, source_id, content, content_hash, embedding, metadata)
VALUES ($1, $2, $3, $4, $5, $6, $7)
ON CONFLICT (id) DO UPDATE SET
 workspace_id = EXCLUDED.workspace_id,
 source_id = EXCLUDED.source_id,
 content = EXCLUDED.content,
 content_hash = EXCLUDED.content_hash,
 embedding = EXCLUDED.embedding,
 metadata = EXCLUDED.metadata,
 updated_at = now();

After each batch, store the last completed ID and counts. At the end, compare:

• Total records
• Records per workspace
• Missing and duplicate IDs
• Vector dimensions
• Null or malformed metadata
• A hash of content and selected metadata

Matching counts are necessary, but they do not prove matching retrieval.

Dual-write before changing reads

Write new and updated chunks to both systems while reads still use the current store. Give each write an idempotency key and log failures by destination.

Decide how the application behaves when one write succeeds and the other fails. A silent partial write creates a gap that appears only after cutover. A repair queue is usually clearer than retrying inside the customer request until it times out.

Keep deletes in the dual-write path too. Old vectors are harder to notice than missing ones.

Compare retrieval, not just latency

Create a labeled set of questions with expected source passages. Run both systems with the same query vectors, filters, and result limit.

Record:

• Whether the labeled passage appears in the top results
• Rank of the first relevant passage
• Result overlap between systems
• Empty and error responses
• Median and tail latency under comparable load

Approximate indexes can return a different order without making the answer worse. Judge the source relevance first. Compare latency only after both sides satisfy the same relevance target.

Cut over with a rollback switch

A safe cutover has distinct steps:

1. Finish the backfill and reconcile gaps.
2. Run dual writes until there is no unexplained drift.
3. Send a small share of reads to pgvector.
4. Compare errors, relevance, database load, and queue health.
5. Increase traffic in controlled steps.
6. Keep Pinecone writes active until rollback is no longer required.

The rollback switch should change reads without a deployment. Test it before the first production read moves.

Remove the old system last

After the observation window, stop old-store writes and take a final export. Confirm the retention and deletion requirements for that export. Remove credentials, network rules, repair jobs, and dead code after the team accepts the rollback boundary.

Do not delete the old index on cutover day. A migration is complete when the new path has been operated, restored, and repaired, not when the first query succeeds.

When pgvector is the wrong move

Do not migrate only because PostgreSQL is already in the stack. Stay with a managed vector service if your database team cannot own index memory, vacuum behavior, replicas, backups, and vector-query load. Also pause if the vector workload needs an independent failure domain or grows in a way the current PostgreSQL plan has not been tested to handle.

The best store is the one that meets the retrieval target and has an operational owner. Price is one row in that decision.

For index and capacity testing, continue with how to scale a RAG system and vector search explained.

Frequently asked questions

Is pgvector always cheaper than Pinecone?

No. The answer depends on vendor pricing, database headroom, replicas, storage, backups, traffic, and the engineering time required to run the system.

Will pgvector always reduce latency?

No. Co-locating data may remove a network hop, but index settings, filters, database load, region, and hardware can outweigh that gain. Benchmark both systems with the same relevance target.

Can I change embedding models during the migration?

You can, but it combines two migrations and makes diagnosis harder. Moving storage first and re-embedding later gives each change its own evaluation and rollback path.

How long should dual writes run?

Long enough to cover normal creates, updates, deletes, retries, and at least one expected traffic cycle. Set the duration from risk and observed drift, not a generic number of days.