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

Question

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?

Siddharth · Answer

Answer
Using JOIN FETCH together with setFirstResult() does not depend on the database dialect; the warning HHH000104: firstResult/maxResults specified with collection fetch; applying in memory! appears because the join produces multiple rows per root entity, so Hibernate cannot safely push the LIMIT/OFFSET to SQL and falls back to in‑memory pagination.
Confirmed facts

The warning is emitted by Hibernate core, not by the JDBC driver.
When a collection is fetched via JOIN FETCH, one parent row can generate N child rows, making the total result set larger than the number of distinct parents.
Hibernate therefore cannot guarantee that applying LIMIT/OFFSET at the database level will return the correct page of parents.

Steps to eliminate the warning while keeping eager loading

First step – paginate identifiers only
// Query that returns only the IDs of the parent entities
String idQuery = "SELECT p.id FROM Parent p WHERE …";
List ids = entityManager.createQuery(idQuery, Long.class)
        .setFirstResult(offset)
        .setMaxResults(pageSize)
        .getResultList();

Second step – fetch the page of parents with their collections
if (!ids.isEmpty()) {
    String parentQuery = "SELECT DISTINCT p FROM Parent p " +
                         "JOIN FETCH p.children WHERE p.id IN :ids";
    List page = entityManager.createQuery(parentQuery, Parent.class)
            .setParameter("ids", ids)
            .getResultList();
    // page now contains the requested slice with children eagerly loaded
}

Alternative – use batch or subselect fetching after the ID page
// After loading the ID page, initialize collections in batches
entityManager.createQuery("SELECT p FROM Parent p WHERE p.id IN :ids", Parent.class)
        .setParameter("ids", ids)
        .setHint("org.hibernate.fetchSize", 50) // or use @BatchSize
        .getResultList();

Measuring impact of large offsets

Enable Hibernate statistics and SQL logging:
hibernate.generate_statistics=true
hibernate.show_sql=true
hibernate.format_sql=true

Compare jdbcConnections and prepareStatementCount with the number of entities returned for a given setFirstResult value.
Monitor JVM heap usage and GC logs during requests with large offsets; a rising heap indicates in‑memory row accumulation.
Run the generated SQL through the database’s EXPLAIN ANALYZE to see how many rows are scanned before the LIMIT/OFFSET is applied.
If you have a stable, unique indexed column (e.g., an auto‑increment id or a timestamp), consider keyset (seek) pagination as a replacement for OFFSET; this avoids scanning skipped rows entirely.

Missing diagnostic detail that could change the recommendation: Do you have a stable, unique, indexed column suitable for keyset pagination? If yes, keyset pagination may replace the two‑step ID query and further reduce overhead.

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