Vector Databases Compared: Pinecone vs Weaviate vs pgvector

Introduction

Vector databases are the backbone of modern AI applications, powering semantic search, recommendation systems, and retrieval-augmented generation. With several options available, choosing the right vector database for your use case can be challenging. This article compares three popular options: Pinecone, Weaviate, and pgvector.

Each takes a different approach. Pinecone is a fully managed cloud service. Weaviate is an open-source database with hybrid search capabilities. pgvector is a PostgreSQL extension that brings vector search to your existing database. Understanding their trade-offs helps you make the right choice.

Pinecone

Pinecone is a managed vector database designed for production use at scale. It handles infrastructure, scaling, and optimization automatically.

Key Features

• Serverless architecture: No capacity planning required
• Pod-based or serverless indexes: Choose based on your scale needs
• Metadata filtering: Filter vectors by metadata alongside similarity search
• Sparse vector support: Hybrid keyword and semantic search
• Namespaces: Partition data within an index for multi-tenancy

Setup and Usage

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
index.upsert(vectors=[
    {
        "id": "doc-1",
        "values": [0.1, 0.2, 0.3, ...],
        "metadata": {"source": "handbook.pdf", "page": 1}
    }
])

# Query with metadata filtering
results = index.query(
    vector=[0.15, 0.25, 0.35, ...],
    top_k=10,
    include_metadata=True,
    filter={"source": {"$eq": "handbook.pdf"}}
)

Pricing

Pinecone’s serverless plan charges based on storage and reads/writes:

• Storage: $0.10/GB/month
• Reads: $8.00 per 1M read units
• Writes: $2.00 per 1M write units

For moderate workloads (1M vectors, moderate query volume), expect $50-100/month.

Pros and Cons

Pros:

• Zero infrastructure management
• Excellent performance at scale
• Built-in metadata filtering
• Automatic scaling

Cons:

• Can get expensive at scale
• No self-hosting option
• Limited query flexibility compared to full databases

Weaviate

Weaviate is an open-source vector database that supports both vector and keyword search out of the box. It can be self-hosted or used as a managed service.

Key Features

• Hybrid search: Combines vector and BM25 keyword search
• Multi-modal: Store text, images, and other data types
• Built-in vectorization: Can generate embeddings automatically
• GraphQL API: Query using GraphQL
• Modules: Pre-built integrations with embedding models

Setup and Usage

import weaviate
from weaviate.classes.config import Property, DataType

# Connect to local instance
client = weaviate.connect_to_local()

# Create a collection
client.collections.create(
    name="Document",
    properties=[
        Property(name="content", data_type=DataType.TEXT),
        Property(name="source", data_type=DataType.TEXT),
        Property(name="page", data_type=DataType.INT)
    ]
)

# Add documents
collection = client.collections.get("Document")
collection.data.insert({
    "content": "Vector databases enable semantic search...",
    "source": "guide.pdf",
    "page": 1
})

# Hybrid search
results = collection.query.hybrid(
    query="how do vector databases work",
    query_properties=["content"],
    alpha=0.5,  # 0 = keyword, 1 = vector
    limit=10
)

for result in results.objects:
    print(result.properties["content"])

Pricing

• Self-hosted: Free (open-source), you pay for infrastructure
• Weaviate Cloud: Starts at $25/month for a sandbox, scales with usage
• Enterprise Cloud: Custom pricing with dedicated resources

Pros and Cons

Pros:

• Excellent hybrid search capabilities
• Self-hostable for data privacy
• Rich query options with GraphQL
• Multi-modal support

Cons:

• More complex to set up than Pinecone
• Requires infrastructure management if self-hosted
• Smaller community than PostgreSQL-based solutions

pgvector

pgvector is a PostgreSQL extension that adds vector similarity search capabilities. If you’re already using PostgreSQL, pgvector lets you add vector search without introducing a new database.

Key Features

• PostgreSQL integration: Works with your existing database
• Multiple index types: IVFFlat and HNSW indexes
• Multiple distance metrics: L2, cosine, and inner product
• Full SQL support: Combine vector search with any SQL query
• ACID compliance: Transactions, constraints, and joins work as expected

Setup and Usage

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

-- Create a table with vector column
CREATE TABLE documents (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    content TEXT,
    source TEXT,
    page INT,
    embedding vector(1536)
);

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

-- Insert vectors
INSERT INTO documents (content, source, page, embedding)
VALUES (
    'Vector databases enable semantic search',
    'guide.pdf',
    1,
    '[0.1, 0.2, 0.3, ...]'::vector
);

-- Similarity search with metadata filtering
SELECT content, source, page,
       1 - (embedding <=> '[0.15, 0.25, 0.35, ...]'::vector) AS similarity
FROM documents
WHERE source = 'guide.pdf'
ORDER BY embedding <=> '[0.15, 0.25, 0.35, ...]'::vector
LIMIT 10;

Pricing

pgvector is free and open-source. You only pay for your PostgreSQL hosting:

• Self-hosted: Free (infrastructure costs only)
• Managed PostgreSQL (RDS, Supabase, etc.): $15-100+/month depending on size
• No per-query costs: Unlike managed vector databases

Pros and Cons

Pros:

• No new infrastructure needed
• Full SQL capabilities (joins, aggregations, transactions)
• Free and open-source
• ACID compliance and reliability of PostgreSQL

Cons:

• Performance plateaus at very large scale (10M+ vectors)
• Limited to PostgreSQL’s scaling model
• Requires manual index tuning
• No built-in hybrid search (requires custom implementation)

Performance Comparison

Performance depends heavily on your dataset size, query patterns, and infrastructure. Here are general observations:

Metric	Pinecone	Weaviate	pgvector
1M vectors query latency	~20-50ms	~10-50ms	~10-50ms
10M+ vectors	Excellent	Good	Degrades
Hybrid search	Supported	Excellent	Manual
Metadata filtering	Fast	Fast	Fast (indexed)
Setup complexity	Low	Medium	Low (if PG exists)

When to Use Each

Choose Pinecone When

• You want zero infrastructure management
• Your team lacks database administration expertise
• You need to scale to tens of millions of vectors
• Budget is not the primary concern
• You need reliable, predictable performance

Choose Weaviate When

• Hybrid search is a core requirement
• You need multi-modal vector search (text + images)
• You want self-hosting for data privacy
• Your application benefits from GraphQL queries
• You need built-in vectorization modules

Choose pgvector When

• You already use PostgreSQL
• Your dataset is under 5-10 million vectors
• You need to combine vector search with relational queries
• Cost is a primary concern
• You want ACID compliance and transactional integrity
• Your team knows SQL

Hybrid Search Capabilities

Hybrid search combines semantic (vector) and keyword (BM25) search for better results:

• Pinecone: Supports sparse vectors for hybrid search, but requires managing sparse embeddings yourself
• Weaviate: Best-in-class hybrid search with a single alpha parameter to balance vector and keyword results
• pgvector: No built-in hybrid search, but you can combine tsvector full-text search with vector search using SQL:

SELECT content, 
       ts_rank(tsv, query) AS text_score,
       1 - (embedding <=> query_vec) AS vector_score,
       (ts_rank(tsv, query) * 0.3 + (1 - (embedding <=> query_vec)) * 0.7) AS hybrid_score
FROM documents, 
     plainto_tsquery('english', 'vector databases') query
ORDER BY hybrid_score DESC
LIMIT 10;

Conclusion

There is no single best vector database. Pinecone is ideal for teams that want a managed service with minimal operational overhead. Weaviate excels at hybrid search and multi-modal applications. pgvector is the pragmatic choice for teams already on PostgreSQL with moderate dataset sizes.

Start with the option that fits your current infrastructure and expertise. All three can handle millions of vectors effectively, and migrating between them is feasible if your needs change. The most important thing is to benchmark with your actual data and query patterns before committing.