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

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. Output Ordering in PostgreSQL

In my original query, I placed ORDER BY rrf_score DESC, id ASC only at the very end of the outer RRF projection.

Here is why that alone isn't enough:

1. PostgreSQL strictly distinguishes a window function's internal ordering (ROW_NUMBER() OVER (...)) from the final output ordering.
2. When multiple chunks share an identical lexical score in ts_rank_cd (which happens frequently on short SKU or keyword queries), the inner sparse_branch CTE has to cut off at LIMIT 20 and assign integer ranks via ROW_NUMBER().
3. Without an explicit tie-breaker inside both inner CTE branches, Postgres can assign different ROW_NUMBER() ranks to tied rows before RRF fusion even happens.

The Fixed Deterministic Hybrid RRF Query (PostgreSQL 17 + pgvector 0.8+)

To make plain-Postgres Hybrid RRF 100% deterministic across CI runs, enforce id ASC in three places: inside both ROW_NUMBER() OVER (...) windows, on both inner branch ORDER BY ... LIMIT cutoffs, and on the final outer FULL OUTER JOIN projection:

sql
SET LOCAL hnsw.iterative_scan = 'relaxed_order';

WITH dense_branch AS (
    SELECT id,
           (embedding <=> :q_vec) AS raw_cosine_distance,
           ROW_NUMBER() OVER (ORDER BY (embedding <=> :q_vec) ASC, id ASC) AS dense_rank
    FROM rag_chunks
    WHERE workspace_tier = :ws
    ORDER BY (embedding <=> :q_vec) ASC, id ASC
    LIMIT 20
),
sparse_branch AS (
    SELECT id,
           ts_rank_cd(fts_vector, plainto_tsquery('english', :q_text)) AS raw_fts_rank,
           ROW_NUMBER() OVER (
               ORDER BY ts_rank_cd(fts_vector, plainto_tsquery('english', :q_text)) DESC, id ASC
           ) AS sparse_rank
    FROM rag_chunks
    WHERE workspace_tier = :ws
      AND fts_vector @@ plainto_tsquery('english', :q_text)
    ORDER BY raw_fts_rank DESC, id ASC
    LIMIT 20
)
SELECT COALESCE(d.id, s.id) AS id,
       d.raw_cosine_distance,
       s.raw_fts_rank,
       (
           COALESCE(1.0 / (60 + d.dense_rank), 0.0) +
           COALESCE(1.0 / (60 + s.sparse_rank), 0.0)
       ) AS rrf_score
FROM dense_branch d
FULL OUTER JOIN sparse_branch s ON d.id = s.id
ORDER BY rrf_score DESC, id ASC
LIMIT 5;