Vector Databases for AI: pgvector vs Pinecone vs Weaviate
AI applications need to store and search embeddings — high-dimensional vectors that represent text, images, and other data as numerical arrays. Traditional databases can’t efficiently search across millions of 1536-dimensional vectors to find the most similar ones. Vector databases are purpose-built for this: they use specialized indexes (HNSW, IVFFlat) to find nearest neighbors in milliseconds. This guide compares three leading options — pgvector, Pinecone, and Weaviate — with representative benchmarks and practical selection criteria.
How Vector Search Works
When you generate an embedding from text using a model like OpenAI’s text-embedding-3-small, you get a 1536-dimensional float array. Semantically similar texts produce vectors that are close together in this high-dimensional space. Vector search finds the K nearest vectors to a query vector — essentially answering “what’s most similar to this?”
The challenge is scale. Brute-force comparison against every vector is O(n) — fine for 10,000 vectors, unusable for 10 million. These purpose-built engines solve this with Approximate Nearest Neighbor (ANN) algorithms that trade a small accuracy loss (typically 95-99% recall) for orders-of-magnitude speed improvements. HNSW (Hierarchical Navigable Small World) graphs are the most popular: they build a multi-layer graph structure that enables sub-millisecond search across millions of vectors.
Moreover, production AI systems need more than just vector search. They need metadata filtering (find similar vectors BUT only in category X), hybrid search (combine vector similarity with keyword matching), and real-time updates (add new vectors without rebuilding the entire index). Each database handles these requirements differently.
Distance Metrics and Why They Matter
Before choosing an engine, you must choose how “similarity” is measured, because the wrong metric quietly destroys recall. The three dominant options are cosine similarity, dot product (inner product), and Euclidean (L2) distance. Cosine ignores vector magnitude and compares only direction, which suits most text-embedding models because those models are not magnitude-normalized in a meaningful way for relevance.
Crucially, the metric must match how the embedding model was trained. OpenAI’s embeddings are normalized to unit length, so cosine and dot product return identical rankings — but dot product is cheaper to compute. By contrast, some open models like the older Sentence-Transformers checkpoints expect cosine specifically. If you index with L2 distance against vectors meant for cosine, your top results will look plausible but silently miss the genuinely closest matches.
There is also a normalization gotcha worth flagging. If you normalize vectors at insert time but forget to normalize the query vector, every search degrades. A common pattern in production teams is to normalize once in the ingestion pipeline and assert vector length in a test, so a model swap that changes dimensionality or scale fails loudly rather than silently.
Tuning HNSW: ef_construction, ef_search, and m
The three HNSW parameters control the recall-versus-latency curve, and most teams leave them at defaults that are wrong for their workload. The m parameter sets how many neighbors each node links to; higher m improves recall but increases index size and build memory. The ef_construction value controls how thoroughly the graph is built, while ef_search (queried at runtime) controls how widely each search explores.
Importantly, ef_search is tunable per query, so you can dial recall up for a high-stakes search and down for a cheap autocomplete. The docs recommend starting around ef_search = 40 and raising it until recall plateaus. Benchmarks typically show recall climbing from roughly 90% to 99% as you move ef_search from 40 to 200, at the cost of two to three times the query latency.
-- Tune HNSW recall vs latency in pgvector
-- Higher ef_search = better recall, slower queries (set per session)
SET hnsw.ef_search = 100;
-- Build-time parameters: bigger m and ef_construction = better recall,
-- slower build, more memory. Build on a machine with ample RAM.
CREATE INDEX ON documents
USING hnsw (embedding vector_cosine_ops)
WITH (m = 24, ef_construction = 256);
-- Measure recall empirically: compare HNSW results against an exact scan
SET hnsw.ef_search = 40;
EXPLAIN ANALYZE
SELECT id FROM documents
ORDER BY embedding <=> '[0.1, 0.2, 0.3]'::vector
LIMIT 10;For IVFFlat (the alternative index), the key knob is the number of lists and the number of probes. IVFFlat builds faster and uses less memory than HNSW but generally gives lower recall at the same speed, which is why HNSW has become the default recommendation for most read-heavy workloads.
pgvector: Vector Search in PostgreSQL
pgvector adds vector operations to PostgreSQL — the database you probably already run. No new infrastructure, no new operational knowledge, no data synchronization between your relational data and vector store. Your vectors live alongside your regular tables with full SQL support.
-- Enable pgvector extension
CREATE EXTENSION vector;
-- Create table with vector column
CREATE TABLE documents (
id SERIAL PRIMARY KEY,
title TEXT NOT NULL,
content TEXT NOT NULL,
category TEXT,
embedding vector(1536), -- OpenAI embedding dimension
created_at TIMESTAMP DEFAULT NOW()
);
-- Create HNSW index for fast similarity search
CREATE INDEX ON documents
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 200);
-- Semantic search with metadata filtering
SELECT id, title, content,
1 - (embedding <=> query_embedding) AS similarity
FROM documents
WHERE category = 'engineering'
AND created_at > '2025-01-01'
ORDER BY embedding <=> '[0.1, 0.2, ...]'::vector
LIMIT 10;
-- Hybrid search: combine semantic + full-text
SELECT id, title,
(0.7 * (1 - (embedding <=> query_vec))) +
(0.3 * ts_rank(to_tsvector(content), plainto_tsquery('kubernetes scaling')))
AS combined_score
FROM documents
WHERE to_tsvector(content) @@ plainto_tsquery('kubernetes scaling')
ORDER BY combined_score DESC
LIMIT 10;pgvector strengths: zero additional infrastructure, full SQL support, ACID transactions, works with your existing PostgreSQL tooling (backups, replication, monitoring), and metadata filtering is just WHERE clauses. pgvector weaknesses: performance degrades past roughly 5 million vectors, HNSW index build is memory-intensive, no built-in sharding for horizontal scaling, and concurrent updates to HNSW indexes can be slower than purpose-built solutions.
One subtle trap deserves attention: pgvector’s metadata filtering interacts awkwardly with HNSW. Because the index walks the graph first and filters afterward, a highly selective WHERE clause can force the engine to scan far more candidates than your LIMIT suggests, or even fall back to an exact scan. The mitigation is partial indexes per common filter value, or the newer iterative-scan modes that let you trade recall for filter selectivity.
Pinecone: Managed Vector Database
Pinecone is a fully managed vector database — no infrastructure to manage, no indexes to tune, no scaling to configure. You send vectors through an API and query them. It’s optimized for production workloads with features like namespaces, metadata filtering, and sparse-dense hybrid search.
from pinecone import Pinecone, ServerlessSpec
# Initialize Pinecone
pc = Pinecone(api_key="your-api-key")
# Create 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-1",
"values": embedding_vector, # 1536-dim float list
"metadata": {
"title": "Kubernetes Scaling Guide",
"category": "engineering",
"author": "jane",
"date": "2026-01-15"
}
}
], namespace="engineering-docs")
# Query with metadata filter
results = index.query(
vector=query_embedding,
top_k=10,
namespace="engineering-docs",
filter={
"category": {"$eq": "engineering"},
"date": {"$gte": "2025-01-01"}
},
include_metadata=True
)
for match in results.matches:
print(f"{match.id}: {match.score:.3f} - {match.metadata['title']}")Pinecone strengths: zero operational overhead, consistent low-latency queries at any scale, serverless pricing (pay per query), excellent metadata filtering, and built-in namespaces for multi-tenancy. Pinecone weaknesses: vendor lock-in (proprietary, no self-hosting), limited keyword-style hybrid search compared to dedicated engines, cost can be significant at scale, less query flexibility than SQL, and data must leave your infrastructure.
Weaviate: Open-Source with Built-in ML
Weaviate is an open-source vector database with built-in vectorization — it can generate embeddings automatically using integrated ML models. You can send raw text and Weaviate handles the embedding generation, storage, and search in one step. Additionally, it supports GraphQL queries, hybrid search (vector + BM25), and multi-modal search (text + images).
import weaviate
import weaviate.classes as wvc
# Connect to Weaviate
client = weaviate.connect_to_local()
# Create collection with built-in vectorizer
documents = client.collections.create(
name="Document",
vectorizer_config=wvc.config.Configure.Vectorizer.text2vec_openai(
model="text-embedding-3-small"
),
properties=[
wvc.config.Property(name="title", data_type=wvc.config.DataType.TEXT),
wvc.config.Property(name="content", data_type=wvc.config.DataType.TEXT),
wvc.config.Property(name="category", data_type=wvc.config.DataType.TEXT),
]
)
# Add data — Weaviate generates embeddings automatically
documents.data.insert_many([
{"title": "K8s Scaling", "content": "Guide to scaling...",
"category": "engineering"},
{"title": "ML Pipeline", "content": "Building production...",
"category": "data-science"}
])
# Hybrid search: vector similarity + BM25 keyword matching
results = documents.query.hybrid(
query="kubernetes autoscaling best practices",
alpha=0.7, # 70% vector, 30% keyword
filters=wvc.query.Filter.by_property("category").equal("engineering"),
limit=10,
return_metadata=wvc.query.MetadataQuery(score=True)
)
for obj in results.objects:
print(f"{obj.properties['title']}: {obj.metadata.score:.3f}")Weaviate strengths: open-source with self-hosting option, built-in vectorization (no separate embedding pipeline), native hybrid search (vector + BM25), GraphQL API, multi-modal support, and an active community. Weaviate weaknesses: higher operational complexity than pgvector (separate infrastructure), memory-intensive for large datasets, less mature than Pinecone for hands-off managed deployments, and built-in vectorization adds latency to inserts.
Cost, Scaling, and Operational Trade-offs
The honest comparison is not about raw query speed — all three are fast enough for most applications. It is about where the cost lands. pgvector’s cost is mostly RAM, because HNSW indexes want to live in memory; a 10-million-vector index at 1536 dimensions can demand tens of gigabytes of RAM before quantization. Pinecone’s cost is per-query and per-stored-vector, which is predictable but climbs steadily as traffic grows. Weaviate self-hosted shifts cost to engineering time spent operating, upgrading, and capacity-planning a stateful cluster.
Memory pressure is the single most common production surprise. Teams prototype with a few hundred thousand vectors, see millisecond latencies, and then watch p99 latency explode when the working set no longer fits in RAM and the index starts paging from disk. Mitigations include scalar or product quantization (which trades a few points of recall for a large memory reduction), sharding across nodes, and pruning stale vectors aggressively rather than letting the index grow unbounded.
Another trade-off is index rebuild behavior. HNSW does not support true deletes cheaply — deletions are tombstoned and only reclaimed on rebuild. Consequently, a high-churn dataset (frequent re-embedding, frequent deletes) slowly bloats and degrades until you reindex. If your data is append-mostly, this rarely bites; if you re-embed your whole corpus monthly, plan the rebuild window deliberately.
When NOT to Use a Dedicated Vector Engine
Not every “search” problem needs ANN. If you have fewer than roughly 100,000 vectors, an exact brute-force scan in NumPy or a plain SQL ordering is simpler, gives 100% recall, and avoids an entire class of tuning bugs. The complexity of HNSW only pays off once linear scan becomes the bottleneck.
Likewise, if your real need is exact keyword lookup, faceted filtering, or structured retrieval, a traditional full-text engine like Elasticsearch or even PostgreSQL’s tsvector will serve you better and more predictably than semantic search. Semantic similarity is powerful, but it is also fuzzy — for “find the invoice numbered 88421” you want an exact index, not a nearest-neighbor guess. A common mistake is reaching for embeddings when a WHERE id = ? would do.
Finally, avoid splitting your relational data and your vectors across two systems prematurely. The synchronization burden — keeping a row and its embedding consistent across a database and a separate vector store — is a real source of bugs. Starting with pgvector keeps both in one transactional system; you can graduate to a dedicated engine when scale genuinely demands it.
Choosing the Right Vector Database
Choose pgvector when: you already use PostgreSQL, your vector count is under 5 million, you need ACID transactions alongside vector search, you want to avoid new infrastructure, or you need complex SQL queries joining vector results with relational data. It’s the simplest path for most applications starting with AI features.
Choose Pinecone when: you want zero operational burden, you need consistent performance at 10M+ vectors, you’re building a SaaS product with multi-tenant vector isolation, or your team doesn’t have infrastructure expertise. The managed experience is genuinely excellent.
Choose Weaviate when: you need hybrid search (vector + keyword), you want built-in embedding generation, you prefer open-source with self-hosting control, you need multi-modal search, or you want GraphQL for complex queries. Weaviate is the most feature-rich open-source option.
Related Reading:
- RAG Architecture Patterns for Production
- Prompt Engineering for Production AI
- Apache Iceberg Data Lakehouse Guide
- Embedding Models Comparison: OpenAI, Cohere, BGE
Resources:
In conclusion, the right vector database depends on your scale, infrastructure preferences, and feature needs. Start with pgvector if you already use PostgreSQL — it handles most use cases up to 5 million vectors with zero new infrastructure. Move to Pinecone or Weaviate when you need dedicated performance, advanced features, or need to scale beyond what PostgreSQL can handle efficiently.