Solving the Hibernate In-Memory Pagination Trap

The Silent Performance Killer: In-Memory Pagination

You have a Java application using Hibernate and a database with millions of records. To keep the UI responsive, you implement pagination using setFirstResult() and setMaxResults(). Everything works perfectly in staging with 100 records. But in production, as your dataset grows, the application starts throwing OutOfMemoryError or slows to a crawl, even though the generated SQL looks correct.

The problem is often a hidden interaction between Join Fetching and Pagination. When you use JOIN FETCH to solve the N+1 select problem (where Hibernate executes one query for the parent and N queries for the children), Hibernate cannot always translate the pagination limit to the database level. Instead, it fetches every matching record into the JVM memory and performs the pagination in Java. This is the "In-Memory Pagination Trap."

Offset-Based vs. Keyset Pagination

Before fixing the memory issue, it is important to choose the right pagination strategy. Most developers start with offset-based pagination, but it carries a hidden cost.

Offset-Based Pagination

This uses the LIMIT and OFFSET clauses in SQL. While simple, the database must still scan through all the rows leading up to the offset. If you request page 1,000 (offset 20,000), the database reads 20,000 rows only to discard them and return the next 20.

Keyset Pagination (The Seek Method)

Keyset pagination avoids the scan by filtering on a unique, indexed identifier (like an ID or a timestamp) from the last record of the previous page. Instead of saying "skip 20,000 rows," you say "give me 20 rows where ID > 54021." This allows the database to jump directly to the record using an index.

Practical Implementation: Avoiding the Memory Trap

To prevent Hibernate from pulling your entire database into memory when fetching collections, avoid using JOIN FETCH in the same query as pagination. Instead, use a two-step approach or a @BatchSize configuration.

The Two-Step Fetch Strategy

First, fetch the IDs of the parent entities using pagination. Second, fetch the full entities and their associations using those IDs.

// Step 1: Fetch only the IDs of the parents for the current page
// Run this on the Application Server with appropriate DB permissions
List<Long> ids = entityManager.createQuery(
    "SELECT p.id FROM Parent p", Long.class)
    .setFirstResult(0)
    .setMaxResults(20)
    .getResultList();

// Step 2: Fetch the full entities and their children using the IDs
// This prevents the in-memory pagination warning
List<Parent> parents = entityManager.createQuery(
    "SELECT p FROM Parent p JOIN FETCH p.children WHERE p.id IN :ids", Parent.class)
    .setParameter("ids", ids)
    .getResultList();

Verification and Diagnostics

To verify if your application is falling into the in-memory trap, enable SQL logging in your application.properties or hibernate.cfg.xml:

spring.jpa.show-sql=true
logging.level.org.hibernate.SQL=DEBUG

Check the logs for a warning similar to: HHH000104: firstResult/maxResults specified with collection fetch; applying in memory!. If you see this, your application is at risk of crashing as the dataset grows.

Trade-offs and Limitations

While the two-step fetch solves the memory issue, it introduces a trade-off in network round-trips. You are now executing two queries instead of one. However, two efficient queries are always preferable to one query that crashes the JVM.

Additionally, remember that keyset pagination requires a strictly ordered, unique column. If you need to allow users to jump to a random page (e.g., "Go to page 500"), keyset pagination cannot do this; you must revert to offset-based pagination and accept the performance hit for deep pages.

Actionable Summary

• Audit your logs for the HHH000104 warning to find hidden in-memory pagination.
• Separate your queries: Use one query to paginate IDs and a second query with JOIN FETCH to retrieve the data.
• Switch to Keyset Pagination for large datasets where users typically browse sequentially (infinite scroll) rather than jumping to specific page numbers.