Solving the Hibernate N+1 Problem During Paginated Collection Fetches

The Performance Trap: In-Memory Pagination

When implementing pagination for entities that have associated collections, developers often encounter a performance cliff. You attempt to optimize the query using a JOIN FETCH to avoid the N+1 problem (where one query for parents triggers N additional queries for children), but you suddenly see a massive spike in memory usage or a warning in your logs: HH000104: firstResult/maxResults specified with collection fetch; applying in memory!

The core issue is that a SQL JOIN produces a Cartesian product. If one parent has ten children, the database returns ten rows for that single parent. Hibernate cannot reliably apply setFirstResult() and setMaxResults() at the database level because the row count no longer matches the entity count. To ensure the pagination is accurate, Hibernate pulls the entire result set into the application memory and paginates it there, which can lead to OutOfMemoryError as your dataset grows.

Diagnostic Matrix

Symptom	Likely Cause	Diagnostic Signal
Slow response times as data grows	N+1 Selects	SQL logs show one SELECT for parents, then dozens of SELECTs for children.
High JVM heap usage / OOM	In-Memory Pagination	Log warning HH000104; SQL logs show a single massive JOIN without a LIMIT clause.
Application Crash / Exception	Multiple Bag Fetch	MultipleBagFetchException when fetching two or more List collections.

Step-by-Step Resolution Path

Step 1: Verify the Query Behavior

Before applying a fix, confirm the exact behavior of your Hibernate version (assumed 5.x or 6.x). Enable SQL logging in your application.properties or persistence.xml:

hibernate.show_sql=true
hibernate.format_sql=true

Execute your paginated request. If you see a single query with a JOIN but no LIMIT or OFFSET clause in the generated SQL, Hibernate is paginating in memory.

Step 2: Implement the Two-Step Fetch Pattern

To achieve database-level pagination while avoiding N+1 selects, decouple the identification of the parents from the fetching of their children. This is the most reliable engineering decision for large datasets.

The Implementation Logic:

1. Query 1: Fetch only the primary keys (IDs) of the parent entities using standard pagination.
2. Query 2: Fetch the full entities and their collections using a JOIN FETCH, filtering by the IDs retrieved in Step 1.

Example Configuration:

// Step 1: Get paginated IDs (Executed at DB level)
List<Long> parentIds = entityManager.createQuery(
    "SELECT p.id FROM Parent p", Long.class)
    .setFirstResult(0)
    .setMaxResults(20)
    .getResultList();

// Step 2: Fetch full data for those IDs (Avoids N+1)
List<Parent> parents = entityManager.createQuery(
    "SELECT p FROM Parent p JOIN FETCH p.children WHERE p.id IN :ids", Parent.class)
    .setParameter("ids", parentIds)
    .getResultList();

Step 3: Handle Multiple Collections

If you need to fetch multiple collections (e.g., Parent.children and Parent.settings), avoid using JOIN FETCH on both in a single query, as this triggers a MultipleBagFetchException. Instead, fetch the first collection with a join and the second collection using a separate query with the same ID list, relying on the Hibernate Second-Level Cache or the current Session to merge the results.

Verification and Risks

How to Verify the Fix

• SQL Log Check: Ensure the first query now contains a LIMIT and OFFSET (or equivalent for your DB dialect).
• Query Count: You should see exactly two queries regardless of the page size (one for IDs, one for the data).
• Log Audit: Confirm that the HH000104 warning no longer appears in the logs.

Critical Limitations

• Memory Overhead: The IN clause has limits depending on the database (e.g., Oracle has a 1,000 element limit). Keep your page size (maxResults) reasonable.
• FetchType.EAGER: Do not change your mapping to FetchType.EAGER to solve this. This will apply the N+1 problem globally to every single query in your application, causing systemic degradation.

Rollback Procedure

Since this change modifies the JPQL/HQL query logic rather than the database schema, rollback involves reverting the repository method to the original single-query JOIN FETCH or standard findAll(Pageable pageable) call.