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 plan tiers (as of July 2026):

The Builder plan ($20/month flat) is new as of May 2026 - it's a fixed-cost tier aimed at solo developers and small teams who need more than Starter limits without committing to metered billing.

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 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, adding pgvector is effectively free - 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 - use halfvec instead. pgvector caps vector columns at 2,000 dimensions. The halfvec type doubles that to 4,000 dimensions and cuts storage by 50% with negligible recall loss on standard embedding models. For OpenAI's text-embedding-3-large (3,072 dims), use halfvec(3072) and skip the dimension reduction workaround. For any 1536-dim model, switching from vector to halfvec also gives 23% faster index builds and 50% less prewarming time:

-- Use halfvec instead of vector for dimension savings
CREATE TABLE documents (
    id TEXT PRIMARY KEY,
    embedding halfvec(1536),  -- was: vector(1536)
    source TEXT,
    category TEXT
);

-- HNSW index works identically with halfvec
CREATE INDEX ON documents
    USING hnsw (embedding vector_cosine_ops);

For existing tables, migrate with: ALTER TABLE documents ALTER COLUMN embedding TYPE halfvec(1536); - this rewrites the table, so plan a maintenance window.

3. Security: upgrade to pgvector 0.8.2 or later. CVE-2026-3172 (February 2026) is a buffer overflow in parallel HNSW index builds that can leak data from other relations or crash the server. Any pgvector version before 0.8.2 is vulnerable when max_parallel_maintenance_workers > 0. The latest stable release is 0.8.4 (June 2026), which also fixes HNSW vacuuming corruption bugs introduced in 0.8.3. Check your version with SELECT extversion FROM pg_extension WHERE extname = 'vector';.

4. Filtered queries can return empty results without iterative scan. When you use a WHERE clause with a HNSW index, pgvector retrieves the top k vectors by distance and then applies your filter. If most candidates fail the filter, you get fewer rows than your LIMIT - sometimes zero. Pgvector 0.8.0 (October 2024, widely launched in 2026) added hnsw.iterative_scan to fix this:

-- Enable per session
SET hnsw.iterative_scan = 'relaxed_order';  -- better recall, slight reordering
SET hnsw.iterative_scan = 'strict_order';   -- preserves exact distance ordering

-- Or enable globally
ALTER SYSTEM SET hnsw.iterative_scan = 'relaxed_order';
SELECT pg_reload_conf();

relaxed_order is recommended for RAG workloads. hnsw.max_scan_tuples (default 20,000) bounds how far the scan extends.

5. 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.

6. 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.

7. 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.

8. 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.

9. 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.

10. Pinecone Python SDK requires Python 3.10+. The Pinecone SDK dropped Python 3.9 support (which reached end-of-life October 2025). If your migration scripts or codebase targets Python 3.9, you'll need to upgrade. The SDK is tested against CPython 3.10 through 3.13.

11. Pinecone full-text search uses different endpoints. Pinecone added BM25 and Lucene-syntax full-text search in public preview (May 2026) via new /namespaces/{namespace}/documents/ endpoints - separate from the vector upsert path. Pgvector handles full-text search through PostgreSQL's built-in tsvector and ts_query. Both approaches work, but the APIs are completely different, so your pgvector hybrid search code won't map cleanly to Pinecone's new FTS endpoints if you ever go back.

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 verified.

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 reaches 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 CHANGELOG
• pgvector Python Package
• Pinecone 2026 Release Notes
• Pinecone Upsert Documentation
• Pinecone Pricing
• Pinecone Understanding Cost
• Supabase - pgvector vs Pinecone Cost and Performance
• Supabase HNSW Index Guide
• Neon - halfvec vs vector storage comparison
• Supabase vec2pg Migration Tool
• Tiger Data - pgvector Is Now Faster Than Pinecone