Vector Databases Explained: pgvector vs Pinecone vs Weaviate

Why Vector Databases Matter

Every RAG system, semantic search engine, and recommendation system needs to answer the same question efficiently: "Given this 1536-dimensional vector, find the 10 most similar vectors in a collection of 10 million."

Brute-force exact search is O(n·d) — it doesn't scale. Vector databases use approximate nearest neighbor (ANN) algorithms (HNSW, IVF, LSH) to answer this query in milliseconds at scale.

The choice of vector database has significant implications for latency, cost, operational complexity, and how tightly your AI features integrate with the rest of your stack.

---

Option 1: pgvector (PostgreSQL Extension)

pgvector adds vector storage and ANN search to PostgreSQL. If you're already running Postgres, this is the lowest-friction path.

-- Enable the extension
CREATE EXTENSION IF NOT EXISTS vector;

-- Store embeddings alongside regular columns
CREATE TABLE documents (
    id          BIGSERIAL PRIMARY KEY,
    content     TEXT NOT NULL,
    source_url  TEXT,
    tags        TEXT[],
    embedding   VECTOR(1536),  -- OpenAI text-embedding-3-small dimensions
    created_at  TIMESTAMPTZ DEFAULT NOW()
);

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

Querying with metadata filtering:

SELECT
    id,
    content,
    source_url,
    1 - (embedding <=> $1::vector) AS similarity
FROM documents
WHERE
    tags @> ARRAY['architecture']
    AND created_at > NOW() - INTERVAL '90 days'
ORDER BY embedding <=> $1::vector
LIMIT 10;

Strengths:

• Zero new infrastructure if you already have Postgres
• Transactional consistency (vector updates are ACID)
• SQL joins between vectors and relational data
• No additional cost

Limitations:

• Performance degrades at >5M vectors without careful tuning
• HNSW index rebuild is expensive
• Not optimized for multi-tenant isolation

---

Option 2: Pinecone (Managed Cloud Service)

Pinecone is a purpose-built vector database as a service. Zero operational overhead.

from pinecone import Pinecone, ServerlessSpec

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

# Create an index
pc.create_index(
    name="documents",
    dimension=1536,
    metric="cosine",
    spec=ServerlessSpec(cloud="aws", region="us-east-1"),
)

index = pc.Index("documents")

# Upsert vectors with metadata
index.upsert(
    vectors=[
        {
            "id": "doc_001",
            "values": embedding_vector,  # list of 1536 floats
            "metadata": {
                "content": "Document text here...",
                "tags": ["architecture", "cloud"],
                "source": "internal-wiki",
            }
        }
    ],
    namespace="production"
)

# Query with metadata filtering
results = index.query(
    vector=query_embedding,
    top_k=10,
    filter={"tags": {"$in": ["architecture"]}},
    include_metadata=True,
    namespace="production"
)

Strengths:

• Best-in-class query performance at scale (>50M vectors)
• Serverless tier scales to zero
• Built-in namespace support for multi-tenancy
• No index management

Limitations:

• Vendor lock-in
• Cost scales with storage + queries (can surprise you)
• Metadata is not queryable with full SQL expressiveness

---

Option 3: Weaviate (Open-Source, Self-Hosted or Cloud)

Weaviate is a full-featured vector database with a built-in data model. Self-hostable or available as a managed cloud service.

import weaviate
from weaviate.classes.config import Configure, VectorDistances

client = weaviate.connect_to_local()

# Define a collection (schema)
client.collections.create(
    name="Document",
    vectorizer_config=Configure.Vectorizer.none(),
    properties=[
        weaviate.classes.config.Property(name="content", data_type=weaviate.classes.config.DataType.TEXT),
        weaviate.classes.config.Property(name="source", data_type=weaviate.classes.config.DataType.TEXT),
        weaviate.classes.config.Property(name="tags", data_type=weaviate.classes.config.DataType.TEXT_ARRAY),
    ],
)

# Insert with pre-computed vector
docs = client.collections.get("Document")
docs.data.insert(
    properties={"content": "...", "source": "wiki", "tags": ["cloud"]},
    vector=embedding_vector,
)

# Hybrid search (vector + BM25 keyword)
results = docs.query.hybrid(
    query="kubernetes deployment strategies",
    vector=query_embedding,
    alpha=0.7,  # 0 = pure BM25, 1 = pure vector
    limit=10,
)

Strengths:

• Open-source with self-hosting option (no vendor lock-in)
• Native hybrid search (vector + BM25) out of the box
• GraphQL and REST APIs
• Multi-modal (text, images, audio)

Limitations:

• Operationally heavier than pgvector
• Learning curve for the data model

---

Decision Guide

Use Case	Recommendation
Already on Postgres, <5M vectors	pgvector — lowest friction
>10M vectors, managed service preferred	Pinecone — best performance
Open-source required, hybrid search needed	Weaviate — most flexible
Multi-modal (text + images)	Weaviate
Maximum SQL query flexibility	pgvector

Start with pgvector. It handles most production RAG use cases. Migrate to Pinecone or Weaviate only when you hit pgvector's limits.