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)

Muhammad Talha — Sun, 08 Nov 2026 18:53:24 +0000

Over the last few weeks, while working on a FastAPI and PostgreSQL RAG pipeline, I kept running into the same three headaches.

First, standard character and sliding-window chunkers were silently chopping our Markdown and PDF financial tables right in the middle of a row. Even worse, sliding windows were creating chunks with over 80% token overlap, which stuffed duplicate text into the prompt and made the LLM hallucinate numbers.

Second, trying to use cloud LLM-as-a-judge tools to test retrieval changes on every commit was way too slow and burned through OpenAI API credits fast, even though 90% of our bad answers were just simple retrieval misses.

Third, when we wired up DIY hybrid search in PostgreSQL combining pgvector and full-text search (ts_rank_cd), our test scores kept changing randomly between runs.

So I built a small local benchmark on PostgreSQL 17 and pgvector 0.8.7 using 30 realistic enterprise chunks (SEC Form 10-K revenue tables, cloud DB pricing matrices, database fault runbooks, and hardware SKUs) and 15 golden test queries. Instead of calling an LLM judge, I used Polars and NumPy to calculate Recall@K, MRR, and NDCG locally for $0.

Here is how the three retrieval methods actually performed head-to-head:

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
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
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

What actually broke during testing:

On exact error codes and SKUs (like ERR-PG-9042-WAL or SKU-NVME-9940-REV-B), pure vector search struggled at Rank 1 (73.3%). Because all chunks from the same database runbook talk about similar topics, their dense embeddings cluster tightly together in cosine space. Pairing a generated TSVECTOR GIN index with Reciprocal Rank Fusion (k=60) pushed the exact SKU and error code matches straight to Rank 1 every time.

On the flip side, when queries used semantic paraphrases without sharing exact keywords, pure Postgres full-text search dropped to 0.0% Recall@5, while Hybrid RRF held onto 100.0% Recall@5 thanks to the HNSW vector candidates.

Before even running the search benchmark, I also wrote a pre-index health scanner to check the chunks themselves. Out of 30 chunks, it 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.

Three SQL fixes that made plain-Postgres hybrid search stable:

Keep raw_cosine_distance, raw_fts_rank, and rrf_score in separate SQL columns so you can actually see whether the vector index or the keyword index caused a bad ranking.
Always add a deterministic tiebreaker at the end of your query: ORDER BY rrf_score DESC, id ASC. Without id ASC, whenever two chunks tie on ts_rank_cd, Postgres returns them in arbitrary heap order, which makes your CI tests pass on one run and fail on the next.
If you use WHERE metadata filters with an HNSW index in pgvector 0.8+, turn on SET LOCAL hnsw.iterative_scan = 'relaxed_order' inside your transaction so strict filters do not starve your LIMIT K results.

Try the benchmark script or the visual workbench locally:

I packaged the standalone Python benchmark script, the 30-chunk and 15-query JSON dataset, and the CLI chunk health checker (pgrag-eval-lite) as a free ($0) download so anyone can test their own chunks locally.

I also built a 4-page localhost FastAPI and HTMX visual workbench (pgRAG-Inspector Pro, running on 127.0.0.1:8787 with zero Node.js build steps) that lets you visually compare Naive vs Sliding Window vs Docling HybridChunker splits side-by-side and exports the async SQLAlchemy 2.0 RRF repository, Alembic migration, and Pytest CI gate in one click.

You can grab the free CLI and benchmark dataset ($0 tier) or check out the Pro workbench here:
https://mtalha5.gumroad.com/l/pgrag-inspector

If you are running into weird PDF table splits or pgvector filtering issues in your own project, drop a comment below and I am happy to share the exact SQLAlchemy 2.0 query we used.

DEV Community: Muhammad Talha

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)