Vector databases

The three layers

1. Embeddings — fixed-size vectors (e.g. 1536 floats from text-embedding-3-small) representing chunk meaning. Same model at index and query time; never mix models in one collection.
2. Metadata — JSON or typed columns attached to each vector: source, document_type, user_id, updated_at, ACL flags. Used for pre-filtering before or during ANN.
3. ANN search — graph or cluster structures (HNSW, IVF) that return top-K nearest neighbors in milliseconds at 100K–100M scale. Exact brute force is reserved for eval baselines and tiny corpora.

Vector DBs are not general OLTP databases—though pgvector blurs the line by living inside PostgreSQL. They optimize for read-heavy similarity queries with optional metadata predicates, not complex joins or transactional updates across many tables. Your application still owns chunk text, embedding jobs, and reranking; the store answers “nearest vectors under these filters.”

Vector DB vs relational DB vs search engine

End-to-end RAG retrieval path

1. User question → embed query (cached).
2. Apply metadata filters (tenant, date, document_type).
3. ANN top-K (e.g. 20–50) from vector store.
4. Optional rerank (cross-encoder) → top 5 for LLM context.

The vector store sits between your chunking pipeline and retrieval strategies (reranking, query expansion, refusal thresholds). Embeddings fundamentals live in Track 1 · Embeddings & semantic search.

At-a-glance matrix

Operational and cost dimensions

Decision heuristics

• Already on Postgres, <5M vectors: start with pgvector + HNSW—one backup pipeline, SQL filters, no new vendor.
• Need hybrid BM25+dense without glue code: Weaviate, Qdrant sparse, OpenSearch, or Redis Stack.
• Zero ops, fast launch: Pinecone serverless or Qdrant Cloud; accept vendor cost until unit economics clear.
• Billion vectors, ML platform team: Milvus / Zilliz with DiskANN.
• Sub-10ms on hot cache: Redis Vector for session-scoped retrieval; Postgres/pgvector for durable corpus.
• Laptop / CI / tutorial: Chroma embedded—migrate before multi-tenant prod.

Flat (exact / brute force)

No auxiliary structure—compare the query to every vector (or every row passing metadata pre-filter). Recall: 100%. Cost: O(n × d) per query. Use for golden-set eval (“what is true top-10?”), corpora under ~10K vectors, and CI regression tests that compare ANN vs exact rankings.

HNSW (Hierarchical Navigable Small World)

Multi-layer proximity graph: greedy beam search from entry points, refine on lower layers. Default for pgvector, Qdrant, Weaviate, Redis HNSW, OpenSearch nmslib/faiss HNSW.

• Build: moderate; no separate training phase (unlike IVF).
• Query params: ef_search (pgvector hnsw.ef_search) — higher → better recall, slower queries.
• Insert: online but slower than IVF; heavy bulk load may defer index creation.
• Memory: high—stores neighbor lists per vector.

IVF (Inverted File / IVFFlat)

k-means partitions the vector space into lists (cells). Each vector assigned to one cell; query probes probes nearest centroids only. pgvector ivfflat, Milvus IVF, Faiss IVF—common at very large scale with memory pressure.

• Build: requires training pass on representative sample—skewed samples hurt recall.
• Query params: probes — higher → better recall, more cells scanned.
• Insert: assign to nearest centroid—fast bulk ingest.
• Memory: lower than HNSW at same n; pairs with PQ compression at billion scale.

Recall / memory / latency trade-offs

Measuring recall correctly

1. Build labeled set: query text → relevant chunk IDs (50–200 questions from support logs).
2. Run exact flat search → ground-truth top-10 IDs.
3. Run ANN with candidate params → compute recall@K = |ANN ∩ truth| / K.
4. Plot recall vs p99 latency; pick smallest ef_search / probes that clears 95% recall@10.

# Run after bulk COPY into chunks — not row-by-row during ingest
HNSW_DDL = """
CREATE INDEX CONCURRENTLY idx_chunks_embedding_hnsw
  ON document_chunks
  USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);
"""
# Session tuning per query (optional)
SET_DDL = "SET hnsw.ef_search = 100;"  # default 40; raise for recall

// Flyway / Liquibase migration snippet
/*
CREATE INDEX CONCURRENTLY idx_chunks_embedding_hnsw
  ON document_chunks
  USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);
*/
@Transactional(readOnly = true)
public void tuneHnsw(JdbcTemplate jdbc) {
  jdbc.execute("SET hnsw.ef_search = 100");
}

Common metadata fields in RAG

Pre-filter vs post-filter

• Pre-filter (preferred): ANN runs only on IDs matching tenant_id = X AND document_type = 'policy'. Qdrant payload indexes, Pinecone metadata filters, pgvector SQL WHERE before ORDER BY distance.
• Post-filter: ANN returns top-100 globally, then drop rows failing filter—dangerous when filter is selective (you may get <K results). Always over-fetch (top 50→filter→5) or use pre-filter.

Example filter expressions

# Qdrant — payload index on tenant_id recommended
from qdrant_client.models import Filter, FieldCondition, MatchValue, Range

flt = Filter(must=[
    FieldCondition(key="tenant_id", match=MatchValue(value="org_abc")),
    FieldCondition(key="document_type", match=MatchValue(value="policy")),
    FieldCondition(key="updated_at", range=Range(gte="2024-01-01T00:00:00Z")),
])
hits = client.search(
    collection_name="kb",
    query_vector=query_emb,
    query_filter=flt,
    limit=20,
)

# Pinecone — metadata dict filter
# index.query(vector=q, top_k=20, filter={"tenant_id": "org_abc", "document_type": "policy"})

// pgvector + Spring JDBC — pre-filter in SQL
String sql = """
  SELECT id, content,
         1 - (embedding <=> ?::vector) AS score
  FROM document_chunks
  WHERE tenant_id = ?
    AND document_type = ?
    AND updated_at >= ?::timestamptz
  ORDER BY embedding <=> ?::vector
  LIMIT ?
  """;
// B-tree on (tenant_id, document_type, updated_at) keeps filter selective

Filter selectivity and recall

If only 0.1% of vectors pass the filter, HNSW may not traverse enough candidates—some engines build per-tenant partitions or namespace indexes. Symptoms: correct chunk exists but never appears in top-K. Fix: dedicated collection/namespace per large tenant, raise ef_search, or partition index by tenant.

Why neither alone is enough

Reciprocal Rank Fusion (RRF)

For each document d, sum contributions from each ranked list (dense list, BM25 list):

RRF(d) = Σ 1 / (k + rank_i(d))

where rank_i(d) is the 1-based rank of d in list i (missing docs contribute 0), and k is a constant—typically 60 (Cormack et al.; widely used in Elasticsearch, OpenSearch, Weaviate). Higher RRF score → better fused rank. No need to normalize cosine [0,1] with BM25 unbounded scores.

Hybrid implementation sketch

1. Retrieve top-N_dense (e.g. 50) from vector store.
2. Retrieve top-N_lex (e.g. 50) from BM25 (OpenSearch, Postgres tsvector, or store-native).
3. Compute RRF scores in application code or engine-native hybrid query.
4. Take top-K fused (e.g. 20) → optional cross-encoder rerank → 5 for LLM.

from collections import defaultdict

K_RRF = 60

def rrf_fuse(*ranked_lists: list[str], k: int = K_RRF) -> list[tuple[str, float]]:
    scores: dict[str, float] = defaultdict(float)
    for lst in ranked_lists:
        for rank, doc_id in enumerate(lst, start=1):
            scores[doc_id] += 1.0 / (k + rank)
    return sorted(scores.items(), key=lambda x: -x[1])

dense_ids = ["c1", "c4", "c2", "c9"]   # from vector ANN
bm25_ids  = ["c4", "c1", "c7", "c2"]  # from OpenSearch
fused = rrf_fuse(dense_ids, bm25_ids)
top_ids = [doc_id for doc_id, _ in fused[:20]]

public static final int K_RRF = 60;

public List<String> rrfFuse(List<List<String>> rankedLists, int topK) {
  Map<String, Double> scores = new HashMap<>();
  for (List<String> list : rankedLists) {
    for (int rank = 0; rank < list.size(); rank++) {
      String id = list.get(rank);
      scores.merge(id, 1.0 / (K_RRF + rank + 1), Double::sum);
    }
  }
  return scores.entrySet().stream()
      .sorted(Map.Entry.<String, Double>comparingByValue().reversed())
      .limit(topK)
      .map(Map.Entry::getKey)
      .toList();
}

When hybrid wins (and when it does not)

• Win: technical docs, support KBs, legal corpora with defined terms + natural language questions.
• Win: eval shows dense-only misses exact-match queries; BM25-only misses paraphrase.
• Skip (initially): tiny homogeneous corpus (<5K chunks) where dense alone hits 95% recall@5.
• Skip: pure conversational memory where lexical overlap is irrelevant—dense + recency may suffice.

Native hybrid (Weaviate, OpenSearch hybrid query, Qdrant sparse+dense) reduces round trips; app-level RRF stays portable across pgvector + external BM25.

Namespace / collection per tenant

• Each tenant gets Pinecone namespace, Qdrant collection, or Chroma collection.
• Pros: strong isolation, independent index tuning, easy delete-tenant (drop namespace), no cross-tenant ANN leakage.
• Cons: operational explosion at 10K+ tenants; cold namespaces; harder cross-tenant analytics; more index memory overhead.

Shared index + metadata filter

• Single collection; every row has tenant_id; every query includes mandatory filter.
• Pros: one index to manage, efficient for many small tenants, simpler backup.
• Cons: filter selectivity issues for huge tenants; blast radius if filter bug; noisy neighbor on shared RAM.

Hybrid tiering (common at scale)

Tenant lifecycle

1. Provision — create namespace or verify filter path; seed empty index.
2. Ingest — embed jobs tag every vector with tenant_id from auth context—not from client body.
3. Query — middleware injects tenant from JWT; integration tests assert cross-tenant negative cases.
4. Offboard — delete namespace or DELETE WHERE tenant_id = ?; verify object storage purge.

def search_kb(query: str, ctx: RequestContext) -> list[ChunkHit]:
    tenant_id = ctx.jwt["org_id"]  # never from request body
    embedding = embed(query)
    return vector_store.search(
        vector=embedding,
        filter={"tenant_id": tenant_id},
        namespace=tenant_id if ctx.tier == "enterprise" else None,
        top_k=20,
    )

public List<ChunkHit> searchKb(String query, AuthContext ctx) {
  String tenantId = ctx.orgId(); // from verified JWT
  float[] embedding = embedder.embed(query);
  return vectorStore.search(SearchRequest.builder()
      .vector(embedding)
      .filter(Map.of("tenant_id", tenantId))
      .namespace(ctx.isEnterprise() ? tenantId : null)
      .topK(20)
      .build());
}

Schema

SCHEMA_SQL = """
CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE document_chunks (
  id            BIGSERIAL PRIMARY KEY,
  chunk_id      TEXT NOT NULL,
  tenant_id     TEXT NOT NULL,
  source        TEXT NOT NULL,
  document_type TEXT NOT NULL,
  user_id       TEXT,
  content       TEXT NOT NULL,
  content_hash  TEXT NOT NULL,
  embedding     vector(1536) NOT NULL,
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (tenant_id, chunk_id)
);

CREATE INDEX idx_chunks_tenant_type ON document_chunks (tenant_id, document_type);
CREATE INDEX idx_chunks_updated ON document_chunks (updated_at DESC);
"""

/*
CREATE EXTENSION IF NOT EXISTS vector;
CREATE TABLE document_chunks (
  id BIGSERIAL PRIMARY KEY,
  chunk_id TEXT NOT NULL,
  tenant_id TEXT NOT NULL,
  source TEXT NOT NULL,
  document_type TEXT NOT NULL,
  user_id TEXT,
  content TEXT NOT NULL,
  content_hash TEXT NOT NULL,
  embedding vector(1536) NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (tenant_id, chunk_id)
);
CREATE INDEX idx_chunks_tenant_type ON document_chunks (tenant_id, document_type);
*/

Upsert (idempotent on tenant + chunk_id)

import hashlib
import psycopg
from openai import OpenAI

client = OpenAI()
MODEL = "text-embedding-3-small"

def content_hash(text: str) -> str:
    return hashlib.sha256(text.encode()).hexdigest()

def embed(text: str) -> list[float]:
    return client.embeddings.create(model=MODEL, input=text).data[0].embedding

def upsert_chunk(conn, row: dict) -> None:
    h = content_hash(row["content"])
    if row.get("content_hash") == h:
        return  # skip unchanged — no API call
    vec = embed(row["content"])
    conn.execute(
        """
        INSERT INTO document_chunks
          (chunk_id, tenant_id, source, document_type, user_id,
           content, content_hash, embedding, updated_at)
        VALUES (%(chunk_id)s, %(tenant_id)s, %(source)s, %(document_type)s,
                %(user_id)s, %(content)s, %(hash)s, %(emb)s, now())
        ON CONFLICT (tenant_id, chunk_id) DO UPDATE SET
          content = EXCLUDED.content,
          content_hash = EXCLUDED.content_hash,
          embedding = EXCLUDED.embedding,
          source = EXCLUDED.source,
          document_type = EXCLUDED.document_type,
          updated_at = now()
        WHERE document_chunks.content_hash IS DISTINCT FROM EXCLUDED.content_hash
        """,
        {**row, "hash": h, "emb": vec},
    )

# Batch ingest: upsert many, then CREATE INDEX CONCURRENTLY once

@Repository
public class ChunkRepository {
  private final JdbcTemplate jdbc;
  private final EmbeddingModel embedder;

  public void upsertChunk(ChunkRow row) {
    String hash = sha256(row.content());
    Optional<String> existing = jdbc.query(
        "SELECT content_hash FROM document_chunks WHERE tenant_id=? AND chunk_id=?",
        rs -> rs.next() ? Optional.of(rs.getString(1)) : Optional.empty(),
        row.tenantId(), row.chunkId());
    if (existing.isPresent() && existing.get().equals(hash)) return;

    float[] vec = embedder.embed(row.content());
    String vecLiteral = toPgVector(vec);
    jdbc.update("""
        INSERT INTO document_chunks
          (chunk_id, tenant_id, source, document_type, user_id,
           content, content_hash, embedding, updated_at)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?::vector, now())
        ON CONFLICT (tenant_id, chunk_id) DO UPDATE SET
          content = EXCLUDED.content,
          content_hash = EXCLUDED.content_hash,
          embedding = EXCLUDED.embedding,
          updated_at = now()
        WHERE document_chunks.content_hash IS DISTINCT FROM EXCLUDED.content_hash
        """,
        row.chunkId(), row.tenantId(), row.source(), row.documentType(),
        row.userId(), row.content(), hash, vecLiteral);
  }
}

Filtered similarity search

def search(
    conn,
    query: str,
    tenant_id: str,
    document_type: str | None = None,
    since: str | None = None,
    k: int = 10,
) -> list[dict]:
    q_vec = embed(query)
    sql = """
      SELECT chunk_id, content, source,
             1 - (embedding <=> %(q)s::vector) AS score
      FROM document_chunks
      WHERE tenant_id = %(tenant)s
    """
    params: dict = {"q": q_vec, "tenant": tenant_id, "k": k}
    if document_type:
        sql += " AND document_type = %(dtype)s"
        params["dtype"] = document_type
    if since:
        sql += " AND updated_at >= %(since)s::timestamptz"
        params["since"] = since
    sql += " ORDER BY embedding <=> %(q)s::vector LIMIT %(k)s"
    return conn.execute(sql, params).fetchall()

public List<ChunkHit> search(
    String query, String tenantId, String documentType, Instant since, int k) {
  float[] qVec = embedder.embed(query);
  String vec = toPgVector(qVec);
  StringBuilder sql = new StringBuilder("""
      SELECT chunk_id, content, source,
             1 - (embedding <=> ?::vector) AS score
      FROM document_chunks
      WHERE tenant_id = ?
      """);
  List<Object> args = new ArrayList<>(List.of(vec, tenantId));
  if (documentType != null) {
    sql.append(" AND document_type = ?");
    args.add(documentType);
  }
  if (since != null) {
    sql.append(" AND updated_at >= ?");
    args.add(Timestamp.from(since));
  }
  sql.append(" ORDER BY embedding <=> ?::vector LIMIT ?");
  args.add(vec);
  args.add(k);
  return jdbc.query(sql.toString(), chunkMapper, args.toArray());
}

Index rebuild strategy

1. Model change — new collection/table suffix (kb_v4); embed full corpus; build HNSW; alias swap; retire old index after soak.
2. Bulk re-ingest — disable HNSW during load; COPY or batch INSERT; VACUUM ANALYZE; CREATE INDEX CONCURRENTLY.
3. Incremental — upsert changed chunks only (content_hash gate); periodic index maintenance (pgvector reindex if supported; Qdrant optimize).
4. Blue/green — dual-write to staging index; validate recall@K; flip read traffic; delete green after 24h.

Backup and disaster recovery

• pgvector: standard Postgres PITR + nightly pg_dump; test restore quarterly.
• Managed (Pinecone/Qdrant): enable vendor snapshots; export vectors + metadata to S3 Parquet for vendor exit.
• Rebuild path: source docs in object storage + chunk manifest + embedding model version → full rebuild without vector backup (slow but sufficient).
• RPO/RTO: vectors are regenerable if corpus text exists—prioritize document backup over vector snapshot if storage cost bites.

Monitoring dashboard

Production readiness checklist

• Single embedding model per active index; migration runbook documented
• HNSW (or IVF) tuned with measured recall@K—not defaults only
• Mandatory tenant filter from auth context on every query
• Content-hash skip on upsert to avoid pointless re-embeds
• Index rebuild procedure tested in staging (timing documented)
• Vectors or rebuild inputs backed up; restore drill completed
• Hybrid / RRF evaluated against dense-only baseline in eval suite
• Runbook for “recall dropped after deploy”—check model version, index params, filter regression

Incident playbooks (short)

Symptom: all scores low, wrong answers. Check embedding model pin; verify query vectors normalized; compare one query brute-force vs ANN in SQL.

Symptom: p99 latency spike. Check HNSW RAM pressure, connection pool exhaustion, missing metadata index causing seq scan + sort.

Symptom: tenant sees empty results. Verify tenant_id filter, ingest lag, offboarded namespace—not retrieval quality.