Choosing the Right Vector Database:
Pinecone vs Chroma vs Weaviate vs pgvector

Pinecone

Pinecone is a fully managed vector database purpose-built for scale. You never manage servers, indexes, or sharding — it just works at any scale.

Strengths: Sub-millisecond query latency at scale, automatic scaling, serverless and pod-based options, excellent SDKs, hybrid search built in.

Weaknesses: Proprietary (vendor lock-in), expensive at high scale, no self-hosting option.

import pinecone
from pinecone import Pinecone, ServerlessSpec

pc = Pinecone(api_key="your-api-key")

# Create an index (one-time setup)
pc.create_index(
    name="my-rag-index",
    dimension=1536,            # Must match your embedding model dimension
    metric="cosine",
    spec=ServerlessSpec(cloud="aws", region="us-east-1")
)

index = pc.Index("my-rag-index")

# Upsert vectors with metadata
vectors = [
    {
        "id": "doc-001",
        "values": embedding_vector,    # your embedding array
        "metadata": {
            "text": "The original document text",
            "source": "company_policy.pdf",
            "category": "HR",
            "date": "2026-01-15"
        }
    }
]
index.upsert(vectors=vectors, namespace="hr-docs")

# Query with metadata filtering
results = index.query(
    vector=query_embedding,
    top_k=5,
    namespace="hr-docs",
    filter={"category": {"$eq": "HR"}, "date": {"$gte": "2026-01-01"}},
    include_metadata=True
)

for match in results.matches:
    print(f"Score: {match.score:.3f} | {match.metadata['text'][:80]}")

Chroma

Chroma is the easiest vector database to get started with — it runs in-process (no server needed), persists to disk, and has excellent LangChain/LlamaIndex integration.

Strengths: Zero setup for development, embeds directly in Python, great for prototyping, good LangChain integration, free.

Weaknesses: Not designed for multi-billion vector scale, less production-battle-tested than Pinecone.

import chromadb
from chromadb.config import Settings

# In-memory (no persistence)
client = chromadb.EphemeralClient()

# With disk persistence
client = chromadb.PersistentClient(path="./chroma_db")

# Create a collection
collection = client.get_or_create_collection(
    name="my_documents",
    metadata={"hnsw:space": "cosine"}  # Use cosine similarity
)

# Add documents (Chroma can compute embeddings for you)
collection.add(
    documents=["Document text 1", "Document text 2", "Document text 3"],
    metadatas=[
        {"source": "doc1.pdf", "page": 1},
        {"source": "doc2.pdf", "page": 5},
        {"source": "doc3.pdf", "page": 2}
    ],
    ids=["id1", "id2", "id3"]
)

# Or add your own embeddings
collection.add(
    embeddings=[embedding1, embedding2],
    documents=["Text 1", "Text 2"],
    ids=["e1", "e2"]
)

# Query with filtering
results = collection.query(
    query_texts=["return policy"],
    n_results=3,
    where={"source": {"$eq": "policy.pdf"}},  # Metadata filter
    include=["documents", "distances", "metadatas"]
)

print(results["documents"])
print(results["distances"])  # Lower = more similar (L2 distance)

Weaviate

Weaviate is an open-source vector database with exceptional filtering capabilities, native hybrid search, and a rich schema system. It's the most feature-complete open-source option.

Strengths: Best-in-class hybrid search (BM25 + vectors), powerful filtering with GraphQL, multi-tenancy built in, native AI module integrations.

Weaknesses: More complex to set up and operate than Chroma, larger footprint.

import weaviate
from weaviate.classes.config import Configure, Property, DataType
from weaviate.classes.query import MetadataQuery, Filter

client = weaviate.connect_to_local()  # or connect_to_weaviate_cloud(...)

# Create a schema
collection = client.collections.create(
    name="Document",
    vectorizer_config=Configure.Vectorizer.none(),  # Bring your own vectors
    properties=[
        Property(name="content", data_type=DataType.TEXT),
        Property(name="source", data_type=DataType.TEXT),
        Property(name="category", data_type=DataType.TEXT),
    ]
)

# Insert objects
with collection.batch.dynamic() as batch:
    for doc, embedding in zip(documents, embeddings):
        batch.add_object(
            properties={
                "content": doc["text"],
                "source": doc["source"],
                "category": doc["category"]
            },
            vector=embedding
        )

# Hybrid search (BM25 + vector)
results = collection.query.hybrid(
    query="return policy",          # Text for BM25
    vector=query_embedding,          # Vector for semantic search
    alpha=0.5,                       # 0=BM25 only, 1=vector only
    filters=Filter.by_property("category").equal("HR"),
    limit=5,
    return_metadata=MetadataQuery(score=True, explain_score=True)
)

for obj in results.objects:
    print(f"Score: {obj.metadata.score:.3f} | {obj.properties['content'][:80]}")

pgvector (PostgreSQL)

pgvector is a PostgreSQL extension that adds vector similarity search to your existing Postgres database. No new infrastructure — just add the extension.

Strengths: Zero new infrastructure, full SQL power for filtering/joins, ACID transactions, no context switching between DB systems, familiar tooling.

Weaknesses: Not designed for pure vector search at massive scale (>10M vectors requires careful indexing), slower than purpose-built vector DBs at scale.

-- Enable pgvector
CREATE EXTENSION IF NOT EXISTS vector;

-- Create table with vector column
CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    content TEXT,
    source VARCHAR(255),
    category VARCHAR(100),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    embedding vector(1536)  -- dimension matches your embedding model
);

-- Create HNSW index for fast approximate search
CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops);

-- Insert a document with its embedding
INSERT INTO documents (content, source, category, embedding)
VALUES ('Our return policy allows 30-day returns.',
        'policy.pdf', 'HR', '[0.12, -0.45, ...]');

-- Semantic search with cosine similarity + metadata filter
SELECT
    content,
    source,
    1 - (embedding <=> '[query vector here]') AS similarity
FROM documents
WHERE category = 'HR'
    AND created_at > NOW() - INTERVAL '6 months'
ORDER BY embedding <=> '[query vector here]'
LIMIT 5;

# Python with asyncpg or SQLAlchemy
import asyncpg
import numpy as np

async def semantic_search(query_embedding: list[float], category: str, top_k: int = 5):
    conn = await asyncpg.connect("postgresql://user:pass@localhost/mydb")

    results = await conn.fetch("""
        SELECT content, source,
               1 - (embedding <=> $1::vector) AS similarity
        FROM documents
        WHERE category = $2
        ORDER BY embedding <=> $1::vector
        LIMIT $3
    """, str(query_embedding), category, top_k)

    await conn.close()
    return results