Hibernate Pagination: Transitioning from Offset to Keyset for Resource Optimization

Question

Optimizing Low-Traffic Workloads
When managing resource consumption for low-traffic workloads in Hibernate, the choice of pagination strategy directly impacts database CPU and memory overhead. The standard setFirstResult() and setMaxResults() approach relies on offset-based pagination, which requires the database to scan and discard rows as the offset increases.

Implementation Constraints
For datasets that may grow over time, deep paging via offsets can lead to linear performance degradation. While keyset pagination (the seek method) avoids this by filtering based on the last seen identifier, it introduces constraints regarding the sort order and the requirement for indexed columns.

There is also a risk of Hibernate performing pagination in memory if Join Fetch is used incorrectly within the query, potentially leading to OutOfMemoryError despite the low traffic volume.

How can the transition from offset-based to keyset pagination be implemented while maintaining support for non-sequential sorting?
What is the most reliable way to ensure the database dialect handles the limit logic rather than Hibernate performing in-memory filtering?

Muntasir · Answer

Confirmed facts

Keyset pagination replaces OFFSET with a WHERE predicate that mirrors the ORDER BY columns, keeping cost constant regardless of page depth. Hibernate will paginate in memory only when a query uses JOIN FETCH on a collection; otherwise the LIMIT/FETCH clause is sent to the database.

Likely explanation / implementation steps

Remove setFirstResult entirely; keep only setMaxResults(pageSize).
Build the seek predicate that matches the exact ORDER BY list (including direction) and appends the primary key as a tiebreaker.
For ASC sorts use (col1, col2, …, id) > (:lastCol1, :lastCol2, …, :lastId); for DESC flip the operator or use < per column.
If the sort columns can be NULL, add explicit NULLS FIRST/LAST handling or ensure they are NOT NULL.
Example HQL for a multi‑column sort (name ASC, createdDate DESC):

SELECT e FROM Entity e
WHERE (
    (e.name > :lastName) OR
    (e.name = :lastName AND e.createdDate < :lastDate) OR
    (e.name = :lastName AND e.createdDate = :lastDate AND e.id > :lastId)
)
ORDER BY e.name ASC, e.createdDate DESC, e.id ASC

Set the parameters (:lastName, :lastDate, :lastId) from the last row of the previous page; for the first page omit the WHERE clause.
Execute the query with setMaxResults(pageSize); Hibernate will render a LIMIT/FETCH clause and no OFFSET.
To guarantee DB‑side limiting, avoid JOIN FETCH on any @OneToMany or @ManyToMany associations in this query. Load collections afterward via a second query, @BatchSize, or an EntityGraph applied to the limited result set.
Verify the generated SQL contains a WHERE clause with the seek predicate and a LIMIT/FETCH, and check the logs for the warning HHH000104; its absence confirms in‑memory pagination is not happening.

Missing diagnostic detail

Please confirm your Hibernate version (5.x vs 6.3+). If you are on 6.3 or newer you can use the built‑in KeyedPage/keyed‑scroll API, which automates the seek predicate; otherwise the manual WHERE‑predicate approach shown above is required.

Hibernate Pagination: Transitioning from Offset to Keyset for Resource Optimization

Optimizing Low-Traffic Workloads

Implementation Constraints

1 answer

Confirmed facts

Likely explanation / implementation steps

Missing diagnostic detail

Your perspective belongs here.

0 question comments