Hibernate Pagination: OFFSET vs Keyset for Deterministic Page Traversal

If you are paging a Hibernate result set with setFirstResult and setMaxResults and your table grows past a few thousand rows, you will eventually hit two problems: pages that slow down as the offset grows, and rows that appear twice or vanish between pages when data changes underneath you. The useful answer is this: keep OFFSET pagination for small, mostly static result sets, and switch to keyset (cursor) pagination — filtering on the last seen value of a unique, indexed, monotonically increasing column — for anything large or frequently written.

Why OFFSET pagination drifts and slows down

setFirstResult(n) translates to SQL OFFSET n. The database still has to scan and discard those n rows on every request, so page 100 costs roughly 100 times the row-scanning work of page 1. Worse, without a total ORDER BY on a unique column, the database is free to return rows in any order that satisfies the plan, which can change between executions — especially across transaction boundaries or after statistics updates. That is how users see the same row on pages 2 and 3.

Keyset pagination avoids both issues. Instead of "skip 5000 rows," you say "give me rows where id > 5000, ordered by id, limit 20." The database uses the index on id to jump directly to the starting point, so every page costs the same regardless of depth, and each row is identified by a stable value rather than a position that shifts under concurrent inserts and deletes.

A worked keyset example in HQL

Assume an Order entity with an auto-increment primary key. The first page has no cursor; subsequent pages pass the last ID from the previous page:

// Runs in a service/repository class with an open Session or EntityManager.
// No special permissions beyond normal entity read access.

// Page 1: no cursor yet
List<Order> page = session.createQuery(
        "from Order o order by o.id asc", Order.class)
    .setMaxResults(20)
    .getResultList();

// Page N: lastSeenId is the id of the last row from the previous page
List<Order> nextPage = session.createQuery(
        "from Order o where o.id > :cursor order by o.id asc", Order.class)
    .setParameter("cursor", lastSeenId)
    .setMaxResults(20)
    .getResultList();

The cursor must be a column that is unique, indexed, and monotonically increasing — a primary key or a version/sequence column. If you sort by a non-unique column like createdAt, make the cursor a compound predicate such as where (o.createdAt, o.id) > (:ts, :id) so ties cannot skip or repeat rows.

If you must keep OFFSET pagination (e.g., a UI that jumps to arbitrary page numbers), always add order by o.id (or another unique column) as the final sort key. That alone fixes the non-determinism, though not the deep-offset cost.

Limits and common mistakes

• Query cache invalidation. Hibernate's query cache keys results by the exact query string plus parameter values. Every distinct offset or cursor value is a separate cache entry, so paginated queries rarely benefit from the query cache and can pollute it. Do not enable caching on paginated queries expecting hit-rate gains.
• Joins inflate row counts. Paginating a query that joins a lazy one-to-many collection (or uses a fetch join on one) produces duplicate parent rows in SQL. Hibernate deduplicates entities in memory, but OFFSET/LIMIT apply to SQL rows, so pages come back short and counts are wrong. Paginate on the root entity ID first, then fetch associations in a second query, or use a DTO projection.
• Keyset cannot jump to page N. Cursors only move forward (or backward with a reversed comparison). If your UI needs "go to page 47," you either keep OFFSET for that path or accept sequential traversal.
• Deletes still shift OFFSET pages. Even with a unique ORDER BY, a row deleted on page 1 pulls a row from page 2 into page 1's range, so a client iterating by offset skips it. Keyset pagination is immune because the cursor anchors to a value, not a position.

How to verify the choice

Run the same query at page depths 1, 5, 10, and 100 with ORDER BY on the primary key. With OFFSET, execution time should grow visibly with depth; with keyset it should stay flat. Confirm with your database's EXPLAIN ANALYZE (PostgreSQL) or equivalent: the keyset plan should show an index scan starting at the cursor value rather than a scan discarding offset rows. Also check row-count consistency — issue two overlapping keyset pages and confirm the boundary row appears exactly once.

This behavior is stable across Hibernate 5.x and 6.x and all major databases, but validate against your specific version and dialect, since pagination SQL generation is dialect-specific.