Hibernate Pagination: Offset-based vs. Keyset-based for Large Datasets

The short answer

There is no universal row-count threshold where setFirstResult() becomes "too slow" — the cost scales with the offset value, not the table size. A 10-million-row table serves page 2 (offset 20) instantly, while a 200,000-row table can stall on offset 150,000. The practical rule: if users routinely page beyond offsets of roughly 10,000–50,000 rows, or your p95 query latency climbs past your API budget (commonly 100–300 ms) as pages deepen, keyset pagination wins. Below that, offset paging is fine and simpler.

Why offset degrades

setFirstResult(n) emits OFFSET n, and most databases must still read, sort, and discard those n rows before returning your page. Work grows linearly with page depth. Keyset pagination replaces the skip with a filter:

// Offset — degrades with depth
query.setFirstResult(page * size).setMaxResults(size);

// Keyset — constant cost, requires indexed unique key
List<Order> page = em.createQuery(
    "SELECT o FROM Order o WHERE o.id > :lastId ORDER BY o.id ASC", Order.class)
  .setParameter("lastId", lastSeenId)
  .setMaxResults(size)
  .getResultList();

The seek predicate uses the index directly, so page 1 and page 100,000 cost the same.

Is there a documented hybrid in Hibernate?

No — Hibernate (including 6.x) ships no built-in hybrid pagination mode. Both styles are just queries you write yourself. The common application-level hybrid is: use offset paging for the first N pages (where jumping is cheap and users actually click page numbers), then switch to keyset/"load more" semantics beyond that, or expose only next/previous navigation. Some teams also precompute page-boundary keys in a background job to restore random access, but that is custom logic, not a Hibernate feature.

Verify before you switch

1. Run both query forms against production-like data with EXPLAIN ANALYZE at shallow and deep offsets; compare latency, not row counts.
2. Confirm the keyset column has an index and the plan shows an index range scan, not a seq scan.
3. Ensure ordering is deterministic — if you sort by a non-unique column (e.g., created_at), append the primary key as a tiebreaker: ORDER BY createdAt, id, and filter on both.
4. Check that your client stores the last key per page and passes it back; losing it silently restarts pagination.

Caveats

Keyset requires a stable, unique, indexed ordering — changing the ORDER BY invalidates saved cursors, and concurrent inserts can shift results under either strategy. The latency thresholds above are heuristics from typical OLTP workloads, not measured guarantees; your storage engine, index coverage, and sort width move the crossover point. Measure on your own data before committing to a rewrite.