Vector Databases Deep Dive: Pinecone vs pgvector vs Weaviate

Why Vector Databases Exist

Traditional databases find exact matches. Vector databases find similar things. When you embed text, images, or audio into high-dimensional vectors using a model like text-embedding-3-large, semantically similar content clusters near each other in vector space. A vector database specializes in finding the k nearest neighbors to a query vector — fast, at scale.

This capability is the backbone of RAG systems, semantic search, recommendation engines, and multimodal search. The choice of vector database shapes your system's latency, cost, operational complexity, and query expressiveness.

The Three Contenders

Pinecone: Managed Simplicity

Pinecone is a fully managed, purpose-built vector database. You get an API, a namespace, and no infrastructure to manage.

Indexing:

import pinecone

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

# Upsert vectors with metadata
index.upsert(vectors=[
    {
        "id": "doc-001",
        "values": [0.1, 0.2, ...],  # 1536 dimensions for ada-002
        "metadata": {
            "source": "internal-wiki",
            "category": "engineering",
            "created_at": "2026-05-01"
        }
    }
])

Querying with metadata filtering:

results = index.query(
    vector=query_embedding,
    top_k=5,
    filter={
        "category": {"$eq": "engineering"},
        "created_at": {"$gte": "2026-01-01"}
    },
    include_metadata=True
)

When to use Pinecone:

• You want zero operational overhead
• Your team doesn't have database expertise
• Billion-scale vectors where managing your own index is impractical
• Budget allows for managed service pricing ($70/month for 1M vectors at p1.x1 pod)

Limitations: No self-hosting option, data residency concerns for regulated industries, opaque internal scaling, vendor lock-in on query API.

pgvector: Postgres-Native

pgvector is a PostgreSQL extension that adds a vector type and HNSW/IVFFlat index types. If you are already running PostgreSQL, this is a zero-infrastructure-addition option.

Setup:

CREATE EXTENSION vector;

CREATE TABLE document_embeddings (
  id          BIGSERIAL PRIMARY KEY,
  content     TEXT,
  embedding   vector(1536),
  metadata    JSONB,
  created_at  TIMESTAMPTZ DEFAULT now()
);

-- HNSW index (fast query, slower insert)
CREATE INDEX ON document_embeddings
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

Semantic search:

SELECT id, content, metadata,
       1 - (embedding <=> $1) AS similarity
FROM document_embeddings
WHERE metadata->>'category' = 'engineering'
  AND created_at > '2026-01-01'
ORDER BY embedding <=> $1
LIMIT 5;

The <=> operator is cosine distance. Also available: <-> (L2), <#> (inner product).

Combined semantic + lexical search:

-- Hybrid search: vector similarity + full-text ranking
SELECT id, content,
       ts_rank(to_tsvector('english', content), query) AS text_rank,
       1 - (embedding <=> $1) AS vec_similarity
FROM document_embeddings,
     websearch_to_tsquery('english', $2) query
WHERE to_tsvector('english', content) @@ query
ORDER BY (0.4 * ts_rank(...) + 0.6 * (1 - (embedding <=> $1))) DESC
LIMIT 10;

This hybrid approach often outperforms pure vector search on precision-critical tasks.

When to use pgvector:

• Already on PostgreSQL (RDS, Supabase, Neon, self-hosted)
• Need transactional consistency between vector and relational data
• Hybrid search (vector + full-text) is a requirement
• Compliance requires data residency control
• <10M vectors (HNSW scales well into this range)

Limitations: Performance degrades at 100M+ vectors without careful sharding. Index build is memory-intensive. Query performance is slower than purpose-built systems at large scale.

Weaviate: Semantic-Native, Open Source

Weaviate is an open-source vector database with a graph-like data model and built-in module support for embedding generation, re-ranking, and multimodal search.

Schema definition:

{
  "classes": [{
    "class": "Document",
    "vectorizer": "text2vec-openai",
    "moduleConfig": {
      "text2vec-openai": {
        "model": "text-embedding-3-large",
        "dimensions": 1024
      }
    },
    "properties": [
      { "name": "content", "dataType": ["text"] },
      { "name": "category", "dataType": ["string"] },
      { "name": "createdAt", "dataType": ["date"] }
    ]
  }]
}

Querying:

{
  Get {
    Document(
      nearText: { concepts: ["circuit breaker patterns"] }
      where: {
        path: ["category"]
        operator: Equal
        valueString: "engineering"
      }
      limit: 5
    ) {
      content
      category
      _additional { certainty distance }
    }
  }
}

Weaviate's GraphQL API is opinionated but expressive. The built-in vectorizer modules mean you can skip the embedding step — send raw text, get results.

When to use Weaviate:

• Multimodal search (text + images + audio)
• You want auto-vectorization (no separate embedding step)
• Graph relationships between objects matter
• Self-hosted open source is a requirement

Limitations: GraphQL API has a learning curve. Higher resource requirements than pgvector. Less ecosystem tooling than Pinecone.

Performance Comparison

Based on HNSW-indexed similarity search at recall@10:

System	1M vectors (QPS)	10M vectors (QPS)	Latency p99	Self-host
Pinecone (p1.x1)	~500	~300	30–80 ms	No
pgvector (HNSW)	~800	~200	20–100 ms	Yes
Weaviate	~600	~250	25–90 ms	Yes

Numbers are approximate and workload-dependent. pgvector wins at low-to-medium scale on a well-tuned instance; Pinecone wins at large scale where managed infrastructure absorbs complexity.

Decision Framework

Already on PostgreSQL?  → pgvector (add no new infrastructure)
Need managed, billion-scale, no ops?  → Pinecone
Need multimodal or graph relationships?  → Weaviate
Need hybrid vector + full-text search?  → pgvector
Need self-hosted, open source, feature-rich?  → Weaviate or Qdrant

The "right" choice at 100K vectors looks different than the right choice at 100M. Start with pgvector — migrate later when you have evidence that you need specialized infrastructure.