Every retrieval-augmented generation project eventually asks: which vector database? The market is crowded — dedicated engines like Qdrant, Weaviate and Milvus, managed services like Pinecone, and vector extensions for databases you already run, most notably pgvector for PostgreSQL. Benchmarks published by vendors rarely match your workload. This guide explains what actually differs between options, which questions decide the choice, and why for many business projects the answer is "the PostgreSQL you already have".
What a vector database does
A vector database stores embeddings — arrays of numbers that represent the meaning of text, images or other data — and answers the query "find the k vectors most similar to this one". Exact search compares the query with every vector; that is fine for tens of thousands of items but too slow for millions. Production systems use approximate nearest neighbour (ANN) indexes that trade a little recall for orders-of-magnitude speed.
On top of that core, a production vector store needs:
- Metadata filtering: "similar documents, but only for tenant 42, language uk, updated after 2026-01-01".
- Hybrid search: combining vector similarity with keyword (BM25) relevance.
- CRUD and consistency: updating and deleting documents without rebuilding the index.
- Operations: backups, replication, monitoring, access control.
How ANN indexes work, briefly
HNSW (Hierarchical Navigable Small World) builds a multi-layer graph where each vector links to its neighbours; search starts at a sparse top layer and descends (Malkov & Yashunin, 2016). It offers excellent recall and latency and supports incremental inserts, at the cost of memory: the graph lives in RAM alongside the vectors. Key parameters: m (links per node), ef_construction (build quality) and ef_search (query-time recall/speed trade-off).
IVF (inverted file) clusters vectors and searches only the closest clusters. It builds faster and uses less memory, but recall depends on choosing how many clusters to probe, and it needs retraining as data changes.
Quantization compresses vectors — scalar (float32 → int8), product quantization, or binary — cutting memory by 4–32x at some recall cost, usually recovered by re-scoring top candidates with full vectors. DiskANN-style indexes keep most data on SSD for very large collections.
Independent comparisons of ANN algorithms are published at ann-benchmarks.com. The practical lesson: index choice and parameters matter more than database brand for raw search performance.
The contenders
| Option | Type | Strengths | Watch out for |
|---|---|---|---|
| pgvector | PostgreSQL extension | One database, SQL joins, transactions, existing backups and tooling; HNSW and IVFFlat | Very large collections and heavy filtered search need tuning; vacuum and memory planning |
| Qdrant | Dedicated engine (Rust), OSS + cloud | Strong filtering integrated into HNSW, quantization, sparse vectors for hybrid | Another system to operate |
| Weaviate | Dedicated engine (Go), OSS + cloud | Built-in hybrid search, modules for vectorisation, multi-tenancy | Higher memory footprint; opinionated schema |
| Milvus | Distributed engine, OSS + Zilliz cloud | Billions of vectors, many index types incl. GPU and DiskANN | Operational complexity of a distributed system |
| Pinecone | Fully managed SaaS | No operations, serverless pricing, good developer experience | Vendor lock-in, data residency, cost at scale |
| Elasticsearch / OpenSearch | Search engines with vector support | Mature BM25 plus vectors in one place | Resource-heavy; vector features vary by version |
All of them are good enough for typical RAG workloads. The decision is mostly about operations, scale and data governance.
Questions that decide the choice
1. How many vectors, really?
Estimate honestly. A company knowledge base with 20,000 documents chunked into 200,000 pieces at 1,024 dimensions is about 800 MB of float32 vectors — comfortably in one PostgreSQL instance. Ten million chunks is about 40 GB before index overhead, which changes the picture. Hundreds of millions to billions need a dedicated, distributed engine or aggressive quantization.
2. How important is filtering?
Multi-tenant SaaS, access-controlled documents and catalogue search depend on filters. Naive "search then filter" breaks when the filter is selective: you ask for the top 10, the ANN returns 10 neighbours, and 9 belong to other tenants. Engines differ in how they combine filtering with the index. Qdrant and Weaviate integrate filters into graph traversal; pgvector supports iterative index scans in recent versions and partial indexes or partitioning per tenant. Test with your real filter selectivity.
3. Do you need hybrid search?
For business documents — product codes, legal article numbers, names, error messages — pure vector search misses exact matches. Hybrid search with BM25 plus vectors, fused with reciprocal rank fusion (Cormack et al., 2009), is the default we recommend; see RAG for business. Weaviate, Qdrant (sparse vectors), Elasticsearch and OpenSearch support it natively; in PostgreSQL, combine pgvector with full-text search in one SQL query.
4. Who operates it?
A dedicated engine is another stateful system: backups, upgrades, monitoring, security patches, capacity planning. If your team already runs PostgreSQL well — with tested backups and disaster recovery — adding pgvector costs almost nothing operationally. A managed service removes operations but adds a vendor, a bill that grows with data, and data-residency questions; see privacy for LLM apps.
5. How does data change?
Frequently updated data (prices, stock, tickets) needs efficient upserts and deletes. HNSW handles inserts well; deletes leave tombstones that need periodic cleanup or reindexing in some engines. Bulk re-embedding when you change the embedding model is a planned migration in any system — keep raw text so you can re-embed.
Hybrid search in PostgreSQL
A compact example combining pgvector and full-text search with reciprocal rank fusion:
-- Schema
CREATE EXTENSION IF NOT EXISTS vector;
CREATE TABLE chunks (
id bigserial PRIMARY KEY,
tenant_id int NOT NULL,
content text NOT NULL,
tsv tsvector GENERATED ALWAYS AS (to_tsvector('simple', content)) STORED,
embedding vector(1024) NOT NULL
);
CREATE INDEX ON chunks USING hnsw (embedding vector_cosine_ops) WITH (m = 16, ef_construction = 64);
CREATE INDEX ON chunks USING gin (tsv);
CREATE INDEX ON chunks (tenant_id);
-- Query: $1 = query embedding, $2 = query text, $3 = tenant
WITH vec AS (
SELECT id, row_number() OVER (ORDER BY embedding <=> $1) AS r
FROM chunks WHERE tenant_id = $3
ORDER BY embedding <=> $1 LIMIT 50
), kw AS (
SELECT id, row_number() OVER (ORDER BY ts_rank(tsv, q) DESC) AS r
FROM chunks, plainto_tsquery('simple', $2) q
WHERE tenant_id = $3 AND tsv @@ q
ORDER BY ts_rank(tsv, q) DESC LIMIT 50
)
SELECT c.id, c.content,
COALESCE(1.0 / (60 + vec.r), 0) + COALESCE(1.0 / (60 + kw.r), 0) AS score
FROM chunks c
LEFT JOIN vec ON vec.id = c.id
LEFT JOIN kw ON kw.id = c.id
WHERE vec.id IS NOT NULL OR kw.id IS NOT NULL
ORDER BY score DESC
LIMIT 10;
The 'simple' text configuration avoids English stemming for Ukrainian text; for better Ukrainian full-text search consider a dedicated dictionary or a search engine. Tune hnsw.ef_search per query for the recall you need.
Benchmarking for your workload
Vendor benchmarks are optimised for the vendor. Run your own, small but realistic:
- Take 100–500 real queries with known relevant documents (your eval set).
- Load your real data with your real embedding model and metadata.
- Measure recall@k against exact search, p95 latency with filters, memory and index build time.
- Test updates — insert and delete 10% of data and re-measure.
- Estimate monthly cost including replicas and backups.
Most teams find that differences in end-to-end RAG quality come from chunking, embeddings, hybrid search and reranking — not from the database. See embeddings and chunking.
Our default recommendations
- Up to ~5–10 million vectors, PostgreSQL already in the stack: pgvector with HNSW, hybrid search in SQL. Simplest operations, transactional consistency with your business data.
- Heavy filtering, multi-tenant at scale, or a need for advanced quantization: Qdrant or Weaviate, self-hosted on Kubernetes or as a managed cloud.
- Hundreds of millions of vectors or GPU-accelerated search: Milvus/Zilliz.
- No ops capacity and no data-residency constraints: Pinecone or another managed service.
Whatever you choose, keep the raw text and metadata as the source of truth and treat the vector index as a rebuildable derivative.
FAQ
Is pgvector "production ready"?
Yes, for the scale most business applications need. Plan memory for HNSW indexes, tune maintenance_work_mem for builds and monitor vacuum.
Should we store vectors next to business data? Often yes: it simplifies access control and joins, and keeps deletes consistent (GDPR erasure must remove vectors too).
How many dimensions should we use? It is set by the embedding model. Models with Matryoshka-style training allow truncating dimensions to save memory with small quality loss.
Can we switch databases later? Yes, if your retrieval layer is behind a thin interface and you keep raw text to re-index.
Sources
- Malkov, Y., Yashunin, D. (2016). Efficient and robust approximate nearest neighbor search using Hierarchical Navigable Small World graphs.
- pgvector on GitHub.
- Qdrant documentation, Weaviate, Milvus documentation, Pinecone documentation.
- ANN-Benchmarks.
- Cormack, G., Clarke, C., Buettcher, S. (2009). Reciprocal Rank Fusion outperforms Condorcet and individual Rank Learning Methods.
- Douze et al. (2024). The Faiss library.