Avoid Deep Offset Pagination in Hibernate with Keyset Pagination

The problem is deep pages, not slow queries

First pages with Hibernate pagination are fast. Page 500 with setFirstResult and setMaxResults is slow. The query is correct, the strategy is not.

Hibernate translates offset pagination to SQL LIMIT and OFFSET. The database must walk to the offset row, discard it, and then return the page. Latency grows with offset.

How Hibernate implements offset pagination

With JPQL or Criteria API the pattern is:

TypedQuery<Order> q = em.createQuery(
  "SELECT o FROM Order o WHERE o.status = :status ORDER BY o.createdAt DESC", Order.class);
q.setParameter("status", OrderStatus.SHIPPED);
q.setFirstResult(pageNumber * pageSize);
q.setMaxResults(pageSize);

This produces SQL like:

SELECT * FROM orders WHERE status = 'SHIPPED' ORDER BY created_at DESC LIMIT 20 OFFSET 9980

setFirstResult is the offset, setMaxResults is the limit. This is offset pagination, not cursor pagination.

With a large offset the database scans and discards rows before returning results. Fetch plans compound the cost. A JOIN FETCH on a collection can multiply rows and cause Hibernate to paginate in memory, which is unsafe.

Keyset pagination with a WHERE clause

Keyset pagination removes OFFSET. The query filters on the last seen sort key and relies on an index to seek directly.

TypedQuery<Order> q = em.createQuery(
  "SELECT o FROM Order o WHERE o.status = :status AND o.createdAt < :cursor ORDER BY o.createdAt DESC", Order.class);
q.setParameter("status", OrderStatus.SHIPPED);
q.setParameter("cursor", lastSeenCreatedAt);
q.setMaxResults(pageSize);

The database can use an index on (status, createdAt) to find the first row after the cursor. Performance depends on that index existing.

Null handling for the first page is a common pitfall. A predicate like (:cursor IS NULL OR o.createdAt < :cursor) can prevent index-only seeks on some databases. A practical approach is to branch in code: run the cursor query when a cursor is present, run a plain query without the predicate for the first page.

Worked example in Spring Data JPA

Spring Data Pageable defaults to offset. For keyset you need explicit control.

public interface OrderRepository extends JpaRepository<Order, Long> {}

@Repository
public class OrderCursorRepository {
  private final EntityManager em;
  public OrderCursorRepository(EntityManager em) { this.em = em; }

  public List<Order> findNext(String status, LocalDateTime cursor, int limit) {
    String jpql = "SELECT o FROM Order o WHERE o.status = :status";
    if (cursor != null) {
      jpql += " AND o.createdAt < :cursor";
    }
    jpql += " ORDER BY o.createdAt DESC, o.id DESC";
    TypedQuery<Order> q = em.createQuery(jpql, Order.class);
    q.setParameter("status", status);
    if (cursor != null) {
      q.setParameter("cursor", cursor);
    }
    q.setMaxResults(limit);
    return q.getResultList();
  }
}

Service usage passes the cursor from the client, typically the createdAt and id of the last item returned.

List<Order> page = repo.findNext("SHIPPED", lastSeenCreatedAt, 20);

Note the tie-breaker o.id DESC. If createdAt is not unique, rows can be skipped or duplicated without a stable secondary key.

Trade-offs and limitations

• No random access. You cannot jump to page 10. Keyset works for infinite scroll or next/previous.
• Sort direction matters. Reverse ordering requires a different predicate.
• Index requirement. Constant-time seeks assume a composite index covering the filter and sort columns. Without it, the plan degrades.
• Fetch plans. Avoid JOIN FETCH on collections with pagination. Use entity graphs for single-valued associations and fetch in a separate query if needed.

How to verify the change

Enable SQL logging to see generated statements.

logging.level.org.hibernate.SQL=DEBUG
logging.level.org.hibernate.orm.jdbc.bind=TRACE

Run the offset query and the keyset query and compare the SQL. The offset version contains OFFSET. The keyset version contains a WHERE on the cursor and LIMIT only.

Inspect the execution plan on the database. In PostgreSQL use EXPLAIN or EXPLAIN ANALYZE. In MySQL use EXPLAIN with ANALYZE in 8.0+. Syntax and output differ between engines. Look for an index seek on the composite index and a row estimate close to the page size, rather than a high row scan with an offset.

Check entity state after pagination. Ensure the result list size is bounded by setMaxResults and that no additional N+1 queries fire for lazy associations accessed in the same transaction.

Changing pagination is a breaking change for clients that expect page numbers. Update API contracts and documentation before deploying.