Paginating hybrid search on Postgres, and why the obvious fix is worse

If you have built hybrid search on Postgres, your query probably looks close to this. One CTE ranks by vector distance, one ranks by ts_rank_cd, and Reciprocal Rank Fusion combines the two rankings.

with vector_candidates as (
  select id, row_number() over (order by embedding <=> $1) as rank
  from documents order by embedding <=> $1 limit 50
),
text_candidates as (
  select id, row_number() over (order by ts_rank_cd(fts, query) desc) as rank
  from documents, websearch_to_tsquery('english', $2) query
  where fts @@ query
  order by ts_rank_cd(fts, query) desc limit 50
)
select coalesce(v.id, t.id) as id,
       coalesce(1.0 / (60 + v.rank), 0.0) + coalesce(1.0 / (60 + t.rank), 0.0) as score
from vector_candidates v
full outer join text_candidates t on v.id = t.id
order by score desc
limit 10;

It works fine until someone asks for page two.

Pages just stop

Add offset 50 and you get an empty result. No error, nothing in the logs, just zero rows, which looks exactly like reaching the end of the results.

The limit 50 inside each CTE is the cause. Those two limits are the entire universe the fusion can see, so the outer limit 10 offset 50 is slicing a list that is at most 50 rows long after the join collapses duplicates. Page six is empty regardless of how many rows matched.

I found this on a 500 row table where 490 rows matched the query. Pages one through five were fine.

The obvious fix is worse

So you size the pool from the offset:

limit greatest(50, $offset + $page_size)

Page one pulls 60 candidates, page five pulls 100, page eight pulls 130. Every page returns rows now.

I did this, then paginated eight pages of ten and counted. 71 distinct rows instead of 80. Nine rows that a single limit 80 query returns never appeared on any page, and several rows showed up twice.

Ranks only exist inside the pool

RRF does not score documents. It scores ranks, and a rank is meaningless except relative to the other rows in the same candidate list.

Widening the text CTE from 60 rows to 130 does not just append 70 rows at the bottom. It admits rows that were not in the text candidate list at all, and any of those already sitting in the vector list now pick up a second contribution they did not have before. Their fused score jumps and they climb past rows the user already saw on page three.

Each page is computed against a different candidate set, so each page is a different ranking. RRF is extremely sensitive to which rows are in the pool, which is the property that makes it work in the first place.

Empty pages are at least visible. Duplicates and silently missing rows are not.

What works

Make the pool a function of the query and never of the page. Pick the deepest page you are willing to serve, set both CTE limits to that constant, and refuse to go past it.

-- in both CTEs. a constant, not derived from the offset
limit 500

Every page is now a window onto one stable ranking. Page one and page eight are slices of the same list.

When someone asks for offset 600, return an error instead of an empty page. Elasticsearch does this with index.max_result_window, which defaults to 10,000 and throws past it. That felt hostile to me until I understood the alternative was a different ranking on every page.

The cost is predictable too: one constant-size candidate scan per query no matter how deep anyone scrolls. Sizing from the offset means scroll depth drives query cost up forever.

Two smaller things

row_number() over () with no order by shows up in several published hybrid search functions:

select id, distance, row_number() over () as rank from semantic

It follows the subquery's order today. Nothing guarantees that, and it is the kind of assumption that breaks under a different plan or a parallel scan. Write row_number() over (order by distance).

The other is tiebreakers. ts_rank_cd ties heavily. On one corpus I measured it produced 3 distinct values across 3,399 matching rows. With order by ts_rank_cd(...) desc limit 50 and nothing after it, which 50 rows come back is arbitrary and can differ between two identical queries. Add , id to that order by, to the vector side, and to the outer query. Without it a row can legitimately appear on two different pages of the same result set.

Summary

The candidate pool is your whole result set, so size it once from the query and never from the offset. Error past it rather than returning an empty page. Put a tiebreaker on every order by that feeds a limit.

I packaged this and a few other things into pghybrid, which does hybrid search on plain pgvector without any extension you cannot install on managed Postgres. MIT, Python and TypeScript. The rules above matter more than the library though, and they apply to whatever you have already written.