Arbitrary Page Jumps vs. Sequential Keyset Seeks for Large Hibernate Result Sets

Direct answer

No fixed offset makes keyset pagination mandatory. In practice, offset-based pagination with Query.setFirstResult()/Query.setMaxResults() remains acceptable for shallow pages and random jumps, and keyset seeks become the preferred approach once the offset is large enough that linear scan cost dominates. A common operational rule of thumb is to consider keyset when offset exceeds ~10,000 rows or about 5% of the total result set, whichever is smaller. A hybrid pattern is feasible: keep offset for the first N shallow pages and switch to keyset for deeper sequential navigation.

Confirmed facts

• Hibernate 5.x and 6.x map setFirstResult/setMaxResults to SQL LIMIT and OFFSET.
• Keyset pagination uses a WHERE predicate on an ordered anchor column plus LIMIT, no OFFSET.
• Keyset requires a stable, non-nullable ordering. Duplicates need a tie-breaker, typically the primary key.
• Offset forces the database to identify and discard preceding rows; cost grows with offset.

Likely explanation and threshold

The exact point where latency becomes unacceptable is not universal. It depends on database engine, index design, row width, and concurrent load. Measurable slowdown typically appears once offset exceeds a few tens of thousands of rows on typical indexed tables. The 10k rows / 5% rule is a planning heuristic, not a specification.

Keyset provides near-constant lookup time when the anchor columns are indexed and the ordering is immutable. If the sort column can change, rows can be missed or duplicated between pages.

Hybrid pattern

Yes. A documented hybrid keeps arbitrary page jumps for shallow pages and switches to keyset for deep navigation.

• Serve pages 0..N with offset pagination for direct jumps.
• Beyond the boundary, require sequential next/previous using the last row’s anchor values as the start point: WHERE (sort_col > :lastSort) OR (sort_col = :lastSort AND id > :lastId) then LIMIT :pageSize.
• Track the boundary in the service layer and reconstruct the keyset predicate from the last entity’s sort key(s) when the user moves past the threshold.

Complexity increases: the service must hide keyset tokens, handle sort changes, and decide the switch point.

Verification steps for this case

1. Enable Hibernate SQL logging and statistics, run queries with increasing setFirstResult and capture generated SQL.
2. Run EXPLAIN or EXPLAIN ANALYZE for offset vs keyset variants with the same page size.
3. Compare rows examined and wall time at representative offsets, e.g., 10k, 100k.

One missing diagnostic detail that changes the recommendation: which database engine you use and whether the chosen sort column(s) are covered by an immutable index. Without that, the threshold cannot be safely quantified.