Hibernate JOIN FETCH Pagination Falls Back to In-Memory Processing

Direct answer

Hibernate ORM applies firstResult/maxResults in memory when a paginated HQL/JPQL/Criteria query uses JOIN FETCH on a collection-valued association and the dialect would otherwise emit a row-level LIMIT/OFFSET. Because joined rows multiply parent rows, a SQL row limit can truncate child collections or skip parent entities, so Hibernate materializes the full result set and truncates it in the JVM.

Confirmed behavior

• Collection-valued JOIN FETCH associations prevent Hibernate from pushing pagination to SQL when the dialect supports native limits.
• Single-valued fetch joins are usually safer to limit, but complex association graphs can still prevent pushdown.
• Adding DISTINCT to a collection fetch join does not make row-level LIMIT safe; it only deduplicates root rows and can still truncate child collections.

Likely explanation

The decision is driven by query shape: when the JDBC row count differs from the distinct root-entity count because of collection fetches, Hibernate falls back to in-memory pagination. Multiple collection JOIN FETCHes create a Cartesian product, so row-based limits are almost always unsafe and in-memory truncation is expected unless the query is rewritten.

How to verify before relying on it

No public API is confirmed to reliably predict in-memory pagination before execution. The practical check is to enable SQL logging and inspect the generated statement:

hibernate.show_sql=true
hibernate.format_sql=true

Run the exact paginated query and confirm whether the SQL contains LIMIT/OFFSET for your dialect. Also check Hibernate logs for the collection-fetch pagination warning, but treat absence of a warning as inconclusive: message text varies by version and some code paths may truncate silently. Compare the number of JDBC rows fetched against maxResults; materializing many more rows than requested indicates in-memory pagination.

Recommended approach

Instead of paginating a collection fetch join, paginate a root-only query for identifiers, then fetch the page and initialize collections separately with an IN clause or batch fetching. This keeps row counts aligned with the requested page and avoids loading the full result set into memory.

Version and dialect caveats

Exact pushdown boundaries and warning behavior remain version- and dialect-specific across Hibernate 6.x. Do not assume a newer release fixes this. Repeat the SQL verification after any ORM upgrade, dialect change, or mapping change. Native SQL queries are out of scope for this behavior.