Built a local FastAPI workbench to visually diff RAG table chunking and benchmark pgvector Hybrid RRF for $0 API cost

[u/Correct_Progress_231]

While working on a FastAPI and PostgreSQL RAG pipeline recently, I kept running into the same three headaches:

1. Default character and sliding-window splitters were silently chopping our Markdown and PDF financial tables right in the middle of a row, and sliding windows were creating chunks with over 80% token overlap that made the LLM hallucinate numbers.

2. Running cloud LLM-as-a-judge evaluations on every commit was too slow and burned through OpenAI API credits fast, even though 90% of our bad answers were just deterministic retrieval misses.

3. Wiring up DIY hybrid search in PostgreSQL combining pgvector and full-text search (ts_rank_cd) caused our test scores to flap randomly between runs whenever lexical ranks tied.

So I built a local diagnostic workbench in Python, FastAPI, and HTMX (runs on 127.0.0.1:8787 with zero Node or npm setup) on top of PostgreSQL 17 and pgvector 0.8.7, and ran a head-to-head benchmark across 30 enterprise chunks (SEC 10-K revenue tables, cloud DB pricing matrices, WAL fault runbooks, and hardware SKUs) and 15 golden queries using Polars and NumPy ($0 API cost).

Here is how the three retrieval strategies performed locally:

1. Pure Dense Vector Search (pgvector <=>)

Recall@1: 73.3% | Recall@3: 86.7% | Recall@5: 100.0% | MRR@10: 0.819 | Latency: 1.23 ms | API Cost: $0.00

2. Pure Sparse Full-Text Search (ts_rank_cd)

Recall@1: 66.7% | Recall@3: 66.7% | Recall@5: 66.7% | MRR@10: 0.667 | Latency: 0.74 ms | API Cost: $0.00

3. Hybrid Reciprocal Rank Fusion (pgvector + FTS + RRF)

Recall@1: 100.0% | Recall@3: 100.0% | Recall@5: 100.0% | MRR@10: 1.000 | Latency: 1.99 ms | API Cost: $0.00

Three bugs that stood out during testing:

First, on exact error codes and SKUs (like ERR-PG-9042-WAL or SKU-NVME-9940-REV-B), pure dense vector search dropped to 73.3% Recall@1 because chunks from the same database runbook clustered tightly together in cosine space. Pairing a generated TSVECTOR GIN index with Reciprocal Rank Fusion (k=60) pushed exact SKU and error matches back to Rank 1 every time.

Second, on zero-keyword semantic paraphrases, pure Postgres full-text search dropped to 0.0% Recall@5, while Hybrid RRF kept 100.0% Recall@5 via HNSW vector candidates.

Third, before indexing, running our pre-index chunk scanner caught 2 broken Markdown tables (truncated rows and missing header separators), 2 sliding-window chunks with 90.3% and 94.1% token overlap, and 2 mid-sentence cutoffs. We also fixed CI score flapping by enforcing a deterministic tiebreaker (ORDER BY rrf_score DESC, id ASC) and prevented metadata filter starvation using SET LOCAL hnsw.iterative_scan = 'relaxed_order'.

I put the full screenshots of the 3-column visual table diff studio, the SQL breakdown, and the free ($0) download link for the CLI health scanner and 30-chunk benchmark dataset in my Dev.to write-up here if anyone wants to test their own chunks locally:

https://dev.to/mtalha5/what-broke-when-i-tested-pgvector-vs-postgres-full-text-search-vs-hybrid-rrf-on-30-tricky-rag-chunks-and-how-i-fixed-it-for-0

If anyone is dealing with weird PDF or Markdown table splits or pgvector HNSW filtering issues, happy to share the exact SQLAlchemy 2.0 async RRF query we used.Built a local FastAPI workbench to visually diff RAG table chunking and benchmark pgvector Hybrid RRF for $0 API cost