Vector Databases Pinecone vs Weaviate vs Chroma vs pgvector

The Story That Explains Vector Databases

Traditional databases answer questions like "Give me all rows where price < 100." Vector databases answer a fundamentally different kind of question: "Give me the things most similar in meaning to this." That distinction is everything when building AI applications.

What Is a Vector (Embedding)?

Before comparing databases, you need to understand what they actually store. An embedding is a list of floating-point numbers produced by an embedding model. These numbers encode semantic meaning — the position of the text in a high-dimensional mathematical space.

The core operation a vector database must perform efficiently is called Approximate Nearest Neighbour (ANN) search: given a query vector, find the k stored vectors that are closest to it — measured by cosine similarity or Euclidean distance — across millions or billions of stored vectors, in milliseconds.

The Vector Database Landscape — Visual Overview

🌲 Pinecone — The Fully Managed Powerhouse

Core Architecture

Pinecone uses a proprietary ANN index (based on HNSW and product quantisation) distributed across pods. Its serverless tier scales to zero when idle and spins up in milliseconds. Indexes are namespace-partitioned, allowing multi-tenancy within a single index.

Python: Full RAG Pipeline with Pinecone

# pip install pinecone-client openai
import os
from pinecone import Pinecone, ServerlessSpec
from openai import OpenAI

# ── 1. Initialise clients ──────────────────────────────────
pc     = Pinecone(api_key=os.environ["PINECONE_API_KEY"])
openai = OpenAI(api_key=os.environ["OPENAI_API_KEY"])

# ── 2. Create or connect to an index ──────────────────────
INDEX_NAME = "rag-docs"
if INDEX_NAME not in [i.name for i in pc.list_indexes()]:
    pc.create_index(
        name=INDEX_NAME,
        dimension=1536,          # text-embedding-3-small output size
        metric="cosine",
        spec=ServerlessSpec(cloud="aws", region="us-east-1")
    )
index = pc.Index(INDEX_NAME)

# ── 3. Embed and upsert documents ─────────────────────────
documents = [
    {"id": "doc-1", "text": "Vector databases store embeddings for semantic search.",
     "metadata": {"source": "chapter_1", "topic": "vector-db"}},
    {"id": "doc-2", "text": "Pinecone is a fully managed serverless vector database.",
     "metadata": {"source": "chapter_2", "topic": "pinecone"}},
    {"id": "doc-3", "text": "RAG improves LLM accuracy by grounding responses in retrieved context.",
     "metadata": {"source": "chapter_3", "topic": "rag"}},
]

def embed(text: str) -> list[float]:
    resp = openai.embeddings.create(input=text, model="text-embedding-3-small")
    return resp.data[0].embedding

vectors = [
    (f'ns1#{doc["id"]}', embed(doc["text"]), doc["metadata"])
    for doc in documents
]
index.upsert(vectors=vectors, namespace="chapter-ns")

# ── 4. Semantic search with metadata filter ────────────────
query   = "How does Pinecone scale for large datasets?"
q_vec   = embed(query)
results = index.query(
    vector=q_vec,
    top_k=3,
    namespace="chapter-ns",
    filter={"topic": {"$in": ["pinecone", "vector-db"]}},
    include_metadata=True
)

for match in results.matches:
    print(f"Score: {match.score:.4f} | Source: {match.metadata['source']}")

🔮 Weaviate — The Knowledge Graph + Vector Hybrid

Key Architecture: HNSW + Inverted Index

Weaviate uses HNSW (Hierarchical Navigable Small World) for ANN search, paired with a traditional inverted index for BM25 keyword search. The combination enables hybrid search — a weighted mix of semantic and keyword relevance — out of the box without extra infrastructure.

Python: Define Schema, Upsert and Hybrid-Search

# pip install weaviate-client
import weaviate
import weaviate.classes as wvc
import os

# ── 1. Connect (local Docker or Weaviate Cloud) ────────────
client = weaviate.connect_to_local()          # or connect_to_wcs()

# ── 2. Create a collection (schema class) ─────────────────
if not client.collections.exists("Article"):
    client.collections.create(
        name="Article",
        vectorizer_config=wvc.config.Configure.Vectorizer.text2vec_openai(),
        generative_config=wvc.config.Configure.Generative.openai(),
        properties=[
            wvc.config.Property(name="title",   data_type=wvc.config.DataType.TEXT),
            wvc.config.Property(name="content", data_type=wvc.config.DataType.TEXT),
            wvc.config.Property(name="year",    data_type=wvc.config.DataType.INT),
        ]
    )

# ── 3. Insert documents ───────────────────────────────────
articles = client.collections.get("Article")
with articles.batch.dynamic() as batch:
    batch.add_object({"title": "Introduction to Vector Search",
                      "content": "Embeddings capture semantic meaning...",
                      "year": 2024})
    batch.add_object({"title": "Weaviate in Production",
                      "content": "Weaviate uses HNSW with BM25 hybrid...",
                      "year": 2023})

# ── 4. Hybrid search (BM25 + semantic, alpha=0.7) ─────────
results = articles.query.hybrid(
    query="vector database hybrid search",
    alpha=0.7,                     # 0 = pure BM25, 1 = pure vector
    filters=wvc.query.Filter.by_property("year").greater_than(2022),
    limit=5,
    return_metadata=wvc.query.MetadataQuery(score=True, explain_score=True)
)

for obj in results.objects:
    print(f"Title: {obj.properties['title']}")
    print(f"Score: {obj.metadata.score:.4f}")
    print(f"Explain: {obj.metadata.explain_score[:80]}")

client.close()

🎨 Chroma — The Developer's Best Friend for Prototyping

Two Modes: Ephemeral and Persistent

Chroma operates in two modes. In-memory (ephemeral) — data lives in RAM, gone when Python exits, perfect for tests. Persistent — data written to a directory on disk using DuckDB + Parquet under the hood. Both modes expose the same identical Python API, making it trivial to switch.

Python: Local RAG with Chroma in Under 30 Lines

# pip install chromadb sentence-transformers
import chromadb
from chromadb.utils import embedding_functions

# ── 1. Create a persistent client ─────────────────────────
client = chromadb.PersistentClient(path="./my_chroma_db")

# ── 2. Plug in a local embedding model (no API key!) ──────
ef = embedding_functions.SentenceTransformerEmbeddingFunction(
    model_name="all-MiniLM-L6-v2"         # 80 MB model, 384-dim
)

# ── 3. Create or get a collection ─────────────────────────
collection = client.get_or_create_collection(
    name="my_rag_docs",
    embedding_function=ef,
    metadata={"hnsw:space": "cosine"}
)

# ── 4. Add documents (Chroma embeds them automatically) ───
collection.add(
    documents=[
        "Chroma is an open-source embedding database for AI applications.",
        "Sentence transformers create dense vector representations of text.",
        "RAG architecture retrieves context before generating an answer.",
        "pgvector extends PostgreSQL with native vector column support.",
    ],
    ids=["id1", "id2", "id3", "id4"],
    metadatas=[
        {"category": "chroma"},
        {"category": "embeddings"},
        {"category": "rag"},
        {"category": "pgvector"},
    ]
)

# ── 5. Query ───────────────────────────────────────────────
results = collection.query(
    query_texts=["Which database works well for RAG prototyping?"],
    n_results=2,
    where={"category": {"$in": ["chroma", "rag"]}},
    include=["documents", "distances", "metadatas"]
)

for doc, dist, meta in zip(
    results["documents"][0],
    results["distances"][0],
    results["metadatas"][0]
):
    print(f"[{meta['category']:10s}] dist={dist:.4f} → {doc[:60]}")

🐘 pgvector — Vectors Inside Your Existing PostgreSQL

Two Index Types: IVFFlat vs HNSW

Python + psycopg2: Full Setup and Search

# pip install psycopg2-binary pgvector openai
import psycopg2
from pgvector.psycopg2 import register_vector
from openai import OpenAI
import numpy as np
import os

conn   = psycopg2.connect(os.environ["DATABASE_URL"])
cur    = conn.cursor()
client = OpenAI()
register_vector(conn)

# ── 1. Enable extension and create table ──────────────────
cur.execute("CREATE EXTENSION IF NOT EXISTS vector;")
cur.execute("""
  CREATE TABLE IF NOT EXISTS articles (
    id         SERIAL PRIMARY KEY,
    title      TEXT NOT NULL,
    content    TEXT NOT NULL,
    category   TEXT,
    published  DATE,
    embedding  VECTOR(1536)     -- OpenAI text-embedding-3-small
  );
""")
conn.commit()

# ── 2. Create HNSW index ──────────────────────────────────
cur.execute("""
  CREATE INDEX IF NOT EXISTS articles_hnsw_idx
  ON articles USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);
""")
conn.commit()

# ── 3. Insert with embedding ──────────────────────────────
def embed(text: str):
    resp = client.embeddings.create(input=text, model="text-embedding-3-small")
    return np.array(resp.data[0].embedding)

rows = [
    ("Vector Databases 101",   "Embeddings enable semantic search at scale.",   "database"),
    ("pgvector Performance",   "HNSW gives 99% recall with fast query time.",    "database"),
    ("Building a RAG System",  "Retrieval augmented generation needs a vector store.", "rag"),
]
for title, content, category in rows:
    emb = embed(content)
    cur.execute(
        "INSERT INTO articles (title, content, category, embedding) VALUES (%s, %s, %s, %s)",
        (title, content, category, emb)
    )
conn.commit()

# ── 4. Nearest-neighbour search — pure SQL! ───────────────
query_vec = embed("How do I add vector search to my Postgres database?")
cur.execute("""
  SELECT title, category,
         1 - (embedding <=> %s::vector) AS cosine_similarity
  FROM   articles
  WHERE  category = 'database'
  ORDER  BY embedding <=> %s::vector
  LIMIT  3;
""", (query_vec, query_vec))

print(f"{'Title':<30} {'Category':<10} {'Similarity'}")
print("-" * 55)
for row in cur.fetchall():
    print(f"{row[0]:<30} {row[1]:<10} {row[2]:.4f}")
cur.close(); conn.close()

Master Comparison: Pinecone vs Weaviate vs Chroma vs pgvector

Which Vector Database Should You Choose? — Decision Flow

Performance Benchmarks — What the Numbers Say

Benchmarking vector databases is genuinely hard — results depend on hardware, dataset size, index parameters, query concurrency, and recall tolerance. The table below reflects community benchmarks on 1M vectors at 1,536 dimensions (representative of OpenAI embeddings), single-node, non-filtered queries.

Full RAG Architecture — The Complete Picture

Golden Rules — Vector Database Production Checklist