Vector Database Selection Guide — Pinecone vs Weaviate vs pgvector

Vector Database Selection Guide — Pinecone vs Weaviate vs pgvector

The core infrastructure of a RAG pipeline is the vector database. It converts text into vectors with hundreds of dimensions, stores them, and retrieves nearest neighbors quickly using cosine similarity. The options can feel overwhelming, but the decision becomes clear once you frame it around a few practical criteria.

Comparing the Major Vector Databases

Pinecone — Fully Managed SaaS

import pinecone

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

#
index.upsert(vectors=[
    {"id": "doc-1", "values": embedding, "metadata": {"text": "...", "source": "..."}}
])

#
results = index.query(vector=query_embedding, top_k=5, include_metadata=True)

Pros: No infrastructure to manage, auto-scaling, low latency Cons: High cost, data leaves your environment, limited free tier

Weaviate — Open Source + Hybrid Search

Weaviate's differentiator is hybrid search. It combines vector similarity and keyword (BM25) search in a single query.

import weaviate

client = weaviate.connect_to_local()

#
results = client.collections.get("Document").query.hybrid(
 query="RAG ",
 alpha=0.5, # 0=, 1=
    limit=5
)

alpha=0.5 mixes vector and keyword signals 50:50. This is especially effective in domains with many proper nouns.

Pros: Hybrid search, self-hosting, multi-tenancy Cons: Operational complexity, memory usage

pgvector — PostgreSQL Extension

If you already run PostgreSQL, pgvector is usually the most practical choice.

--
CREATE EXTENSION IF NOT EXISTS vector;

--
CREATE TABLE documents (
  id SERIAL PRIMARY KEY,
  content TEXT,
  embedding vector(1536)
);

--
SELECT content, 1 - (embedding <=> $1) AS similarity
FROM documents
ORDER BY embedding <=> $1
LIMIT 5;

Pros: Reuse existing infrastructure, stay in SQL, transactional guarantees Cons: Performance degrades beyond a few million rows (can be mitigated with an HNSW index)

Selection Criteria

Startup / MVP               → pgvector (fastest start, lowest cost)
Production SaaS             → Pinecone (zero ops burden)
On-prem / hybrid            → Weaviate (self-hosted)
Already using PostgreSQL    → pgvector (infra consolidation)

HNSW vs IVFFlat — Choosing an Index

The index algorithm also matters when you run a vector DB.

Creating an HNSW index in pgvector:

CREATE INDEX ON documents
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

In the next post, we'll go deep on how to prepare data for a vector DB: chunking strategies.