Build a Retrieval Pipeline That Survives Real Production Data

TL;DR — A retrieval pipeline is not "embed text and query a vector DB"; it is an ingestion system with irreversible decisions around chunk boundaries, versioning, and metadata. The single biggest practical win is to store enough metadata to re-embed, filter, dedupe, explain results, and roll back bad ingests without re-scraping the world. Reading time: ~7 min

What it is and where it sits

A retrieval pipeline turns source documents into searchable units for semantic search or RAG. In practice it sits between your raw content systems and your application’s answer-generation path.

It usually talks to:

• source systems: filesystems, S3-compatible object storage, Git repos, CMS exports, ticket systems, docs sites
• preprocessing jobs: parsers, OCR, HTML cleaners, language detectors
• embedding workers: batch jobs calling an embedding model
• storage: a relational DB for metadata plus a vector-capable index
• serving path: your API that takes a user query, retrieves candidates, optionally reranks, then passes context to an LLM or returns search results

What it replaces: naive keyword search over whole documents, or the common first attempt where you dump full pages into a vector store and hope cosine similarity saves you.

Where it lives in request/data flow:

[Source docs] -> [Parse/Clean] -> [Chunk] -> [Embed] -> [Index + Metadata DB]
                                                        |
User query -> [Embed query] -> [Vector search + filters] -> [Optional rerank] -> [LLM/app]

A useful mental model: retrieval is two systems, not one.

• Offline ingestion path: expensive, batch-oriented, versioned
• Online query path: latency-sensitive, filter-heavy, observable

If you only design the online path, you will regret the first schema change, model upgrade, or duplicate-ingest incident.

How it actually works

Walk one realistic example: internal engineering docs stored as Markdown in Git, served to a support bot.

Step 1: Parse and normalize documents

Suppose you ingest docs/runbooks/postgres-failover.md. Before chunking, normalize content into a canonical record:

• doc_id: stable logical ID, not filename if filenames can move
• source_uri: git://repo/path?ref=<commit> or HTTPS permalink
• content_text: plain text after Markdown stripping or section-aware parsing
• title, headings, language
• source_last_modified, ingested_at
• content_hash: hash of normalized text, not raw bytes

Why normalized text hash matters: if whitespace or frontmatter changes but visible content does not, you can skip re-embedding.

Step 2: Chunk with structure, not fixed size only

Bad pipeline: split every 800 tokens with 100-token overlap.

Better pipeline: first split on document structure (#, ##, HTML headings, paragraph blocks, code fences, table boundaries), then apply token limits inside each section.

For our runbook, a section like "Failover procedure" becomes 3 chunks because it is long. Each chunk stores:

• chunk_id: deterministic, e.g. sha256(doc_id + section_path + chunk_index + chunk_text)
• doc_id
• section_path: Runbooks > Postgres > Failover procedure
• chunk_index
• token_count
• char_start, char_end in normalized document
• text

Why deterministic IDs matter: reruns become idempotent. If your worker crashes halfway through, you can upsert instead of duplicating.

Step 3: Embed chunks and record embedding provenance

Each chunk goes through an embedding model. Store the vector, but also store:

• embedding_model: exact model identifier
• embedding_dim
• embedding_created_at
• preprocessing_version
• chunking_version
• embedding_input_hash

If you do not store these, you cannot answer basic production questions like "why did recall drop after Tuesday’s deploy?"

Step 4: Index vectors and metadata separately but joinable

A common pattern is:

• Postgres table for chunk metadata and source-of-truth text
• vector index in Postgres with pgvector, or a dedicated vector engine

At query time, you embed the user query, ask for top-k nearest neighbors, then apply metadata filters like:

• tenant_id = 'acme'
• doc_type IN ('runbook', 'kb')
• language = 'en'
• is_deleted = false
• published_at <= now()

This is where teams discover they forgot to store tenant_id, published_at, or access_scope.

Step 5: Retrieve, optionally rerank, then build context

User asks: "How do I fail over Postgres without losing writes?"

Online flow:

1. Normalize query text
2. Embed query with the same embedding family used for the index, or a compatible query encoder
3. Search top 50 by vector similarity with metadata filters
4. Optionally rerank top 20 with a cross-encoder or LLM reranker
5. Return top 5 chunks with citations and surrounding context

The result you want is not just chunk text. Return enough for debugging and UX:

• score
• doc_id, chunk_id
• title and section path
• source URI
• content snippet
• ingest/version info

When things go wrong, this is the difference between "the bot is bad" and "chunking v3 split numbered procedures across chunk boundaries."

When to use it (and when not to)

You probably do not need this if:

• your app only searches a few hundred records
• exact-match search solves most queries
• you cannot invest in ingestion observability, backfills, and schema evolution
• your source data is so noisy that parsing quality is the real bottleneck

Trade-offs

Benefit and cost come together here.

• Better semantic recall
  • Cost: more moving parts than BM25; embedding jobs, vector index tuning, and model/version management
• Better answer quality for RAG
  • Cost: chunking mistakes become product bugs; bad boundaries poison retrieval silently
• Metadata filtering and explainability
  • Cost: larger schema, more storage, more careful upsert/delete logic
• Incremental updates
  • Cost: you need stable IDs, content hashes, tombstones, and reprocessing workflows
• Model upgrades
  • Cost: dual-write or rolling re-embed migrations; mixed-dimension indexes are usually a hard stop
• Fast top-k retrieval
  • Cost: ANN indexes trade exactness for speed; tuning lists, probes, HNSW params, and recall becomes an operational task
• Vendor flexibility
  • Cost: if you rely on provider-specific hybrid search or filter semantics, migration gets painful

The hidden cost most teams miss is operational burden. Retrieval quality degrades gradually: parser changes, malformed docs, duplicate chunks, stale deletes, and mixed embedding versions do not always throw hard errors.

In practice

Example 1: Postgres schema with pgvector and metadata you will actually use

⚠️ Creating or rebuilding indexes on large tables can lock writes or spike IO depending on your migration strategy. Run schema changes in staging first and use concurrent index creation where supported.

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE documents (
  doc_id text PRIMARY KEY,
  tenant_id text NOT NULL,
  source_uri text NOT NULL,
  title text,
  doc_type text NOT NULL,
  language text,
  source_last_modified timestamptz,
  content_hash text NOT NULL,
  access_scope text NOT NULL DEFAULT 'internal',
  is_deleted boolean NOT NULL DEFAULT false,
  ingested_at timestamptz NOT NULL DEFAULT now(),
  parser_version text NOT NULL,
  chunking_version text NOT NULL
);

CREATE TABLE chunks (
  chunk_id text PRIMARY KEY,
  doc_id text NOT NULL REFERENCES documents(doc_id) ON DELETE CASCADE,
  chunk_index integer NOT NULL,
  section_path text,
  char_start integer NOT NULL,
  char_end integer NOT NULL,
  token_count integer NOT NULL,
  text_content text NOT NULL,
  embedding_model text NOT NULL,
  embedding_dim integer NOT NULL,
  embedding_input_hash text NOT NULL,
  embedding_created_at timestamptz NOT NULL DEFAULT now(),
  embedding vector(1536) NOT NULL
);

CREATE INDEX documents_tenant_type_deleted_idx
  ON documents (tenant_id, doc_type, is_deleted);

CREATE INDEX chunks_doc_id_idx ON chunks (doc_id);
CREATE INDEX chunks_embedding_ivfflat_idx
  ON chunks USING ivfflat (embedding vector_cosine_ops)
  WITH (lists = 100);

This gives you a source-of-truth metadata table and a chunk table with embedding provenance. Gotcha: vector(1536) hardcodes dimensionality; changing models later means a new column/table or a full migration.

Example 2: Python ingest job with deterministic IDs and upserts

import hashlib
import os
from datetime import datetime, timezone
import psycopg

PARSER_VERSION = "md-v2"
CHUNKING_VERSION = "section-then-token-v3"
EMBEDDING_MODEL = "text-embedding-1536"


def sha256(s: str) -> str:
    return hashlib.sha256(s.encode("utf-8")).hexdigest()


def chunk_sections(text: str, max_chars: int = 1800):
    sections = text.split("\n## ")
    out = []
    for sec in sections:
        sec = sec.strip()
        if not sec:
            continue
        for i in range(0, len(sec), max_chars):
            out.append(sec[i:i + max_chars])
    return out


def fake_embed(text: str) -> list[float]:
    return [0.0] * 1536


def ingest_doc(conn, tenant_id: str, source_uri: str, title: str, doc_type: str, text: str):
    doc_id = sha256(source_uri)
    content_hash = sha256(text)
    chunks = chunk_sections(text)

    with conn.cursor() as cur:
        cur.execute(
            """
            INSERT INTO documents
              (doc_id, tenant_id, source_uri, title, doc_type, content_hash, parser_version, chunking_version, ingested_at)
            VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s)
            ON CONFLICT (doc_id) DO UPDATE SET
              content_hash = EXCLUDED.content_hash,
              title = EXCLUDED.title,
              parser_version = EXCLUDED.parser_version,
              chunking_version = EXCLUDED.chunking_version,
              ingested_at = EXCLUDED.ingested_at,
              is_deleted = false
            """,
            (doc_id, tenant_id, source_uri, title, doc_type, content_hash, PARSER_VERSION, CHUNKING_VERSION, datetime.now(timezone.utc)),
        )

        for idx, chunk in enumerate(chunks):
            chunk_id = sha256(f"{doc_id}|{idx}|{chunk}")
            embedding = fake_embed(chunk)
            cur.execute(
                """
                INSERT INTO chunks
                  (chunk_id, doc_id, chunk_index, char_start, char_end, token_count, text_content, embedding_model, embedding_dim, embedding_input_hash, embedding)
                VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)
                ON CONFLICT (chunk_id) DO UPDATE SET
                  text_content = EXCLUDED.text_content,
                  embedding_model = EXCLUDED.embedding_model,
                  embedding_dim = EXCLUDED.embedding_dim,
                  embedding_input_hash = EXCLUDED.embedding_input_hash,
                  embedding = EXCLUDED.embedding
                """,
                (chunk_id, doc_id, idx, 0, len(chunk), len(chunk.split()), chunk, EMBEDDING_MODEL, 1536, sha256(chunk), embedding),
            )
    conn.commit()


if __name__ == "__main__":
    dsn = os.environ["DATABASE_URL"]
    with psycopg.connect(dsn) as conn:
        ingest_doc(conn, "acme", "git://docs/runbooks/postgres-failover.md?ref=abc123", "Postgres Failover", "runbook", open("postgres-failover.md").read())

This is the minimum shape of an idempotent ingest worker. Gotcha: the example does not delete stale chunks when chunk boundaries change; in production, compare current chunk IDs to existing ones and tombstone or delete the missing set.

Example 3: Query with metadata filters and diagnostics

SET ivfflat.probes = 10;

SELECT
  c.chunk_id,
  d.doc_id,
  d.title,
  d.source_uri,
  d.doc_type,
  c.section_path,
  1 - (c.embedding <=> $1::vector) AS score,
  left(c.text_content, 240) AS snippet,
  c.embedding_model,
  d.chunking_version
FROM chunks c
JOIN documents d ON d.doc_id = c.doc_id
WHERE d.tenant_id = 'acme'
  AND d.is_deleted = false
  AND d.access_scope IN ('internal', 'support')
  AND d.doc_type IN ('runbook', 'kb')
ORDER BY c.embedding <=> $1::vector
LIMIT 10;

This is the online retrieval path in one query. Gotcha: if recall looks suspiciously low, increase ivfflat.probes; low probe counts are a classic reason for "obviously relevant chunk missing" incidents.

Typical failure shapes worth recognizing:

ERROR:  different vector dimensions 1024 and 1536
LINE 12: ORDER BY c.embedding <=> $1::vector

That means query embeddings and indexed embeddings came from different models or preprocessing paths.

ERROR:  insert or update on table "chunks" violates foreign key constraint "chunks_doc_id_fkey"
DETAIL:  Key (doc_id)=(...) is not present in table "documents".

That usually means your worker writes chunks before the document upsert committed, or you split document/chunk writes across transactions.

Further reading

• PostgreSQL Documentation: pgvector extension docs and index access methods
• Sentence-BERT paper
• LangChain text splitters docs
• Information Retrieval by Manning, Raghavan, and Schütze
• The "Search" and "Caching" chapters of the MDN HTTP docs

This article was written by an AI system and published pending human review. Verify anything you intend to act on.