RAG Technical Documentation Assistant

Self Project | Python 3.12, PostgreSQL 16, pgvector (HNSW), GIN Full-Text Search, Cross-Encoder Reranking, Gemini 1.5 Flash

A production-quality Retrieval-Augmented Generation (RAG) system built from the ground up for high-accuracy querying over PostgreSQL 16 official documentation and Linux manual pages. Designed to expose and optimize every microarchitectural layer of a real-world enterprise RAG pipeline without hiding mechanics behind opaque wrapper frameworks like LangChain or LlamaIndex.

🎯 Core Engineering Philosophy:
Most commercial RAG tutorials rely on high-level libraries that abstract away vector distance calculations, database connection pooling, full-text tokenizers, and prompt assembly. This project implements every stage explicitly: recursive token-aware chunking with tiktoken, dual-indexed PostgreSQL storage (HNSW vector index + GIN inverted index), rank-based Reciprocal Rank Fusion (RRF), cross-encoder neural reranking, and citation-grounded LLM synthesis with automatic hallucination guardrails.

📚 Speeches & Documentation Modules

Module Type Target Duration Core Focus & Architectural Concepts
intro.html Elevator Pitch 1–2 mins Executive summary covering the dual-index PostgreSQL design, hybrid RRF fusion, cross-encoder reranking, and citation grounding with strategic follow-up interview hooks.
detailed.html Architecture Speech 4–5 mins Complete end-to-end technical walkthrough across 5 stages: ingestion/chunking, PostgreSQL/pgvector indexing, hybrid RRF search, cross-encoder reranking, and LLM serving/evaluation.
complete_flow.html System Deep Dive Reference Guide Exhaustive, start-to-finish pipeline walkthrough covering all 9 phases in full detail: DocBook/man scraping, recursive token chunking, 384-dim BGE embeddings, SQL schemas & HNSW/GIN indexes, vector/FTS/RRF algorithms, cross-encoder inference, prompt formatting, FastAPI/CLI, and the 41-question benchmark suite.
interview_qa.html Interview Prep Deep Dive Q&A Comprehensive Master Q&A Guide covering 10 technical categories (A through J): architectural decisions, recursive chunking & token boundaries, BGE embeddings, pgvector HNSW & GIN full-text indexing, Reciprocal Rank Fusion (k=60), cross-encoder reranking, LLM citation contracts, asyncpg pooling, schema constraints, and quantitative IR evaluation.

🏗️ System Architecture & Dataflow

Documentation Corpus (PostgreSQL 16 Docs + Linux Man Pages)
         │
         ▼
┌──────────────────────────────────────────────────────────────────────────┐
│                           Ingestion Pipeline                             │
│  Downloader (aiohttp / groff) → Extractor (DocBook/Regex) →              │
│  Chunker (Recursive 512 tokens / 64 overlap) →                           │
│  Encoder (BAAI/bge-small-en-v1.5) → SHA-256 Idempotent Upsert            │
└──────────────────────────────────────────────────────────────────────────┘
         │
         ▼
┌──────────────────────────────────────────────────────────────────────────┐
│                   Unified PostgreSQL 16 Database Storage                 │
│  documents (id, name, source_url, doc_type)                              │
│  chunks (content, content_tsv TSVECTOR, embedding VECTOR(384))            │
│  ├── HNSW Index: cosine distance (<=>), m=16, ef_construction=64         │
│  └── GIN Index: full-text search over content_tsv                        │
└──────────────────────────────────────────────────────────────────────────┘
         │
    ┌────┴──────────────────────────────┐
    ▼                                   ▼
[Channel A: Semantic Vector]       [Channel B: Lexical Full-Text]
pgvector HNSW Cosine Search        PostgreSQL websearch_to_tsquery
ORDER BY embedding <=> $1::vector  WHERE content_tsv @@ query
SET hnsw.ef_search = 40            ORDER BY ts_rank_cd(...) DESC
    │                                   │
    └─────────────────┬─────────────────┘
                      ▼
┌──────────────────────────────────────────────────────────────────────────┐
│                 Phase 3: Reciprocal Rank Fusion (RRF)                    │
│      RRF_Score(c) = Σ_retriever 1 / (60 + rank_in_retriever)             │
│      Merges Top-20 Vector + Top-20 FTS without score scale distortion    │
└──────────────────────────────────────────────────────────────────────────┘
                      │
                      ▼
┌──────────────────────────────────────────────────────────────────────────┐
│                 Phase 4: Cross-Encoder Neural Reranking                  │
│      cross-encoder/ms-marco-MiniLM-L-6-v2 (~22M parameters, 80 MB)       │
│      Joint (query, passage) cross-attention over Top-20 candidates       │
│      Selects Top-5 context chunks sorted by rerank_score DESC            │
└──────────────────────────────────────────────────────────────────────────┘
                      │
                      ▼
┌──────────────────────────────────────────────────────────────────────────┐
│                 Phase 5: Citation-Grounded LLM Generation                │
│      Numbered context blocks [1]...[5] with source URLs                  │
│      System prompt enforcing strict [Source N] inline citations          │
│      Zero external hallucinations; "insufficient context" fallback       │
│      Google GenAI (Gemini 1.5 Flash / Gemma fallback) or OpenAI          │
└──────────────────────────────────────────────────────────────────────────┘
                      │
         ┌────────────┴────────────┐
         ▼                         ▼
FastAPI REST API (/query)    Interactive Terminal CLI (cli.py)
Asynchronous Connection Pool REPL & One-Shot with ANSI Colors

⚙️ Technical Decisions & Design Rationale

Component Engineering Choice Technical Justification
Dense Embeddings BAAI/bge-small-en-v1.5 (384 dimensions) Top-tier retrieval quality on the MTEB / BEIR benchmark at compact size; 512-token context window; requires asymmetric retrieval instruction prefix for queries: "Represent this sentence for searching relevant passages: ".
Vector Index HNSW (Hierarchical Navigable Small World) Sub-linear $\mathcal{O}(\log N)$ nearest-neighbour search graph. Configured with $m=16$ bidirectional connections, $ef_{construction}=64$. Query candidate search parameter $ef_{search}$ is dynamically tunable at runtime via SET hnsw.ef_search = 40 without rebuilding the index.
Lexical Search PostgreSQL FTS (tsvector + GIN) Uses websearch_to_tsquery to parse natural-language user queries with implicit AND, quoted phrase matching, and minus exclusions. Ranked with ts_rank_cd (cover-density ranking) which weights proximity of search terms.
Score Fusion Reciprocal Rank Fusion (RRF, $k=60$) Cosine similarity ($[0, 1]$) and ts_rank_cd ($[0, \infty)$) have vastly different score distributions. RRF uses ranks instead of raw scores ($\frac{1}{60 + \text{rank}}$), preventing either retriever from dominating the candidate list.
Reranking Model cross-encoder/ms-marco-MiniLM-L-6-v2 Compact 22M-parameter model (~80 MB) trained on MS MARCO passage ranking. Performs full joint cross-attention between query and passage tokens. Evaluated strictly on the top-20 retrieved candidates to maintain low latency on CPU.
Storage Engine Unified PostgreSQL 16 (Single Store) Eliminates external vector database complexity (no separate Milvus/Pinecone cluster). Stores relational document metadata, chunks, tsvector text search tokens, and VECTOR(384) embeddings in a single ACID-compliant database with zero synchronization lag.
LLM Client Gemini 1.5 Flash / Google GenAI SDK Sub-second TTFT (Time To First Token), generous free-tier throughput, strong system instruction following, and automatic model fallback cascade (Flash $\to$ Gemma $\to$ Pro).
Backend & CLI FastAPI + asyncpg + Rich Terminal REPL High-concurrency async pipeline using connection pooling with custom register_vector codec, paired with an interactive terminal REPL (cli.py) featuring slash commands (/mode, /topk, /stats).

📊 Evaluation & Benchmark Summary

The system was evaluated against a custom 41-question technical dataset spanning PostgreSQL MVCC, query optimization, indexing internals, WAL logging, and Linux administrative tools:

Retrieval Mode Recall@5 Recall@10 Hit@5 Hit@10 MRR Latency (p50)
Vector Search (HNSW) 0.2927 0.3049 0.3171 0.3171 0.2988 ~18 ms
Hybrid Search (Vector + FTS RRF) 0.2927 0.3049 0.3171 0.3171 0.2988 ~26 ms
Hybrid + Cross-Encoder Rerank 0.2927 0.3171 0.3171 0.3415 0.2791 ~112 ms
💡 Key Empirical Insights: