When you need a vector database (and when you don't)

The question of when do you need a vector database comes up in almost every RAG project, usually after someone has already provisioned Pinecone or Qdrant. The honest answer: you need one when semantic similarity is the primary access pattern for unstructured data at a scale where brute-force search becomes painful. You don’t need one when your data is structured, your queries are exact-match, or your corpus fits in memory with room to spare. This guide walks through the decision criteria, the alternatives, and the migration path if you start simple and outgrow it.

Start with the access pattern

Vector databases solve one problem well: approximate nearest neighbor (ANN) search over high-dimensional embeddings. If your queries look like “find documents similar to this text” or “retrieve the top-k chunks relevant to this question,” a vector index is the right tool. If your queries look like “find all invoices over $10k from Q3” or “get the user’s last 5 orders,” you have a relational problem — use Postgres.

The confusion usually starts when teams conflate “we have embeddings” with “we need a vector database.” Embeddings are just vectors. You can store them in a BYTEA column, a FLOAT[] array, or a dedicated vector type in pgvector. The database only becomes necessary when the similarity search itself is the bottleneck.

-- pgvector: good enough for millions of vectors on modest hardware
CREATE EXTENSION vector;
CREATE TABLE chunks (
  id bigserial PRIMARY KEY,
  content text,
  embedding vector(1536)
);
CREATE INDEX ON chunks USING hnsw (embedding vector_cosine_ops);
-- Query
SELECT content FROM chunks
ORDER BY embedding <=> $1::vector
LIMIT 10;

This handles 1-5M vectors on a single node with sub-100ms p99 latency. The operational complexity is zero if you already run Postgres.

The scale thresholds that matter

These are rough boundaries. The real constraint is query throughput and latency SLA, not raw vector count. A 10M-vector index on a single node with 64GB RAM can serve 500 QPS if the working set fits in memory. The same index on a 16GB instance will thrash.

Pitfall: Provisioning a managed vector database for 50k vectors “because we might scale.” You’ve added a network hop, a new auth model, a separate backup strategy, and a vendor dependency for zero measurable benefit. Start with pgvector or SQLite-VSS. Migrate when the profiler says so.

When metadata filtering dominates

Most production RAG queries combine semantic search with structured filters: “find chunks about refund policy for enterprise tier updated after 2024-01-01.” This is where vector databases diverge.

pgvector supports pre-filtering (filter then ANN) and post-filtering (ANN then filter). Pre-filtering is exact but can eliminate the ANN index benefit if the filter selectivity is low. Post-filtering is fast but may return fewer than k results after filtering.

-- Pre-filter: uses btree index on tier, then scans filtered rows
SELECT content FROM chunks
WHERE tier = 'enterprise' AND updated_at > '2024-01-01'
ORDER BY embedding <=> $1
LIMIT 10;

-- Post-filter: ANN first, then filter in memory
SELECT content FROM chunks
WHERE embedding <=> $1 < 0.3
  AND tier = 'enterprise'
  AND updated_at > '2024-01-01'
LIMIT 10;

Dedicated vector databases (Qdrant, Weaviate, Pinecone) implement filtered HNSW — the graph traversal respects the filter predicate during search. This is genuinely harder to replicate in Postgres and becomes the decisive factor around 1M+ vectors with high-cardinality metadata filters.

If your filter cardinality is low (e.g., tenant_id with 50 values), pgvector’s partitioned tables work fine. If you filter on high-cardinality fields (user_id, session_id, arbitrary tags), a purpose-built vector database saves significant engineering time.

Hybrid search is not a vector database feature

BM25 + vector hybrid search is often cited as a reason to adopt a vector database. It’s not. You can run BM25 in Postgres with pg_trgm or paradedb, in SQLite with sqlite-fts5, or in OpenSearch/Elasticsearch. The vector database vendors added BM25 because their customers demanded it, not because it’s architecturally coupled to ANN.

-- Hybrid in Postgres: RRF (reciprocal rank fusion)
WITH vector_results AS (
  SELECT id, content, 1.0 / (rank + 60) AS score
  FROM (
    SELECT id, content, ROW_NUMBER() OVER (ORDER BY embedding <=> $1) AS rank
    FROM chunks
    LIMIT 50
  ) v
),
bm25_results AS (
  SELECT id, content, 1.0 / (rank + 60) AS score
  FROM (
    SELECT id, content, ROW_NUMBER() OVER (ORDER BY ts_rank_cd(to_tsvector('english', content), plainto_tsquery('english', $2)) DESC) AS rank
    FROM chunks
    LIMIT 50
  ) b
)
SELECT id, content, SUM(score) AS combined_score
FROM (
  SELECT * FROM vector_results
  UNION ALL
  SELECT * FROM bm25_results
) combined
GROUP BY id, content
ORDER BY combined_score DESC
LIMIT 10;

This works. It’s not pretty, but it runs on your existing database. Only migrate to a hybrid-native engine (OpenSearch, Vespa, or a vector DB with native BM25) when the SQL becomes a maintenance burden or the latency budget demands a single query plan.

Operational reality check

Before you add a vector database to your stack, answer these:

1. Who owns the index rebuild? Embedding model changes require re-embedding and re-indexing. A 10M-vector rebuild takes hours. Do you have a blue-green strategy?
2. What’s your backup/restore story? pg_dump works. Vector database snapshots vary by vendor — some are fast, some are “export to S3 and pray.”
3. How do you handle multi-tenancy? Row-level security in Postgres is battle-tested. Vector databases range from “native namespaces” to “filter on tenant_id and hope.”
4. Can you tolerate eventual consistency? Most distributed vector databases are eventually consistent by default. If your RAG pipeline requires read-your-writes, you need a leader-based system or synchronous replication.

Common pitfall: Treating the vector database as a black box. ANN indexes have tunable parameters (ef_construction, ef_search, M for HNSW) that trade recall for latency. The defaults are conservative. You will need to benchmark and tune for your workload.

# Benchmark script template
import time
import numpy as np
from sentence_transformers import SentenceTransformer

model = SentenceTransformer('BAAI/bge-small-en-v1.5')
queries = [model.encode(q) for q in test_queries]

def benchmark_search(index, queries, k=10, ef_search=64):
    index.set_ef(ef_search)  # HNSW parameter
    latencies = []
    recalls = []
    for q, gt in zip(queries, ground_truth):
        start = time.perf_counter()
        results = index.search(q, k)
        latencies.append((time.perf_counter() - start) * 1000)
        recalls.append(len(set(results) & set(gt[:k])) / k)
    return np.percentile(latencies, 99), np.mean(recalls)

for ef in [32, 64, 128, 256]:
    p99, recall = benchmark_search(index, queries, ef_search=ef)
    print(f"ef_search={ef}: p99={p99:.1f}ms, recall@10={recall:.3f}")

Run this against your actual data and queries. The “right” ef_search is the lowest value that meets your recall target.

Migration path: start simple, extract later

The lowest-risk architecture: embed in your application, store in Postgres with pgvector, search with SQL. When (if) you hit the limits above, extract the vector search into a dedicated service.

┌─────────────┐     ┌─────────────┐     ┌──────────────────┐
│  Application │────▶│   Postgres  │────▶│  Vector Service  │
│  (embeddings)│     │  (pgvector) │     │  (Qdrant/Milvus) │
└─────────────┘     └─────────────┘     └──────────────────┘
       │                   │                      │
       │            < 5M vectors           > 5M vectors
       │            simple filters         complex filters
       │            low QPS                high QPS
       ▼                   ▼                      ▼
   Phase 1             Phase 2               Phase 3

The extraction is straightforward because the interface is stable: embed(query) → vector → search(vector, filters, k) → results. Your application code barely changes.

What about n4n.ai?

If you’re routing LLM calls through a gateway like n4n.ai, the embedding model choice becomes a configuration parameter, not a code change. Swap text-embedding-3-small for bge-large-en-v1.5 by updating the model directive in the request header. The vector database doesn’t care — it only sees 1536-dim or 1024-dim floats. This decoupling matters when you inevitably re-embed your corpus.

Decision checklist

Use this at your next architecture review:

• Primary query pattern is semantic similarity? → Vector index needed
• Corpus > 5M vectors or QPS > 500? → Dedicated vector database
• High-cardinality metadata filters on every query? → Dedicated vector database
• Hybrid BM25 + vector required and single-digit ms latency? → OpenSearch/Vespa or vector DB with native hybrid
• Team has zero Postgres ops experience but strong Kubernetes? → Managed vector database (Pinecone, Zilliz Cloud)
• None of the above? → pgvector or SQLite-VSS

The honest answer

When do you need a vector database? When the cost of not having one — in latency, recall, or engineering hours spent fighting pgvector’s limits — exceeds the cost of operating one. That threshold is higher than most vendors admit. Start with Postgres. Measure. Migrate when the data forces you to.