pgvector vs Pinecone vs Weaviate: Vector Database Comparison

TL;DR — pgvector, Pinecone, and Weaviate serve different enterprise needs. pgvector is the self-hosted choice for teams already on PostgreSQL — vectors alongside application data, ACID transactions, hybrid search via tsvector, and zero per-user costs. Pinecone is the managed choice for teams that want zero infrastructure — but costs $70-5,000+/month depending on scale. Weaviate offers native hybrid search (keyword + vector in one query) with both self-hosted and managed options. For enterprise RAG with <10M vectors, pgvector is the most cost-effective. For >100M vectors, consider Qdrant or Milvus. The decision comes down to: self-hosted vs managed, dataset size, hybrid search requirements, and whether you need vectors in the same database as your application data. All three support HNSW indexing and approximate nearest neighbor search.

Every team building RAG faces the same question: which vector database? The answer depends on your scale, infrastructure preferences, and whether you need hybrid search.

Two years ago, the choice was simpler: Pinecone for managed, pgvector for PostgreSQL, everything else was experimental. In 2026, all three contenders have production-grade offerings, but they've diverged in architecture, pricing, and sweet spots.

Side-by-Side Comparison

Performance Benchmarks

Note: Benchmarks vary by hardware, vector dimensions, and configuration. Test on your own workload.

Pricing Comparison

Key insight: At 100M vectors, Pinecone costs 5-10x more than self-hosted pgvector or Weaviate. The managed convenience premium scales linearly with data size.

When to Use Each

pgvector: The PostgreSQL-Native Choice

-- Create vector column and HNSW index
CREATE TABLE documents (
    id UUID PRIMARY KEY,
    content TEXT,
    embedding VECTOR(1536),
    acl TEXT[],
    source TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- HNSW index for fast ANN search
CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops)
    WITH (m = 16, ef_construction = 64);

-- Vector similarity search with ACL filtering
SELECT id, content, source,
       1 - (embedding <=> $1) AS similarity
FROM documents
WHERE acl && $2  -- user has access
ORDER BY embedding <=> $1
LIMIT 10;

-- Hybrid search: BM25 + vector + RRF in SQL
WITH bm25 AS (
    SELECT id, ROW_NUMBER() OVER (
        ORDER BY ts_rank_cd(tsv, query) DESC
    ) AS rank
    FROM documents, plainto_tsquery('english', $3) query
    WHERE tsv @@ query LIMIT 50
),
vector AS (
    SELECT id, ROW_NUMBER() OVER (
        ORDER BY embedding <=> $1
    ) AS rank
    FROM documents WHERE acl && $2 LIMIT 50
)
SELECT id,
    COALESCE(1.0/(60 + bm25.rank), 0) +
    COALESCE(1.0/(60 + vector.rank), 0) AS rrf_score
FROM bm25 FULL OUTER JOIN vector USING (id)
ORDER BY rrf_score DESC LIMIT 10;

Use pgvector when:
- You're already running PostgreSQL
- You need vectors alongside application data with ACID transactions
- Your dataset is under 10M vectors
- You need row-level security for multi-tenant access
- You want hybrid search without a separate search engine
- Cost matters (free, infrastructure only)

Don't use pgvector when:
- Dataset exceeds 10M vectors (latency degrades)
- You need GPU-accelerated search
- You need horizontal scaling across multiple nodes
- You want managed infrastructure

Pinecone: The Managed Choice

from pinecone import Pinecone

pc = Pinecone(api_key="your-api-key")
index = pc.Index("company-knowledge-base")

# Upsert vectors with metadata
index.upsert(vectors=[
    {
        "id": "doc-001",
        "values": [0.1, 0.2, ...],
        "metadata": {
            "source": "google_drive",
            "acl": ["engineering", "finance"],
            "title": "Refund Policy"
        }
    }
])

# Query with metadata filtering
results = index.query(
    vector=[0.1, 0.2, ...],
    filter={"acl": {"$in": ["engineering"]}},
    top_k=10,
    include_metadata=True
)

Use Pinecone when:
- You want zero infrastructure management
- Your team is small and ops-focused on application, not databases
- You need serverless scaling (pay per usage)
- You need sparse-dense hybrid search (serverless mode)
- Dataset is large (>100M vectors) and you don't want to self-host

Don't use Pinecone when:
- Cost at scale is a concern ($5,000+/month at 100M vectors)
- Data sovereignty requires on-premise deployment
- You need ACID transactions
- You need vectors in the same database as application data

Weaviate: The Hybrid Search Choice

import weaviate

client = weaviate.connect_to_local()

# Create collection with vectorizer
collection = client.collections.create(
    name="Document",
    vectorizer_config=Configure.Vectorizer.text2vec_transformers(),
    properties=[
        Property(name="content", data_type=PropType.TEXT),
        Property(name="source", data_type=PropType.TEXT),
        Property(name="acl", data_type=PropType.TEXT_ARRAY),
    ]
)

# Hybrid search (BM25 + vector in one query)
results = collection.query.hybrid(
    query="refund policy for enterprise customers",
    alpha=0.5,  # 0=BM25 only, 1=vector only, 0.5=balanced
    limit=10,
    filters=Filter.by_property("acl").contains_any(["engineering"])
)

Use Weaviate when:
- You need native hybrid search (BM25 + vector in one query)
- You want both self-hosted and managed options
- You need built-in vectorization modules (text2vec, multi2vec)
- You want GraphQL-based queries
- You need multi-modal search (text, image, audio)

Don't use Weaviate when:
- You need ACID transactions (Weaviate is eventually consistent)
- You're already on PostgreSQL (pgvector is simpler)
- You need row-level security at the database level

Decision Matrix

Self-Hosted vs Managed: The Real Tradeoff

pgvector vs Pinecone vs Weaviate Checklist

• [ ] Estimate your vector count (1M? 10M? 100M?)
• [ ] Determine if you need self-hosted or managed
• [ ] Check if you need hybrid search (BM25 + vector)
• [ ] If already on PostgreSQL: start with pgvector (simplest path)
• [ ] If you need managed: evaluate Pinecone (serverless) vs Weaviate Cloud
• [ ] If you need native hybrid search: Weaviate or OpenSearch
• [ ] If you need ACID transactions: pgvector (PostgreSQL)
• [ ] If you need row-level security: pgvector (PostgreSQL RLS)
• [ ] Benchmark on your actual data (vector dimensions, query patterns)
• [ ] Test metadata filtering performance (critical for ACL enforcement)
• [ ] For pgvector: configure HNSW with m=16, ef_construction=64 as starting point
• [ ] For pgvector: add tsvector column for BM25 hybrid search
• [ ] For Pinecone: evaluate serverless vs pod-based pricing for your query pattern
• [ ] For Weaviate: tune alpha parameter (0.3-0.7) for hybrid search balance
• [ ] Plan for incremental updates (don't rebuild index on every change)
• [ ] Consider content hash deduplication to avoid re-indexing
• [ ] Monitor query latency p99 (target: <100ms for enterprise RAG)
• [ ] Set up backups and recovery (especially for self-hosted)
• [ ] Evaluate RRF fusion for hybrid search results
• [ ] Integrate with AI knowledge base architecture
• [ ] For >100M vectors: consider Qdrant or Milvus instead

FAQ

Which is better: pgvector, Pinecone, or Weaviate?

There is no single "better" option — it depends on your use case. pgvector is best when you're already using PostgreSQL and want vectors alongside your application data with ACID transactions. Pinecone is best for teams that want a fully managed service with no infrastructure to maintain. Weaviate is best when you need built-in hybrid search (keyword + vector in one query) with a managed cloud option. For self-hosted enterprise deployments with <10M vectors, pgvector is the most cost-effective. For >100M vectors, consider Qdrant or Milvus.

Is pgvector production-ready for enterprise RAG?

Yes. pgvector is production-ready for enterprise RAG with datasets under 10M vectors. It uses HNSW (Hierarchical Navigable Small World) indexing for fast approximate nearest neighbor search. The main advantage is that vectors live alongside your application data in PostgreSQL, giving you ACID transactions, full-text search (tsvector for hybrid search), row-level security, and point-in-time recovery. For datasets over 10M vectors, pgvector performance degrades compared to dedicated vector databases.

How much does Pinecone cost vs pgvector?

Pinecone costs $70-500+/month depending on pod size and usage (serverless mode charges per read/write unit). pgvector is free — you only pay for your PostgreSQL infrastructure. A self-hosted PostgreSQL instance with pgvector for 1M vectors costs ~$20-50/month in cloud compute. The equivalent Pinecone deployment costs $70-150/month. At 100M vectors, Pinecone can cost $5,000+/month while a self-hosted pgvector or Qdrant deployment costs $500-1,000/month in infrastructure.

Does pgvector support hybrid search?

pgvector does not support hybrid search natively, but you can implement it using PostgreSQL full-text search (tsvector) alongside vector similarity. Run BM25 search using ts_rank_cd and vector search using pgvector's <=> operator, then fuse results with Reciprocal Rank Fusion (RRF) implemented in SQL. This requires manual implementation but gives you hybrid search in a single database. OpenSearch and Weaviate support hybrid search natively.

When should you switch from pgvector to a dedicated vector database?

Switch from pgvector to a dedicated vector database (Qdrant, Milvus, Weaviate) when: (1) your dataset exceeds 10M vectors and query latency exceeds 100ms, (2) you need specialized features like sparse vectors for hybrid search, (3) you need horizontal scaling across multiple nodes, or (4) you need GPU-accelerated search. For most enterprise RAG deployments with 100K-5M vectors, pgvector is sufficient and simpler to operate.

Want a self-hosted AI company brain that does all of this out of the box?
Book a demo →