Hibernate Pagination: Transitioning from Offset to Keyset for Large Datasets

Direct Answer

To keep the same EntityManager / Query creation path but avoid deep OFFSET scans, replace the offset logic with a key‑based predicate on the ORDER BY column. Bind the last seen key value and only set setMaxResults(pageSize)—do not call setFirstResult(). The HQL/JPQL string changes, but the rest of the API stays identical.

Keyset Transition Example

// Single‑column unique key
String hql = "from Order o where o.id > :lastId order by o.id asc";
TypedQuery q = em.createQuery(hql, Order.class);
q.setParameter("lastId", lastId); // last id from previous page
q.setMaxResults(pageSize); // no setFirstResult
List page = q.getResultList();

For a composite key you can use a lexicographic comparison that most dialects support:

String hql = "from Order o where (o.a, o.b) > (:lastA, :lastB) order by o.a asc, o.b asc";
// or, if row‑value syntax is unavailable:
// where o.a > :lastA or (o.a = :lastA and o.b > :lastB)

When In‑Memory Pagination Happens

Hibernate pushes LIMIT/OFFSET to SQL only when it can apply the slice after the database has returned rows. A trigger for in‑memory pagination is:

• A join fetch that pulls a collection association (one‑to‑many or many‑to‑many) together with setFirstResult() or setMaxResults().
• Using select distinct in combination with a collection join fetch, which forces Hibernate to deduplicate rows in the JVM.

In these cases the SQL generated contains the LIMIT/OFFSET, but Hibernate reads the full join result set into memory and performs the slicing after hydration. This can lead to OutOfMemoryError on large datasets.

Mitigation Strategies

1. Remove the collection join fetch if you only need a page of root entities. Fetch the collection lazily or in a separate query.
2. Use join fetch on to‑one associations; these are usually compatible with SQL‑level pagination.
3. Verify by enabling SQL logging. The generated SQL should not contain OFFSET in a keyset query, and the query plan should show an index seek on the sort key.
4. Check for Hibernate’s “in‑memory pagination” warning in the logs (the exact message varies by version and dialect). If present, the query is being sliced in the JVM.

Diagnostic Question

Does your current query include a join fetch of a collection association? If so, keyset pagination alone will not prevent in‑memory hydration; you’ll need to adjust the fetch strategy as described above.

Assumptions & Uncertainty

• The guidance assumes a relational database with an indexed sort key and a Hibernate version that supports SQL‑level LIMIT/OFFSET pushing (Hibernate 5.4+). Exact behavior can differ in older releases or in dialects lacking OFFSET support. • Composite keyset predicates may require dialect‑specific syntax; the examples use standard JPQL where possible. • The in‑memory pagination trigger is documented in Hibernate’s migration notes, but the exact log message and whether pagination is pushed can vary by version and configuration. Verify against your environment.