Choosing a Vector Database for Production: Pinecone vs Weaviate vs pgvector

The vector database ecosystem moves fast enough that a lot of the content comparing these tools is either out of date or written by someone with a vested interest in one of them. This post is neither. It's the comparison we use internally when scoping RAG and embedding-based systems for production deployments.

The thesis upfront: pgvector inside your existing PostgreSQL is the right default for most mid-market deployments. Pinecone is the right call in specific circumstances. Weaviate earns its complexity for a narrower set of use cases. Most teams choose Pinecone because it's what the LangChain quickstart uses, not because they evaluated the tradeoffs - and that decision costs them in unexpected ways.

What You're Actually Choosing Between

These three tools aren't equivalently positioned. Understanding what each one optimises for clarifies when each one wins.

Pinecone is a fully managed, purpose-built vector database. The value proposition is developer experience and operational simplicity: you provision an index, upsert vectors, query, and never think about infrastructure. Pinecone handles replication, scaling, and uptime. The tradeoff is cost at scale and a deliberately constrained feature set - it does vector search exceptionally well and very little else.

Weaviate is an open-source vector database with a richer feature set: hybrid search combining BM25 and dense retrieval, an extensive module ecosystem for generating embeddings inside the database, and a GraphQL API for complex queries. You can self-host or use Weaviate Cloud. The power comes with operational complexity - self-hosting Weaviate is meaningfully more work than running PostgreSQL.

pgvector is a PostgreSQL extension. It's not a vector database - it adds vector similarity search to a database you probably already run. The extension adds vector as a native column type and two index types for approximate nearest neighbour search. What you gain is the full power of SQL: joins, transactions, filtering on any column, integration with your existing schemas. What you give up relative to dedicated solutions is scale ceiling and some retrieval quality at the high end.

Pinecone: When the DX Is Worth the Price

Pinecone has legitimately excellent developer experience. Provisioning an index, upserting a million vectors, and running queries against it can be done in an afternoon. The Pythonic client is clean, the documentation is good, and the managed infrastructure means you don't own the operational surface area.

For teams that are moving fast, don't want to manage infrastructure, and are early in their vector search journey, this removes real friction. The time-to-first-working-search is faster than any alternative.

Two things to understand before committing.

Pinecone's filtering is metadata filtering, not SQL. You can filter on metadata fields you attach to each vector at upsert time - strings, numbers, booleans. What you can't do is filter on conditions that live in your relational database. If you want to find the top-k semantically similar documents AND filter to only those belonging to a specific client AND exclude documents that have been marked inactive in your PostgreSQL documents table - that query requires a roundtrip. You fetch from Pinecone, then post-filter in your application, which either wastes the k-limit or requires over-fetching with a larger k and filtering down. Neither is clean.

For RAG systems where each user has their own isolated namespace of documents, this is fine. For systems where vector search results need to be joined against relational data with complex conditions, it creates friction that grows with the complexity of your filtering logic.

The cost model changed significantly with Pinecone Serverless, which moved from pod-based pricing (expensive at rest) to consumption-based pricing (cheap at low query volume, scales with usage). For a new project, Serverless is almost always the right Pinecone starting point. Understand what your query volume will look like at scale before locking in, and model the cost against pgvector's marginal cost of zero additional infrastructure.

When Pinecone Is the Right Call

High query volume - millions of queries per day - against a large vector index where you need guaranteed latency and don't want to own the infrastructure. Organisations where operational overhead is genuinely more expensive than Pinecone's pricing. Systems where the vector data is largely decoupled from relational queries and metadata filtering is sufficient. Teams with limited DevOps capacity who need to ship something reliable without becoming vector database experts.

Weaviate: Hybrid Search Is the Real Reason to Use It

Weaviate's standout capability is hybrid search - combining BM25 keyword search with dense vector retrieval in a single query, with configurable weighting between the two. For most RAG applications, this produces better retrieval quality than pure vector search.

Here's why. Pure vector search finds semantically similar content. It handles paraphrase well - "how do I cancel my subscription" retrieves content about account termination even if the word "cancel" never appears. What it handles poorly is exact term matching. A user asking about a specific product code, a person's name, or a precise legal clause wants lexical match, not semantic approximation. BM25 handles this correctly. Hybrid search gives you both, weighted appropriately for your use case.

If retrieval quality is the critical variable in your application - legal document Q&A, technical support against a precise knowledge base, financial document extraction - Weaviate's hybrid search is worth evaluating seriously. The quality improvement over pure vector search is real and measurable.

# Weaviate hybrid query example
result = (
    client.query
    .get("Document", ["content", "source", "date"])
    .with_hybrid(
        query="termination clause notice period",
        alpha=0.5  # 0 = pure BM25, 1 = pure vector, 0.5 = balanced
    )
    .with_limit(5)
    .do()
)

The alpha parameter is the lever you tune per use case. Keyword-heavy queries want lower alpha. Semantic/conceptual queries want higher. Getting this right for your application requires evaluation on real queries against your real corpus.

The Operational Cost

Self-hosted Weaviate requires more sustained operational attention than PostgreSQL. The memory footprint for HNSW indices is substantial - Weaviate's default in-memory indexing means the entire index needs to fit in RAM for optimal performance. For large corpora, this has hardware implications. Weaviate Cloud removes the operational burden but adds cost and external dependency.

The GraphQL API has a steeper learning curve than a SQL interface. Teams comfortable with graph query patterns will adapt quickly. Teams expecting a SQL-like interface will have a period of friction.

When Weaviate Is the Right Call

Retrieval quality is the primary concern and hybrid search meaningfully improves it for your corpus. You're comfortable managing the operational overhead or using Weaviate Cloud. Your queries are complex enough to benefit from Weaviate's filtering but don't require joining against a relational database. The embedding module ecosystem is valuable for your use case (generating embeddings at ingest time inside the database rather than in your application layer).

pgvector: The Underrated Default

Here's the case for pgvector, stated plainly.

Most mid-market RAG applications have fewer than five million vectors. At that scale, a well-configured PostgreSQL instance with pgvector and an HNSW index returns approximate nearest neighbour results in single-digit milliseconds. That's fast enough for any user-facing application. You don't need a dedicated vector database for this query volume.

What you get in return for staying in PostgreSQL:

Full SQL filtering. Your vector search results are rows in a PostgreSQL table. You can join them against any other table in your database, filter on any column with any SQL condition, use CTEs, window functions, and transactions. If you're building a RAG system for a multi-tenant SaaS product and need to scope document retrieval to the current user's organisation with filtering on document status, created date, and permission level - that's one SQL query.

SELECT
    d.id,
    d.content,
    d.source,
    1 - (e.embedding <=> $1::vector) AS similarity
FROM document_embeddings e
JOIN documents d ON e.document_id = d.id
WHERE d.organisation_id = $2
  AND d.status = 'active'
  AND d.created_at > NOW() - INTERVAL '2 years'
ORDER BY e.embedding <=> $1::vector
LIMIT 10;

That query - semantic similarity search with three relational filters and a join - runs natively. In Pinecone you're making two roundtrips and post-filtering in application code. The difference compounds as filtering conditions grow.

Zero new infrastructure. If you're already on PostgreSQL, pgvector is CREATE EXTENSION vector; and a migration. No new service to provision, monitor, back up, or pay for. Operational complexity stays constant.

ACID transactions. Vector upserts and relational updates can be wrapped in the same transaction. Atomicity across your embedding index and your source data is straightforward to guarantee. With a separate vector database, keeping the two in sync requires explicit coordination logic.

Index Choice: IVFFlat vs HNSW

pgvector supports two index types. The choice matters for production.

IVFFlat partitions vectors into lists and searches only the most relevant lists for each query. Faster to build, lower memory footprint, and reasonably good recall if the lists parameter is tuned correctly. The rule of thumb: lists should be approximately rows / 1000 for up to one million rows, and sqrt(rows) beyond that. Requires a SET ivfflat.probes at query time to control the recall/speed tradeoff.

HNSW (Hierarchical Navigable Small World) builds a graph structure that supports very fast approximate nearest neighbour queries with better recall than IVFFlat at equivalent query time. Higher memory overhead and slower index build time. For production systems where you're not rebuilding the index constantly and can afford the memory, HNSW is the right default.

-- HNSW index with cosine distance (for normalised embeddings)
CREATE INDEX ON document_embeddings
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

-- Query with cosine similarity
SELECT document_id, 1 - (embedding <=> $1) AS similarity
FROM document_embeddings
ORDER BY embedding <=> $1
LIMIT 20;

The m parameter controls the number of connections per node - higher values improve recall at the cost of memory and build time. ef_construction controls build-time search depth. Default values are reasonable starting points; tune based on your recall benchmarks.

The Honest Ceiling

pgvector has limits. At tens of millions of vectors on a standard instance, query latency starts climbing and memory pressure from the HNSW index becomes a real infrastructure concern. Recall quality also degrades relative to dedicated solutions at these scales.

For most mid-market RAG systems - internal knowledge bases, product catalogues, document Q&A for teams of hundreds rather than millions of users - you won't hit this ceiling. If your initial estimates put you comfortably under five million vectors and query volume is in the tens of thousands per day rather than millions, pgvector covers the use case without reservation.

If you're building infrastructure that genuinely expects hundred-million-scale vector counts or multi-million daily queries, start with Pinecone. Don't optimise for pgvector's ceiling before you know you're approaching it.

The Decision in Practice

How we actually make this call on new projects:

If the team is already on PostgreSQL and the expected vector count is under five million: start with pgvector. Evaluate whether you need to migrate when you have real usage data, not before.

If retrieval quality is the primary product differentiator and hybrid search would meaningfully improve it for the corpus: evaluate Weaviate seriously. Build a benchmark on a representative sample of your documents before committing.

If the team is Python-first and moving fast, operational overhead is a real constraint, and vector data doesn't need complex relational joins: Pinecone Serverless is a reasonable starting point. Budget for migration if you scale past the point where the cost model stops making sense.

The worst outcome is choosing a dedicated vector database early because the tutorials use it, then discovering you've added operational complexity and cost for a problem that pgvector would have solved with a single extension and a migration.

If you're scoping a RAG system and want an honest assessment of which storage layer fits your use case, query volume, and existing infrastructure, get in touch. This is a decision that's worth getting right before you build the ingestion pipeline around the wrong choice.