Overview: Comprehensive, production-grade answers to technical interview questions for the RAG Technical Documentation Assistant. Prepared in the author's own words with exact code mappings to
rag/files/, microarchitectural database reasoning, algorithmic proofs, and systems design trade-offs.
Technical manualsβsuch as PostgreSQL 16 documentation and Linux man pagesβare dense, highly specific, and unforgiving of slight errors. Standard commercial LLMs hallucinate non-existent CLI flags, confuse version-specific syntax (e.g. PG 14 vs PG 16 features), and cannot provide clickable citations back to the exact manual section. My project provides a production-grade RAG pipeline that retrieves authoritative documentation excerpts and forces the LLM to ground its response with verifiable [Source N] citations, returning an explicit fallback whenever documentation is insufficient.
A standard chatbot relies purely on its static parametric weights. It cannot be updated without retraining or fine-tuning, suffers from catastrophic forgetting, and frequently produces confident hallucinations on niche configuration flags. RAG decouples knowledge storage from reasoning: documentation is indexed dynamically in PostgreSQL, and the model acts strictly as a contextual synthesis engine over verified excerpts.
Technical documentation is authoritative, structured, and definitive. For systems engineering, getting an option name or default timeout wrong can cause production downtime. Documentation contains precise parameters, return codes, and operational edge cases, making it the highest-value ground truth for software engineers and SREs.
ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
β INGESTION PIPELINE β
β Postgres HTML / Linux Man Pages β BeautifulSoup & Groff Stripping β
β β Recursive Token Chunker (512 tokens / 64 overlap via tiktoken) β
β β BGE-Small Encoder (384-dim) β SHA-256 Idempotent Upsert β
βββββββββββββββββββββββββββββββββββββ¬βββββββββββββββββββββββββββββββββββββ
β
βΌ
ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
β POSTGRESQL 16 UNIFIED STORAGE β
β - documents table (metadata, doc_type, URLs) β
β - chunks table (content, content_hash, content_tsv GIN, embedding) β
β - HNSW Index (m=16, ef_construction=64, cosine distance <=>) β
β - GIN Index (to_tsvector English lexemes) β
βββββββββββββββββββββββββββββββββββββ¬βββββββββββββββββββββββββββββββββββββ
β
ββββββββββββββββββ΄βββββββββββββββββ
βΌ βΌ
[Vector Search: HNSW] [FTS: websearch_to_tsquery]
Top-20 Semantic Candidates Top-20 Lexical Candidates
β β
ββββββββββββββββββ¬βββββββββββββββββ
βΌ
ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
β RECIPROCAL RANK FUSION (RRF, k=60) β
β Merges Top-20 + Top-20 by rank: Ξ£ 1 / (60 + rank_in_retriever) β
βββββββββββββββββββββββββββββββββββββ¬βββββββββββββββββββββββββββββββββββββ
β Top-20 Merged Candidates
βΌ
ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
β CROSS-ENCODER NEURAL RERANKER (MiniLM-L-6-v2) β
β Joint Query-Passage Cross-Attention over Top-20 Candidates β
β Selects Top-5 highest scoring context chunks β
βββββββββββββββββββββββββββββββββββββ¬βββββββββββββββββββββββββββββββββββββ
β Top-5 High-Precision Chunks
βΌ
ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
β CITATION-GROUNDED GENERATION (Gemini 1.5 Flash) β
β Numbered context blocks [1]...[5] + Strict System Prompt Contract β
β Output answer with [Source N] β Regex parser extracts citations β
βββββββββββββββββββββββββββββββββββββ¬βββββββββββββββββββββββββββββββββββββ
β
ββββββββββββββββββ΄βββββββββββββββββ
βΌ βΌ
FastAPI REST API (/query) Terminal REPL CLI (cli.py)
downloader.py): Asynchronously scrapes PostgreSQL 16 DocBook HTML via aiohttp or captures terminal manual pages using subprocess.run(["man", "-P", "cat", cmd]).extractor.py): Strips navigation boilerplate, headers, footers, and table-of-contents; cleans groff backspace bold codes (.\x08); extracts clean section titles and text.chunker.py): Recursively splits text at paragraph, sentence, and word boundaries into 512-token pieces with 64-token overlap using tiktoken.encoder.py): Transforms text into 384-dimensional float32 normalized embeddings using BAAI/bge-small-en-v1.5, applying asymmetric query prefixes.connection.py, schema.sql): Manages the PostgreSQL database connection pool, applies relational constraints, and maintains HNSW vector and GIN full-text indexes.The ingestion pipeline creates a record in documents. The document is extracted and split into Chunk objects. For each chunk, a SHA-256 hash is generated. Chunks are embedded in batches of 32 and inserted into chunks via an executemany query using ON CONFLICT (content_hash) DO NOTHING. PostgreSQL's generated column automatically compiles the content_tsv full-text vector, and both HNSW and GIN indexes are updated incrementally.
The query string is passed to encoder.encode_query(), which prepends the BGE query prefix and outputs a 384-dim vector. The system queries PostgreSQL: HNSW searches vector cosine distance for 20 candidates, while websearch_to_tsquery queries the GIN index for 20 lexical candidates. Reciprocal Rank Fusion combines the lists into 20 candidates. The cross-encoder reranks them, selecting the top 5. The prompt builder wraps these 5 chunks into numbered context blocks. The LLM generates the answer with [Source N] citations, which are parsed via regex and returned with metadata.
Using a separate vector database (like Pinecone, Milvus, or Qdrant) alongside a primary database creates a distributed systems problem: dual-writes can fail, vector indexes suffer synchronization lag, and relational metadata must be kept in sync via complex ETL pipelines. PostgreSQL with pgvector provides a single-store ACID architecture: vector embeddings, relational metadata, and full-text search live in the same row. Relational filtering and vector ordering happen in a single query plan.
The hardest problem was score calibration in hybrid retrieval. Cosine similarity produces values bounded in $[0, 1]$ (usually clustered between $0.68$ and $0.85$), whereas PostgreSQL's ts_rank_cd produces unbounded scores $[0, \infty)$ based on keyword repetition and document length. Linear score combinations ($\alpha S_{vec} + \beta S_{fts}$) failed because single outlier keyword matches completely overwhelmed vector semantic scores. I solved this by implementing Reciprocal Rank Fusion ($k=60$), which operates purely on ordinal ranks, rendering score fusion completely scale-invariant.
execute_rag() from Input to Output:cli.py (and mirrored in api/routes/query.py), execute_rag(query_text, mode, top_k, pool) traces as follows:
q_emb = await loop.run_in_executor(None, encoder.encode_query, query_text) runs the BGE bi-encoder on a worker thread.pool. In hybrid mode, it awaits hybrid_search(query_text, q_emb, conn), which executes vector_search (HNSW cosine distance) and fts_search (GIN tsvector) sequentially on that connection, then calls reciprocal_rank_fusion() to merge them into 20 candidates.hybrid_rerank mode, it offloads reranker.rerank(query_text, chunks, top_k) to a thread. The cross-encoder predicts logits for all 20 pairs, sorts them, and slices the top-5 chunks.generate_answer(query_text, chunks). This builds numbered excerpts, calls Gemini 1.5 Flash via the GenAI SDK with system grounding instructions, parses [Source N] citations via regex, and returns the final answer with source metadata and latency.Three reasons: First, cost and latency: feeding a 200-page manual per query burns millions of tokens and takes dozens of seconds. Second, context window limits: while models have larger windows, technical libraries easily exceed 10 million tokens. Third, the "Lost in the Middle" phenomenon: attention mechanisms degrade when attempting to locate a single needle in a massive haystack. Chunking isolates the exact 3β5 relevant paragraphs, maximizing answer precision.
Chunking is the process of partitioning large continuous documents into smaller, coherent text passages. It is necessary because embedding models (like BGE) have fixed maximum context lengths (512 tokens), and vector similarity is much sharper when comparing focused passages rather than entire books.
Implemented in ingestion/chunker.py: It uses a hierarchy of boundary separators. It first splits by double newlines (paragraphs). If a piece exceeds 512 tokens, it splits by punctuation followed by whitespace (sentences). If a single sentence still exceeds 512 tokens (e.g. huge code blocks), it falls back to word splitting. It then greedily merges pieces until reaching 512 tokens, carrying over a 64-token overlap from the tail of the previous chunk.
Arbitrary character slicing (e.g. slicing every 1000 characters) cuts words, sentences, and code syntax in half. A command flag like -rf might be split into -r and f across two chunks, ruining keyword search and confusing the LLM. Splitting at natural syntactic boundaries preserves semantic integrity.
Character counts do not map predictably to token counts. In technical documentation, code snippets, JSON objects, and paths have a much higher token-to-character ratio than plain English prose. Counting tokens directly with tiktoken (cl100k_base) guarantees that chunks never exceed the embedding model's 512-token limit, preventing silent vector truncation.
Section names provide critical domain context to the LLM (e.g., distinguishing the kill system call in Section 2 from the kill command-line utility in Section 1). URLs allow the frontend to display verifiable, clickable links for every citation.
A content hash is a SHA-256 digest computed over the raw text of each chunk. In PostgreSQL, content_hash has a UNIQUE constraint. During ingestion, ON CONFLICT (content_hash) DO NOTHING skips duplicate chunks automatically. This makes the ingestion pipeline fully idempotent and resumable if interrupted.
Chunks whose text has changed generate new SHA-256 hashes and are inserted as new rows. However, old chunks that no longer exist in the upstream manual would remain orphaned unless pruned. To maintain freshness, our pipeline supports document-level replacement by deleting existing chunks via document_id before re-ingesting an updated manual.
Metadata misalignment causes catastrophic context corruption:
DROP TABLE is incorrectly labeled with the section SELECT Statements, the LLM prompt will present destructive behavior under a read-only heading, leading the model to hallucinate false constraints.[Source 2], but the UI presents a link pointing to the wrong documentation page. In pipeline.py, we guarantee correctness by strictly pairing each chunk with its extracted section metadata tuple throughout the entire batching pipeline.An embedding is a mapping of discrete human language (words, sentences, or code) into a continuous high-dimensional vector space (e.g. $\mathbb{R}^{384}$). Semantically similar concepts are located close to each other in this geometric space.
The text is tokenized and passed through multiple transformer encoder layers. Self-attention allows each token to aggregate contextual meaning from all other tokens in the sentence. The final token hidden states are mean-pooled into a single fixed-length 384-dimensional vector, which is normalized to unit length.
Training a state-of-the-art embedding model requires hundreds of millions of diverse text pairs, contrastive loss formulations with large batch sizes, and massive GPU compute clusters. Pretrained models (like BGE) have already learned rich semantic representations across billions of documents.
BAAI/bge-small-en-v1.5 is a 33M-parameter model that punches well above its weight, leading the MTEB retrieval leaderboard in its class. Choosing 384 dimensions over 768 or 1536 dimensions reduces HNSW vector index memory consumption in PostgreSQL by 50% to 75%, allowing the entire index to remain cache-resident in RAM while executing vector distance math twice as fast on CPU.
pgvector enforces fixed dimensionality per column. Setting VECTOR(384) ensures that every inserted vector matches the exact output dimensionality of bge-small-en-v1.5, preventing dimension mismatch errors and enabling hardware SIMD acceleration.
It means their angular separation in 384-dimensional space is small (their dot product / cosine similarity is close to 1.0). In linguistic terms, they describe equivalent concepts or frequently appear in identical semantic contexts.
Cosine distance measures the angle between vectors rather than their magnitude. In text retrieval, document length can cause vector magnitudes to vary. Cosine distance evaluates directional alignment, making similarity invariant to verbosity.
When vectors have unit length ($\|v\|_2 = 1.0$), cosine similarity simplifies directly to the inner dot product: $$\text{Cosine Similarity}(u, v) = \frac{u \cdot v}{\|u\|_2 \|v\|_2} = u \cdot v$$ $$\text{Cosine Distance} = 1 - (u \cdot v)$$ This avoids expensive square root and division operations during vector distance calculations inside PostgreSQL.
The BGE model family uses instruction tuning for asymmetric retrieval. Questions are short and exploratory, while documentation passages are descriptive. Prepending "Represent this sentence for searching relevant passages: " conditions the transformer to project user questions into the vector subspace of potential answers.
Keyword search requires exact token matches. Semantic search matches conceptual intent.
Example: A user searches for βdatabase becomes unresponsive under heavy loadβ.
max_connections and lock timeouts, even if the word "unresponsive" never appears in the text.pgvector is an open-source extension for PostgreSQL that introduces a native VECTOR data type, distance calculation operators (<=> for cosine, <-> for L2, <#> for inner product), and vector index access methods (HNSW and IVFFlat).
It provides transactional integrity (ACID), zero data synchronization lag, unified backups, and the ability to combine relational metadata filters, GIN full-text searches, and vector similarity in a single query execution plan.
Hierarchical Navigable Small World index. It organizes high-dimensional vectors into a multi-layered graph. The top layers contain sparse, long-range connections for fast coarse routing, while the bottom layers contain dense connections for precise local search, providing $\mathcal{O}(\log N)$ approximate nearest-neighbor search complexity.
m = 16: Maximum number of bidirectional connections per node per layer. Higher values improve recall on complex datasets but increase index RAM footprint.ef_construction = 64: Size of the dynamic candidate priority list during index build time. Higher values construct a higher-quality graph at the expense of slower build time.A runtime PostgreSQL configuration parameter that sets the candidate list size during query traversal. Executing SET hnsw.ef_search = 40 before querying allows the developer to trade latency for recall dynamically on a per-query basis without rebuilding the index.
Exact k-NN performs a brute-force linear scan ($\mathcal{O}(N)$) across all vectors in the table, guaranteeing 100% recall but taking hundreds of milliseconds as datasets grow. ANN traverses an index graph ($\mathcal{O}(\log N)$), evaluating only a fraction of vectors to return in ~15 ms with 98%+ recall.
PostgreSQL performs a Sequential Scan (Seq Scan), evaluating cosine distance across every row in the table. On small tables (<500 rows) it is fast, but on production datasets it saturates CPU and disk I/O, causing queries to stall.
ef_search.A sorted list of normalized, stemmed lexemes (root words with stop words removed) along with their positional offsets within the document, optimized for full-text searching.
A parsed query token representing lexemes combined with boolean operators (& AND, | OR, ! NOT, <-> phrase distance) used to match against a tsvector.
It parses natural language queries into valid tsquery expressions following web search syntax: space-separated words become AND clauses, "quoted strings" become phrase searches, and -word becomes a NOT filter, without throwing syntax errors on unescaped symbols.
The text search match operator in PostgreSQL. It returns TRUE if a tsvector matches a tsquery (e.g. content_tsv @@ query).
Cover-density ranking. It scores how frequently query terms appear in the document and gives extra weight to terms that appear close to each other in the text, providing superior relevance for multi-word technical queries.
Generalized Inverted Index. A GIN index maps every distinct lexeme to an array of table rows that contain it. It accelerates full-text queries from an $\mathcal{O}(N)$ table scan to an $\mathcal{O}(1)$ dictionary lookup.
content_tsv is the physical table column storing the pre-computed tsvector data for each chunk. The GIN index is the separate inverted index data structure stored on disk that indexes those lexemes to accelerate @@ lookups. The column holds the representation; the index accelerates search across that representation.
Vector search and full-text search have inverted failure modes. Vector search captures semantic concepts but misses exact keyword strings, syntax flags (e.g. -rf), and specific configuration keys. Full-text search guarantees exact lexical matching but fails on paraphrases and conceptual questions. Combining them eliminates blind spots.
READ COMMITTED vs REPEATABLE READ) and MVCC snapshots. Keyword search finds nothing if the specific phrase "prevent dirty reads" is absent.SIGKILL vs SIGTERM.Using Reciprocal Rank Fusion (RRF) in retrieval/hybrid.py over the top-20 candidates from vector search and top-20 candidates from full-text search.
An algorithmic technique for merging multiple ranked lists by summing the reciprocal of their ranks: $$\operatorname{RRF}(d) = \sum_{r \in \text{retrievers}} \frac{1}{k + \operatorname{rank}_r(d)}$$
Cosine similarity is bounded in $[0, 1]$ with narrow variance ($0.65β0.85$), while ts_rank_cd is unbounded $[0, \infty)$ and sensitive to chunk length and keyword repetition. Combining raw scores ($\alpha S_{vec} + \beta S_{fts}$) allows outlier keyword scores to dominate. RRF operates purely on ordinal ranks, making score fusion scale-invariant.
The smoothing constant $k$ dampens the penalty of lower ranks. If $k=1$, rank 1 gives $1.0$ and rank 2 gives $0.5$ (a 50% drop). With $k=60$, rank 1 yields $\frac{1}{61} \approx 0.01639$ and rank 2 yields $\frac{1}{62} \approx 0.01613$. This ensures that chunks appearing consistently in both lists at rank 5 beat a chunk that appeared at rank 1 in only one list.
Its reciprocal ranks are summed: $\frac{1}{60 + \text{rank}_{vec}} + \frac{1}{60 + \text{rank}_{fts}}$. This compound boost propels mutually agreed chunks to the top of the combined candidate list.
In reciprocal_rank_fusion(), scores are accumulated into a dictionary keyed by chunk_id. When a chunk appears in the second list, its score is added to the existing key rather than creating a duplicate entry.
All unique chunks are included and sorted by their single reciprocal score. Chunks ranked #1 in either list receive $\frac{1}{61}$, followed by #2 at $\frac{1}{62}$, ensuring that strong discoveries from both search channels are preserved in the top-20 candidate pool.
Pure full-text search fails whenever users phrase questions conversationally without knowing exact terminology. Pure vector search fails on exact commands, flags, and configuration keys. Empirical benchmarks demonstrate that hybrid search provides the highest overall Recall@10 and Hit@10.
Reranking is a two-stage information retrieval pattern where a lightweight first-stage retriever surfaces a candidate pool (e.g. top-20), and a computationally intensive neural model re-evaluates and reorders those candidates to maximize precision for the top-5 slots.
Bi-encoders encode query and passage independently, compressing all text into a 384-dimensional dot product. A cross-encoder performs full token-to-token cross-attention between query and passage, identifying subtle negations and syntactic qualifiers that bi-encoders miss.
[CLS] Query [SEP] Passage [SEP]. All query tokens attend to all passage tokens across all layers. Drastically more accurate, but computationally expensive ($\mathcal{O}(N)$ forward passes per query).It is compact (~22M parameters, ~80 MB), highly optimized for CPU inference (~80 ms for 20 candidates), and fine-tuned on the MS MARCO passage ranking dataset, making it ideal for technical passage relevance.
The concatenated string is processed through 6 transformer layers. The embedding of the special [CLS] token is passed through a linear classification layer that outputs a single unbounded logit score representing relevance.
Vector similarity is a single 384-dimensional dot product requiring a few dozen CPU clock cycles. A cross-encoder executes millions of matrix multiplications and softmax operations across 6 transformer layers for each candidate.
Evaluating 50,000 chunks would require 50,000 transformer forward passes, taking over 3 minutes per query. In our two-stage architecture, the database indexes retrieve 20 candidates in 25 ms, and the cross-encoder evaluates only those 20 candidates in 80 ms.
No. Reranking can only reorder candidates that are already present in the candidate pool. If the initial hybrid retrieval stage fails to include the relevant chunk in the top-20, the reranker has no way to surface it. Reranking optimizes precision, not recall.
Prune the candidate pool from 20 to 10; quantize model weights to 8-bit integer (INT8) using ONNX Runtime; or use batching on a GPU worker.
An AI architecture that enhances LLM generation by retrieving relevant documents from an external datastore and injecting them into the prompt context, grounding the model's answer in authoritative factual data.
Fine-tuning alters the model's stylistic and linguistic behavior, not its reliable factual recall. Fine-tuned models still hallucinate, cannot cite source URLs, and require expensive retraining whenever documentation updates. RAG updates instantly as soon as documentation is re-indexed in PostgreSQL.
In generation/prompt.py: The prompt consists of a strict SYSTEM_PROMPT enforcing grounding rules, followed by a user message containing numbered context blocks:
[1] Document: <doc_name>
Section: <section_title>
URL: <source_url>
---
<chunk_content>
followed by a separator and the user query.
By using explicit labeled section headers (Documentation excerpts: vs Question:) and horizontal line dividers (ββββββββββββββββ).
The system prompt states: β1. Answer using ONLY the information present in the provided excerpts. 2. For every factual claim, add an inline citation: [Source N]. 3. If context is insufficient, state: 'The provided documentation does not contain enough information to answer this question.' Do not guess or use external knowledge.β
The model is prompted to insert inline citation tags like [Source 1] or [Source 1][Source 2] directly after each statement in its response.
In generation/llm.py, extract_citations(answer, chunks) scans the generated text with regex:
_CITATION_RE = re.compile(r"\[Source\s+(\d+)\]", re.IGNORECASE)
For each match $N$, it accesses chunks[N-1] and extracts document_name, section, and source_url.
extract_citations() checks bounds (if 0 <= idx < len(chunks):). Any phantom citation (e.g. [Source 9] when only 5 chunks were provided) is silently discarded.
Yes. extract_citations() is a syntactic parser, not a semantic verifier. If the LLM generates a hallucinated statement and appends [Source 1], the parser maps it to chunk 1. Independent validation requires an automated NLI (Natural Language Inference) entailment model or an LLM-as-judge audit.
Complex multi-hop reasoning can lead the model to infer causal relationships not stated in the text. Furthermore, if a retrieved chunk contains outdated documentation, the model will faithfully reproduce that outdated information.
If the user asks an irrelevant or out-of-scope question (e.g. "How do I cook pasta?"), the model must explicitly state that the documentation is insufficient rather than generating ungrounded advice.
Provider redundancy prevents outages. In generation/llm.py, we implement an automatic fallback cascade (gemini-flash-latest $\to$ gemma-4-26b-a4b-it $\to$ gemini-pro-latest), alongside support for OpenAI.
FastAPI provides asynchronous execution on Starlette ASGI, automatic Pydantic request/response serialization, built-in OpenAPI documentation, and native lifespan connection pool hooks.
Both call the identical pipeline: encode query vector $\to$ query PostgreSQL via hybrid_search $\to$ rerank via cross_encoder $\to$ call generate_answer $\to$ return structured JSON or terminal formatting.
It allows cooperative multitasking on Python's single-threaded event loop. While waiting for network I/O from PostgreSQL or the Gemini API, the thread yields control to handle other requests.
No. CPU-bound code (such as PyTorch transformer encoding or regex parsing) runs synchronously on the main thread and blocks the event loop unless offloaded to a thread pool via loop.run_in_executor().
Using asyncpg.create_pool() initialized during application startup in database/connection.py, registering the pgvector binary codec for zero-copy serialization.
Opening a PostgreSQL connection involves TCP handshakes, TLS negotiation, and authentication (~30 ms). A pool maintains pre-established connections, allowing requests to acquire an active connection in <1 ms.
Each request acquires a connection from the pool. With max_size=10, up to 10 queries execute concurrently. Additional requests wait asynchronously in a queue without failing.
Configure timeouts on asyncpg (command_timeout=60.0) and HTTP clients; implement exponential backoff retry loops on HTTP 429 rate limits; and use model fallbacks.
Granular phase latencies: query embedding time, database vector search time, database FTS time, RRF fusion time, cross-encoder rerank time, and LLM TTFT.
For LLMs: execute an automatic fallback cascade to alternate models. For embeddings: keep local model weights cached on disk to operate offline without external API dependencies.
Implement an in-memory Redis cache: hash the normalized query string and cache the resulting JSON response. For near-duplicate queries, use a semantic vector cache.
Containerize with Docker, run Uvicorn workers behind an Nginx reverse proxy, and expose GET /health and GET /stats endpoints for Kubernetes health probes and Prometheus scrapers.
hybrid_search:retrieval/hybrid.py, vector search and full-text search are awaited sequentially on the same connection:
vec_results = await vector_search(query_embedding, conn, top_k=vector_top_k)
fts_results = await fts_search(query, conn, top_k=fts_top_k)
If asked about concurrency by an interviewer: be completely honest! Explain that while the pipeline docstring mentions parallel execution, the actual implementation runs them sequentially on a single checked-out connection to avoid acquiring two connection pool slots for one query. To make them truly parallel, one would acquire two connections and run asyncio.gather().β
Foreign key relationship (REFERENCES documents (id) ON DELETE CASCADE). It establishes referential integrity, grouping chunks under their parent source document.
If a record in documents is deleted, PostgreSQL automatically deletes all corresponding child rows in chunks, preventing orphaned chunks.
Intentional denormalization. Storing document_name and source_url directly on chunks eliminates the need to execute an SQL JOIN documents on the hot query path during top-K retrieval.
UUIDs are globally unique, distributed-safe identifiers. They allow chunks to be generated across distributed workers without ID collisions or database sequence locking.
It automatically compiles and stores the tsvector lexemes upon insertion:
content_tsv TSVECTOR GENERATED ALWAYS AS (to_tsvector('english', content)) STORED
This keeps full-text search representations continuously synchronized with chunk content without requiring custom triggers.
PostgreSQL's GENERATED ALWAYS ... STORED mechanism immediately updates the content_tsv column and updates both the GIN index and HNSW index.
IF NOT EXISTS only checks if a table with that name exists. If the table already exists from an older commit with missing columns or different types, PostgreSQL skips execution entirely. Schema migrations require explicit ALTER TABLE statements.
Using ON CONFLICT (content_hash) DO NOTHING skips identical chunks. For updated documents, delete existing chunks by document_id and re-insert.
DELETE FROM documents WHERE id = <doc_id>;
The ON DELETE CASCADE constraint immediately purges all child chunks.
Embeddings from different models cannot be compared or mixed. You must:
1. Add a new column: ALTER TABLE chunks ADD COLUMN embedding_new VECTOR(new_dim);
2. Re-embed all chunks and populate embedding_new.
3. Build a new HNSW index on embedding_new.
4. Update application settings to query the new column, then drop the old column.
βI evaluated three retrieval configurations on a benchmark of 41 questions: vector-only search, hybrid search using Reciprocal Rank Fusion, and hybrid search with cross-encoder reranking.
Hybrid search did not show measurable improvement over vector-only retrieval in my current experiments. Adding cross-encoder reranking improved Recall@10 from 30.5% to 31.7% and Hit@10 from 31.7% to 34.1%. However, MRR decreased from 0.2988 to 0.2791, while retrieval latency increased from approximately 32 milliseconds to 112 milliseconds.
These results revealed a trade-off between retrieving more relevant chunks within the top ten results, ranking the first relevant result highly, and latency. My next step would be to investigate why hybrid retrieval provides no measurable gain and optimize the retrieval pipeline based on per-query results.β
Run python -m evaluation.run_eval --modes vector hybrid --skip-generation. The benchmark outputs a comparison table showing Recall@5, Recall@10, and Hit@K across both modes.
Compare hybrid vs hybrid_rerank. In our benchmarks, reranking improved Hit@10 from 31.71% to 34.15% and Recall@10 from 30.49% to 31.71%.
Using an LLM-as-judge prompt (implemented in metrics.py) that scores answers on a 1β5 scale based on factual correctness, citation accuracy, and hallucination absence against reference answers.
hybrid_search to execute vector search and FTS concurrently across two pooled connections using asyncio.gather().The LLM receives conflicting excerpts in its context. Without explicit conflict instructions, it will either mention both perspectives or favor the earlier excerpt. This can be improved by adding a prompt rule: "If sources disagree, state the conflict explicitly."
The system prompt's strict fallback rule triggers: "The provided documentation does not contain enough information to answer this question."
Apply a similarity score cutoff threshold (e.g. discard chunks with cross-encoder score < 0.0); enforce strict negative prompt guardrails; and run automated NLI verification.