Hibernate pagination with setFirstResult() triggers in‑memory paging warning

Hibernate pagination with setFirstResult() triggers in‑memory paging warning

In a Java application using Hibernate 6, the goal is to retrieve a subset of a large @OneToMany collection via JPQL pagination, using setFirstResult() and setMaxResults(). The application must avoid loading the entire collection into memory and maintain acceptable query performance.

Constraints include a large offset value (e.g., setFirstResult(10_000)) and the use of JOIN FETCH to eagerly load related entities. When both are combined, the Hibernate logs emit the warning HHH000104: firstResult/maxResults specified with collection fetch; applying in memory!, indicating that the database cannot apply the limit and the JVM must perform in‑memory pagination.

This behavior varies across Hibernate versions and SQL dialects; some databases translate LIMIT/OFFSET differently, while others may support OFFSET/FETCH. The unresolved decision is whether to modify the query strategy to avoid the warning, or to accept the memory cost and rely on JVM tuning.

Questions:

• Does using JOIN FETCH with setFirstResult() always lead to in‑memory paging in Hibernate 6, or is it dependent on the underlying database dialect?
• What alternative query patterns (e.g., subquery pagination, batch fetching) can preserve eager loading semantics while allowing database‑level pagination?
• How can we measure the impact of large offsets on query performance and JVM memory usage in a production environment?