Menu

Why outer ORDER BY isn't enough for deterministic hybrid RRF search in PostgreSQL 17 (pgvector + ts_rank_cd)
📰

Why outer ORDER BY isn't enough for deterministic hybrid RRF search in PostgreSQL 17 (pgvector + ts_rank_cd)

DEV Community: postgres·Muhammad Talha·5 days ago
#t5qtiX5e
#devtopostgresql#devto#dev#order#row_number
Reading 0:00
15s threshold

In my previous benchmark testing pure pgvector cosine search ( 73.3% Recall@1), native Postgres full-text search ts_rank_cd ( 66.7% Recall@1), and Hybrid Reciprocal Rank Fusion ( 100.0% Recall@1 in 1.99 ms ) across 30 enterprise RAG chunks, we used a deterministic tie-breaker ( ORDER BY rrf_score DESC, id ASC ) to stop CI evaluation scores from flapping.

Download the Free Lite CLI + 30-Chunk Benchmark Dataset ($0 tier) or pgRAG-Inspector Pro Studio:

https://mtalha5.gumroad.com/l/pgrag-inspector

Shortly after sharing the benchmark on r/Rag , a senior Postgres engineer ( DistributionKey7062 ) pointed out a subtle PostgreSQL window ordering trap in my original RRF query that anyone running hybrid search in production should check for.

The Bug: Window Ordering vs.…

Continue reading — create a free account

Join HashtagPLUS to read full articles, follow hashtags, vote, and join the conversation.