OutOfMemoryError during pagination with join fetch in Hibernate ORM

In-Memory Pagination Behavior

When utilizing setFirstResult() and setMaxResults() in Hibernate, the framework typically delegates pagination to the database using dialect-specific SQL clauses. However, a known behavior occurs when these methods are combined with join fetch on collection associations.

In environments with small datasets, such as local development, this configuration may appear functional. In production environments with large datasets, Hibernate may be unable to apply the pagination limit at the database level due to the Cartesian product created by the fetch join. This often results in the framework retrieving the entire result set into the JVM heap to perform pagination in memory.

Constraints and Verification

This behavior is dependent on the Hibernate version and the specific database dialect used. Verification typically requires monitoring JVM heap usage and inspecting generated SQL to confirm the absence of LIMIT or OFFSET clauses.

• Does Hibernate provide a mechanism to enforce database-level pagination when join fetch is present?
• What are the recommended alternatives to join fetch to avoid in-memory pagination while still reducing the N+1 select problem?