Order the migration steps for moving 8M vectors from Pinecone to pgvector with zero query downtime.

Order steps·Medium·4.0 · 0·~1 min·Asked atCred Freshworks H2o Ai

Attempt it

1After a stable soak period, stop dual writes and decommission the Pinecone index
2Bulk export historical vectors plus metadata from Pinecone and COPY them into Postgres, then build the HNSW index
3Shift read traffic to Postgres gradually behind a flag, watching error rates and tail latency
4Shadow query both systems with mirrored production traffic and compare recall, latency, and result overlap
5Enable dual writes so every new upsert and delete is applied to both Pinecone and Postgres
6Provision Postgres with pgvector, create the table, choose the distance operator, and plan HNSW parameters to match production recall needs

**Migrating between vector databases** under live traffic has the same shape as any zero-downtime data migration. The trick is to keep the old system fully working until the new system is fully proven, then flip atomically. For Pinecone to pgvector specifically, six steps in this order work: 1. **Stand up Postgres with pgvector.** Create the table, pick a distance metric matching what Pinecone used (cosine, L2, or inner product), and plan HNSW parameters (`m`, `ef_construction`, `ef_search`). 2. **Turn on dual writes** so every new vector goes to both systems. This must happen *before* the export, or writes during the export window only land in Pinecone and get lost. 3. **Bulk-export from Pinecone** and load into Postgres using `COPY` for speed. Build the HNSW index after the data is loaded; building during loading is slower. 4. **Shadow-test:** mirror real queries to both systems and compare the result sets. If pgvector's recall or latency is worse, fix it before any user-facing change. 5. **Flip reads gradually behind a feature flag.** 1%, 10%, 50%, 100%. Dual writes stay on so rollback is just flipping the flag. 6. **Soak, then decommission.** After one to two weeks of stability, stop dual writes, snapshot Pinecone, then drop the index. Skipping dual writes is the most common silent failure mode.

**Six steps, in this order, because the order itself is the rollback invariant.** 1. **Provision pgvector.** Choose the distance operator that matches Pinecone's metric exactly (`vector_cosine_ops`, `vector_l2_ops`, or `vector_ip_ops`). Plan HNSW parameters: `m` typically 16-32, `ef_construction` 64-200, `ef_search` tuned later for recall. Pre-create the table with metadata columns. 2. **Enable dual writes.** Every upsert and delete in application code goes to Pinecone *and* Postgres. Implement as a write-through wrapper with idempotent upserts and a retry queue so transient failures cannot drop a write silently. This step must precede step 3. 3. **Bulk export and load.** Page through Pinecone via the fetch API, write vectors plus metadata to disk in batches, then `COPY` into Postgres. Build the HNSW index *after* the load completes (`CREATE INDEX CONCURRENTLY ... USING hnsw`), because in-place index maintenance during load is significantly slower than bulk-then-index. 4. **Shadow-query.** Mirror live queries to both systems. Compare top-K result overlap, recall against a labelled probe set, p50/p99 latency, and metadata-filter behavior. Tune `ef_search` until pgvector matches or exceeds Pinecone on the metrics that matter. 5. **Flip reads behind a flag.** Gradual rollout (1%, 10%, 50%, 100%) per-tenant or per-route. Dual writes continue so rollback is a single toggle. Watch error rates, tail latency, and downstream KPIs at each step. 6. **Soak and decommission.** One to two weeks on Postgres-only reads with dual writes on. Then stop dual writes, snapshot Pinecone, drop the index, cancel the contract. **The invariant:** between steps 2 and 6, both systems stay complete and rollback is a flag toggle. Skip step 2 or 4 and it becomes a recovery project.

TL;DR

Provision pgvector, start dual writes, bulk-export and build the HNSW index, shadow-compare, flip reads behind a flag, soak, then decommission Pinecone.

Memory aid

Easy to grasp

Moving 8 million vectors from one database to another while the application keeps running is like changing a tire on a moving car. You cannot stop, and you cannot lose any spinning rubber. The trick is to bolt on the new tire next to the old one, drive on both for a while so they share the load, prove the new one handles the road the same way as the old one, then slowly shift weight onto it. Only after the new tire has carried full weight on its own for a few days do you actually remove the old one. Every step in this sequence exists to keep the car driving smoothly even if you discover the new tire is the wrong size halfway through.

Key concepts

Vector Databases

Concept explanation~2 min read

Everything you need to truly understand this topic: intuition, mechanics, step by step explanation, code, formulas, and worked example. Click to expand.

Pinecone-to-pgvector migrations have become common as teams consolidate operational surface area, reduce vendor cost, or align retrieval with their relational data. The migration mechanics are well understood at this point: it is fundamentally a zero-downtime data migration with the added wrinkle that the new system has different index-building semantics and the old system is a managed service with API rate limits on bulk operations.

This deep-dive walks through the six-step sequence in detail, the rollback invariant that forces the order, the parameter-tuning required to make pgvector match Pinecone on retrieval quality, and the operational subtleties (Postgres vacuum, index bloat, replication) that surface only after the soak period starts.

Provisioning pgvector correctly

Distance operator alignment

Pinecone exposes cosine, dot product, and Euclidean metrics. pgvector exposes three operator classes for HNSW: vector_cosine_ops, vector_ip_ops (inner product), and vector_l2_ops (Euclidean). The choice must match the source database exactly. A migration that switches metric silently rebuilds the entire ranking and the migration becomes a quality regression.

The operator is fixed at index creation time. Changing it requires dropping and rebuilding the index.

HNSW parameters

pgvector HNSW takes two build-time parameters: m (max connections per node, typically 16-32) and ef_construction (search width during build, typically 64-200). Higher values give better recall at the cost of build time and index size. For an 8M-row corpus at 768-1536 dim, sensible defaults are m=16, ef_construction=100. For workloads that need very high recall, push to m=24, ef_construction=200.

Query-time recall is also gated by ef_search, a session parameter. Default is 40; production deployments typically run 80-200.

Capacity planning

An 8M-row HNSW index at 1536 dim float32 with m=16 is roughly 50 GB on disk (raw vectors) plus 5-15 GB graph overhead. The Postgres instance needs shared_buffers and work_mem sized accordingly, plus enough RAM beyond that for the index pages to stay hot. Skimping on RAM is the most common reason pgvector underperforms Pinecone in benchmarks.

Dual writes and the bulk load

Shadow comparison and the read flip

Decommissioning and the operational reality of pgvector

The 2026 Pinecone-to-pgvector migration checklist with numbers

Situations where this technique stops working.

2–4 min · Everything important, quickly.

Real products, models, and research that use this idea.

Supabase has documented Pinecone-to-pgvector migrations for cost-driven moves; their template follows the dual-write plus bulk-load shape.
Neon and Crunchy Data publish similar migration playbooks for managed Postgres with pgvector targeting Pinecone customers.

What an interviewer would ask next. Try answering before peeking at the approach.

QHow do you tune `ef_search` to match Pinecone recall without blowing the latency budget?

Sweep ef_search across {40, 80, 120, 200} on a labelled probe set; pick the smallest value that meets the recall target. Watch p99 latency at each setting because ef_search is linear in cost. Document the chosen value for ops; default 40 is rarely enough.

2 more follow-ups an interviewer would ask next. Sign in to reveal them.

Red flags & common mistakes

The phrases that signal junior thinking. Click to expand.

Most common mistake

Starting the bulk export before dual writes are live, which leaves Postgres missing every upsert that landed during the export window.

60 second bullets to scan on the way to the call.

Why dual writes must precede the bulk export
Distance-operator alignment as a correctness prerequisite

Primary sources. Browse if you want the original framing.