Migrating from Pinecone to pgvector

Why Move Off Pinecone?

The main reason is cost. Benchmarks from Timescale show that PostgreSQL with pgvector and pgvectorscale achieves 28x lower p95 latency and 16x higher query throughput than Pinecone's storage-optimized (s1) index at 99% recall, while costing 75% less per month ($835/month on AWS EC2 vs. $3,241/month on Pinecone s1 for a 50M vector workload).

The second reason is consolidation. If you already run PostgreSQL, adding pgvector means your vectors live with your relational data. No separate service to manage, no cross-network latency on hybrid queries, and you can join vector results with regular SQL tables in a single query. For RAG pipelines that combine retrieval with metadata filtering, this is a real advantage.

Still, pgvector isn't a drop-in replacement for every use case. If you're running hundreds of millions of vectors with sub-10ms latency requirements across multiple regions, Pinecone's managed infrastructure still has an edge. The sweet spot for pgvector is workloads under 50 million vectors, which covers most production RAG applications.

Feature Parity Table

Schema Mapping

Pinecone organizes data into indexes with namespaces. Each record has an ID, a vector, and optional metadata. In pgvector, this maps to a PostgreSQL table with a vector column.

Pinecone index structure:

# Pinecone record
{
    "id": "doc-42",
    "values": [0.1, 0.2, 0.3, ...],  # 1536-dim vector
    "metadata": {
        "source": "manual",
        "category": "support",
        "created_at": "2026-01-15"
    }
}

Equivalent pgvector table:

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE documents (
    id TEXT PRIMARY KEY,
    embedding vector(1536),
    source TEXT,
    category TEXT,
    created_at DATE,
    namespace TEXT  -- maps to Pinecone namespace
);

-- Create HNSW index for cosine similarity
CREATE INDEX ON documents
    USING hnsw (embedding vector_cosine_ops)
    WITH (m = 16, ef_construction = 64);

The key structural difference: Pinecone stores metadata as a JSON-like blob. In pgvector, you break metadata into proper SQL columns with types and indexes. This is more work upfront but gives you real query power downstream.

Code Examples

Upserting Vectors

Pinecone:

from pinecone.grpc import PineconeGRPC as Pinecone

pc = Pinecone(api_key="YOUR_API_KEY")
index = pc.Index(host="INDEX_HOST")

index.upsert(
    vectors=[
        {
            "id": "doc-42",
            "values": [0.1, 0.2, 0.3],  # truncated for brevity
            "metadata": {"source": "manual", "category": "support"}
        }
    ],
    namespace="production"
)

pgvector:

import psycopg2
from pgvector.psycopg2 import register_vector
import numpy as np

conn = psycopg2.connect("postgresql://user:pass@localhost/mydb")
register_vector(conn)
cur = conn.cursor()

embedding = np.array([0.1, 0.2, 0.3])  # truncated for brevity
cur.execute(
    """INSERT INTO documents (id, embedding, source, category, namespace)
       VALUES (%s, %s, %s, %s, %s)
       ON CONFLICT (id) DO UPDATE SET embedding = EXCLUDED.embedding""",
    ("doc-42", embedding, "manual", "support", "production")
)
conn.commit()

The ON CONFLICT ... DO UPDATE clause gives you upsert behavior equivalent to Pinecone's. The register_vector call from the pgvector Python package handles numpy array serialization.

Querying Similar Vectors

Pinecone:

results = index.query(
    vector=query_embedding,
    top_k=10,
    include_metadata=True,
    filter={"category": {"$eq": "support"}},
    namespace="production"
)

for match in results.matches:
    print(f"{match.id}: {match.score}")

pgvector:

cur.execute(
    """SELECT id, 1 - (embedding <=> %s) AS similarity
       FROM documents
       WHERE category = 'support'
         AND namespace = 'production'
       ORDER BY embedding <=> %s
       LIMIT 10""",
    (query_embedding, query_embedding)
)

for row in cur.fetchall():
    print(f"{row[0]}: {row[1]}")

The <=> operator computes cosine distance. Use <-> for L2 distance or <#> for inner product. Note that pgvector returns distance (lower is closer), so the code above converts to similarity with 1 - distance.

Bulk Data Migration with vec2pg

Supabase built vec2pg, a CLI tool that automates Pinecone-to-pgvector migration. It handles namespace iteration and hits 700-1,100 records per second.

pip install vec2pg

vec2pg \
    --source pinecone \
    --pinecone-api-key $PINECONE_API_KEY \
    --pinecone-index my-index \
    --postgres "postgresql://user:pass@localhost/mydb"

This creates a table at vec2pg.my_index with columns for ID, vector, metadata (as JSONB), and namespace. You can then reshape this into your target schema with SQL:

INSERT INTO documents (id, embedding, source, category, namespace)
SELECT
    id,
    embedding,
    metadata->>'source',
    metadata->>'category',
    namespace
FROM vec2pg.my_index;

Pricing Impact

The cost difference is sizable, especially at scale.

Pinecone Serverless charges per operation:

• Storage: $0.33/GB/month
• Read units: $8.25 per million (Standard)
• Write units: $2.00 per million
• Minimum commitment: $50/month (Standard), $500/month (Enterprise)

pgvector on self-hosted PostgreSQL costs only the infrastructure:

• AWS RDS db.r6g.xlarge (4 vCPU, 32GB RAM): ~$415/month
• Storage (500GB gp3 EBS): ~$40/month
• Total: ~$455/month

For a workload with 10 million vectors (1536 dimensions), 5 million queries per month, and moderate write volume:

At this scale, Pinecone Serverless is actually cheaper. The breakeven happens around 50-100 million vectors or high query volumes. At 50M vectors, Pinecone s1 costs ~$3,241/month versus ~$835/month for pgvector on EC2, a 75% savings.

If you already pay for a PostgreSQL instance, the gradual cost of adding pgvector is effectively zero - just enable the extension.

Known Gotchas

1. HNSW index build time is slow. Building a HNSW index on 10 million vectors takes approximately 40 minutes on a mid-tier instance. Plan for maintenance windows. The index also consumes significant RAM during construction.

2. 2,000 dimension limit. pgvector caps standard vectors at 2,000 dimensions (4,000 for half-precision). OpenAI's text-embedding-3-large defaults to 3,072 dimensions. You'll need to reduce dimensionality with the dimensions parameter when producing embeddings, or use half-precision vectors.

3. No automatic scaling. Pinecone scales reads and writes automatically. With pgvector, you manage connection pools, read replicas, and instance sizing yourself. Managed PostgreSQL services (Supabase, Neon, Aiven) reduce this burden.

4. Metadata filtering requires schema design. Pinecone filters on arbitrary JSON metadata fields. With pgvector, you need to define columns and indexes upfront for fields you want to filter on. Changing your filter schema means ALTER TABLE and re-indexing.

5. No built-in namespace isolation. Pinecone namespaces provide data isolation within an index. In PostgreSQL, you'll need to implement this with a namespace column, separate tables, or PostgreSQL schemas.

6. Connection pooling is essential. Unlike Pinecone's HTTP API (stateless), PostgreSQL uses persistent connections. Without a connection pooler like PgBouncer, you'll exhaust your connection limit under load.

7. Recall degrades without tuning. HNSW defaults work well for small datasets, but at scale you'll need to tune m, ef_construction, and ef_search parameters. Test recall against your actual query patterns before going to production.

8. No sparse vector support. Pinecone supports sparse-dense hybrid vectors natively. Pgvector handles dense vectors only. For hybrid search, combine pgvector with PostgreSQL's built-in full-text search using tsvector.

FAQ

Can I migrate incrementally or does it have to be all at once?

You can run both systems in parallel during migration. Write to both, query from Pinecone, then cut over once pgvector is validated.

Do I need pgvectorscale or is plain pgvector enough?

For under 5 million vectors, plain pgvector with HNSW works well. Above that, pgvectorscale's StreamingDiskANN keeps the index on disk and improves cost efficiency.

What managed PostgreSQL services support pgvector?

Supabase, Neon, Aiven, AWS RDS, Azure Database for PostgreSQL, and Google Cloud SQL all support pgvector as a native extension.

How does query performance compare at scale?

At 50M vectors with 99% recall, pgvectorscale achieves comparable QPS to Pinecone with 28x lower p95 latency, according to Timescale benchmarks.

Will my LangChain or LlamaIndex code need changes?

Both frameworks have pgvector integrations. You'll swap the vector store class but the retrieval API stays the same.

Sources:

• pgvector GitHub Repository
• pgvector Python Package
• Pinecone Upsert Documentation
• Pinecone Pricing
• Pinecone Understanding Cost
• Supabase - pgvector vs Pinecone Cost and Performance
• Supabase vec2pg Migration Tool
• Tiger Data - pgvector Is Now Faster Than Pinecone