Cursor-Based Hibernate Pagination: Faster Deep Queries

The Problem: Offset Queries Slow Down with Depth

Hibernate's setFirstResult/setMaxResults generates an SQL OFFSET query. The database must read and discard every row before the target page, so deep pages become progressively more expensive — effectively O(n) in the offset — causing high latency on large tables.

The Fix: Keyset (Cursor) Pagination

Instead of skipping rows, remember the last ID from the previous page and filter on it:

• Use WHERE id > :lastId so the database reads only the rows you need.
• Works with HQL, JPQL, Criteria, or native SQL, provided the entity has a unique, monotonically increasing identifier such as a generated primary key.

// Run in a service/DAO with an open Hibernate Session (e.g., inside a @Transactional method)
List<Item> page = session.createQuery(
    "from Item i where i.id > :lastId order by i.id", Item.class)
  .setParameter("lastId", lastSeenId) // 0 or null-safe start for the first page
  .setMaxResults(pageSize)
  .list();
// Next cursor: page.get(page.size() - 1).getId()

Check the result with EXPLAIN ANALYZE on the generated SQL: the cursor version should show an index range scan instead of a full scan with a large offset. Benchmarks on large tables typically show 10–100× speedups on deep pages.

Trade-offs and Limitations

Keyset pagination has real constraints. If the primary key is not monotonic or has gaps, rows can be skipped or repeated. You cannot jump to an arbitrary page number — only forward (or backward with a reversed condition). Lazy-loaded associations accessed during iteration can trigger N+1 queries, so batch-fetch or join-fetch where needed. Under concurrent writes, a low transaction isolation level may surface rows inserted after the cursor was captured; verify ordering consistency across runs.

Actionable Next Step

Pick one hot query in your application that uses setFirstResult with large offsets, rewrite it with the id > :lastId pattern above, and compare execution plans before and after. Keep setMaxResults to bound memory, and close the session promptly if you switch to ScrollableResults for streaming.