Hibernate Pagination: Transitioning from Offset-based to Keyset Seek for Deep Page Performance

Question

Performance Degradation in Deep Pagination
When utilizing setFirstResult() and setMaxResults() in Hibernate ORM, the generated SQL typically relies on LIMIT and OFFSET clauses. While efficient for initial pages, this approach causes linear performance degradation as the offset increases because the database must scan and discard all preceding rows before returning the target window.

Memory and Consistency Constraints
There is a known risk when combining pagination with join fetches, where Hibernate may perform pagination in memory rather than at the database level, potentially leading to OutOfMemoryError. Additionally, offset-based pagination is susceptible to result drifting if records are inserted or deleted between requests.

To maintain constant-time performance for large datasets, a transition to keyset pagination (the seek method) is required, filtering by the last seen unique identifier instead of a numeric offset.

How can keyset pagination be implemented within the Hibernate Criteria API to avoid the performance penalties of setFirstResult()?
What is the recommended strategy to prevent in-memory pagination when using join fetches in a keyset-based approach?

Tasadduq Burney · Answer

Answer Replace setFirstResult() with a WHERE clause that filters on the last seen unique sort key (the "seek" method). This eliminates the OFFSET clause and lets the database use an index seek, giving constant‑time page retrieval. Confirmed facts Hibernate Criteria API (since 5.0) allows you to build predicates with CriteriaBuilder and add them to a CriteriaQuery. When the query contains only a simple equality/inequality on an indexed column and an ORDER BY on the same column, most relational databases generate an index seek rather than a full scan. Join fetches do not trigger Hibernate’s in‑memory pagination fallback as long as the query does not produce duplicate rows that require post‑processing; using join fetch for to‑one associations or @BatchSize for collections keeps pagination in the DB. Likely explanation / steps Choose a unique, monotonic sort key – typically the primary key or a composite of (timestamp, id). This guarantees that keyset pagination will not miss or duplicate rows. Obtain the last key from the previous page – e.g., the ID of the last entity returned. Build the CriteriaQuery: CriteriaBuilder cb = em.getCriteriaBuilder(); CriteriaQuery cq = cb.createQuery(Entity.class); Root root = cq.from(Entity.class); // Seek predicate: greater than the last seen key Predicate seek = cb.greaterThan(root.get("id"), lastSeenId); cq.where(seek); // Order by the same key to maintain stable ordering cq.orderBy(cb.asc(root.get("id"))); // Limit page size TypedQuery query = em.createQuery(cq); query.setMaxResults(pageSize); List page = query.getResultList(); Apply join fetches safely – add join fetch for needed to‑one associations before setting the seek predicate. For collections, prefer @BatchSize or a separate query to avoid duplicate rows that would force Hibernate to paginate in memory. Execute and capture the next key – after retrieving the page, the ID of the last element becomes the lastSeenId for the subsequent request. Verification Enable SQL logging (hibernate.show_sql=true) and confirm the generated SQL lacks an OFFSET clause. Run EXPLAIN on the logged query; look for an "Index Seek" (or "Range Scan") on the sorted column. Benchmark a deep page (e.g., page 1000) using keyset vs. setFirstResult(10000); keyset should show flat latency. Missing diagnostic detail: If your sort key is not guaranteed to be unique and strictly monotonic (e.g., a non‑unique timestamp), keyset pagination could skip or duplicate rows. Confirm the uniqueness of the chosen key before proceeding.

Hibernate Pagination: Transitioning from Offset-based to Keyset Seek for Deep Page Performance

Performance Degradation in Deep Pagination

1 answer

Answer

Confirmed facts

Likely explanation / steps

Verification

Your perspective belongs here.

0 question comments