Hibernate Deep Pagination: Why OFFSET Slows Down and Keyset Stays Fast

The deep-page latency problem

You ship a REST endpoint that returns customer orders. The first few pages load instantly. A support agent jumps to page 500 to investigate an old dispute, and the request hangs for seconds. Nothing in the code changed; only the offset grew. This is the classic deep-pagination trap, and in Hibernate it usually comes down to a single choice: whether you are asking the database to skip rows or to seek a position.

The useful takeaway: setFirstResult() and setMaxResults() are fine for shallow, human-navigable lists. Once users scroll deep or export large datasets, a keyset (cursor) query keeps response time flat, while offset queries get slower the further you go.

Why OFFSET degrades linearly

Offset pagination maps directly to SQL LIMIT and OFFSET. Hibernate exposes this through setFirstResult() (the offset) and setMaxResults() (the limit). The database does not teleport to row 100,000. It walks the index or table, counts the rows you asked it to skip, and only then returns the page you want.

Because the engine must examine every discarded row, latency grows with the offset. At an offset of 50 the cost is trivial. At 100,000 the same query may trigger a full index scan or sequential scan, depending on the database and the available indexes. The work is wasted: those rows are counted and thrown away.

There is a second, subtler problem. If rows are inserted or deleted between page requests, the offset window shifts. A record that was on page one may slide to page two, producing duplicates; a deletion may cause a record to be skipped entirely. For a report that must reconcile exactly, this instability matters.

Keyset pagination: seek, don't skip

Keyset pagination—also called the seek method—avoids counting. Instead of asking for “rows 100,001 through 100,050,” you ask for “the next 50 rows after the last one I saw.” You add a WHERE clause on a unique, indexed column, usually the primary key, and order by that same column.

The database can use the index to jump straight to the cursor position. It does not scan the preceding rows, so the lookup time stays roughly constant regardless of how deep you are. In Hibernate you express this with HQL/JPQL or the Criteria API; you still call setMaxResults() for the page size, but you replace setFirstResult() with a predicate.

One caveat: the seek column must be unique and indexed. If you filter on a non-unique timestamp, ties can cause rows to be missed or repeated unless you add a tie-breaker column. If the index is missing, the database falls back to a scan and the performance benefit disappears.

Worked example: cursor-based HQL in Hibernate

Assume an Order entity mapped to an orders table, with id as the primary key. The offset version looks familiar:

List<Order> page = session.createQuery(
    "FROM Order o ORDER BY o.id", Order.class)
    .setFirstResult(100_000)
    .setMaxResults(50)
    .getResultList();

The keyset version replaces the offset with a cursor parameter. The client sends the id of the last order from the previous page:

List<Order> page = session.createQuery(
    "FROM Order o WHERE o.id > :lastId ORDER BY o.id", Order.class)
    .setParameter("lastId", lastSeenId)
    .setMaxResults(50)
    .getResultList();

On the first request, lastId can be 0 or the minimum value for the column. On subsequent requests, you read the final entity’s identifier from the result list and pass it back. The SQL generated is a range scan on the primary-key index, not a skip-and-count.

Trade-offs and how to verify

Keyset pagination is not free. The most obvious limitation is navigation: you cannot jump directly to page 500 without knowing the cursor from page 499. Infinite scrolls and “Load more” buttons fit naturally; a paginated admin grid with numbered page links does not. If users need arbitrary page jumps, offset pagination remains the pragmatic choice for small result sets.

You also give up some flexibility in ordering. Keyset requires a deterministic, unique sort key. Sorting by a mutable column like status is fragile unless you combine it with a unique tie-breaker.

Before committing, verify the behavior on your actual database and Hibernate version. Run the offset query at a high offset and inspect the execution plan; look for full index scans or sequential scans and a large number of rows examined. Then run the keyset query and confirm the plan uses an index range scan on the seek column. Finally, test concurrent modification: delete a row from the first page while a second request is in flight. The offset response may duplicate or skip a record; the keyset response remains stable as long as you only move forward.

If you need a middle ground, some teams cap the offset (for example, no page beyond 1,000) and force a filter or search for deeper access. That keeps the UI simple while avoiding the worst latency spikes.

Choosing your pagination strategy

Use offset pagination when the dataset is bounded, the UI requires numbered page jumps, and the maximum offset stays small. Use keyset pagination when users scroll deep, export large volumes, or consume an API feed where stability under concurrent writes matters more than random access. The decision is not about which API is more modern; it is about whether your database is skipping rows or seeking a position.