Vector Databases Compared: pgvector vs Pinecone vs Weaviate

Vector databases are the storage layer for AI applications — powering RAG, semantic search, recommendation, and anomaly detection. In 2026 there are more options than ever. Here's a direct comparison of the three I've evaluated in production: pgvector, Pinecone, and Weaviate.

What a vector database does

A vector database stores high-dimensional embeddings (typically 768–3072 dimensions) and supports efficient approximate nearest neighbor (ANN) search — finding the k most similar vectors to a query vector.

# Embed a document
embedding = openai.embeddings.create(
    input="This is the document text",
    model="text-embedding-3-small"
).data[0].embedding  # 1536-dimensional float array

# Store it (simplified)
vector_db.upsert(id="doc_001", vector=embedding, metadata={"source": "blog"})

# Query it
results = vector_db.query(
    vector=embed("find similar documents"),
    top_k=10,
    filter={"source": "blog"}
)

pgvector (PostgreSQL extension)

pgvector adds vector similarity search to PostgreSQL. You get vector storage + search in the same database as your relational data.

Indexes:

• ivfflat — faster build, good recall at moderate scale
• hnsw — better recall, slower build, higher memory, better for dynamic datasets

-- HNSW index (recommended for most cases)
CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops)
    WITH (m = 16, ef_construction = 64);

-- Query: 10 nearest neighbors with metadata filter
SELECT id, content, 1 - (embedding <=> $1) AS similarity
FROM documents
WHERE tenant_id = $2
  AND created_at > NOW() - INTERVAL '30 days'
ORDER BY embedding <=> $1
LIMIT 10;

Strengths:

• One database for everything (no sync)
• SQL joins across vector results and relational data
• ACID transactions (embeddings update atomically)
• Zero additional operational cost if you already run PostgreSQL
• Metadata filtering via standard SQL WHERE

Weaknesses:

• Recall degrades above ~5M vectors without careful HNSW tuning
• Memory: HNSW index loads into RAM (1536-dim, 5M vectors ≈ ~30GB)
• No built-in multi-tenancy isolation primitives (implement via row-level security)

Best for: < 5M vectors, existing PostgreSQL users, applications needing SQL joins on vector results.

Pinecone

Managed vector database, optimized for scale and simplicity. No infrastructure to manage.

import pinecone

pc = pinecone.Pinecone(api_key="...")
index = pc.Index("my-index")

# Upsert
index.upsert(vectors=[
    {"id": "doc_001", "values": embedding, "metadata": {"source": "blog"}}
])

# Query with metadata filter
results = index.query(
    vector=query_embedding,
    top_k=10,
    filter={"source": {"$eq": "blog"}},
    include_metadata=True
)

Strengths:

• Serverless tier (pay per query, no idle cost)
• Scales to 100M+ vectors with consistent performance
• Simple API, fast to get started
• Namespaces for multi-tenancy (tenant isolation built-in)
• Fully managed (no ops)

Weaknesses:

• Expensive at scale ($70+/month for 1M vectors with frequent queries)
• No SQL — metadata filtering is limited to Pinecone's filter DSL
• Data lives outside your PostgreSQL — requires sync logic
• Vendor lock-in

Best for: Large-scale semantic search (> 5M vectors), teams that want fully managed with no operational overhead.

Weaviate

Open-source vector database with a graph-like data model. Can be self-hosted or used as a managed service (Weaviate Cloud).

import weaviate

client = weaviate.Client("http://localhost:8080")

# Define schema
client.schema.create_class({
    "class": "Document",
    "vectorizer": "text2vec-openai",  # auto-embed on insert
    "properties": [{"name": "content", "dataType": ["text"]}]
})

# Query
result = (
    client.query
    .get("Document", ["content"])
    .with_near_text({"concepts": ["machine learning"]})
    .with_limit(10)
    .do()
)

Strengths:

• Graph-like cross-references between objects
• Built-in vectorization (auto-embed on insert via modules)
• Hybrid search (BM25 + vector) built in
• Self-hostable (open source)
• Generative search (call LLM on retrieved results in one query)

Weaknesses:

• Complex schema definition and query language (GraphQL)
• Higher operational complexity than pgvector
• Weaviate Cloud pricing is comparable to Pinecone

Best for: Knowledge graph use cases, teams that want built-in vectorization and LLM integration, hybrid search without custom implementation.

Direct comparison

| Feature | pgvector | Pinecone | Weaviate | |---------|----------|----------|---------| | Max scale (practical) | ~5M vectors | 1B+ | 100M+ | | Setup complexity | Low | Very low | Medium | | Operational overhead | None (existing PG) | None (managed) | Medium (self-host) | | SQL support | ✅ Full | ❌ No | ❌ No | | Hybrid search built-in | Partial (manual) | ✅ Yes | ✅ Yes | | Multi-tenancy | Via RLS | Namespaces | Multi-tenancy | | Cost (1M vectors) | $0 (existing PG) | ~$70/mo | $0 (self-host) | | Managed option | Supabase, Neon | ✅ Native | ✅ WCS |

My recommendation

Start with pgvector. For 95% of applications under 5M vectors, pgvector is the right choice. It costs nothing if you're already on PostgreSQL, requires no sync logic, and supports SQL joins that no dedicated vector DB can match.

Move to Pinecone when your vector count exceeds what pgvector can handle with acceptable recall, you need truly serverless scaling, or your team can't manage infrastructure.

Choose Weaviate when you have graph-like relationships between objects, need built-in vectorization, or want hybrid search without implementing it yourself.

FAQ

What is pgvector? pgvector is an open-source PostgreSQL extension that adds vector data types, operators, and indexes (ivfflat and HNSW) for similarity search. It lets you run vector search in your existing PostgreSQL database.

Is pgvector good enough for production? Yes, for most applications. pgvector with HNSW indexing handles millions of vectors with sub-50ms query times. The practical limit is around 5M vectors before you need careful tuning or dedicated vector DB.

What embedding dimensions does pgvector support? pgvector supports up to 2,000 dimensions with ivfflat and up to 2,000 dimensions with HNSW. OpenAI's text-embedding-3-small produces 1,536 dimensions — within limits.

Which vector database is fastest? At < 1M vectors, HNSW in pgvector, Pinecone, and Weaviate all achieve < 50ms P99 query latency. At 10M+ vectors, Pinecone's managed infrastructure maintains consistent performance while pgvector requires more careful tuning.

---

Written by Shihab Shahriar Antor — AI Engineer & Founder of Shahriar Labs. See also: RAG in Production: Architecture That Actually Scales · pgvector: Vector Search in PostgreSQL.