Architecting Hibernate Pagination to Prevent In-Memory Exhaustion

The In-Memory Pagination Problem

When using Hibernate (Java ORM), developers often combine setFirstResult() and setMaxResults() with JOIN FETCH to resolve the N+1 select problem. However, this combination triggers a critical failure mode: in-memory pagination. Because SQL joins create a Cartesian product—where a parent entity is duplicated for every child entity retrieved—the database cannot accurately apply LIMIT and OFFSET to the resulting rows without cutting off child records mid-entity.

To maintain entity integrity, Hibernate fetches the entire result set into the JVM memory and performs the slicing manually. For large datasets, this leads to severe latency and OutOfMemoryError crashes. The takeaway is that JOIN FETCH and pagination are mutually exclusive at the database level.

Design Requirements for Large-Scale Retrieval

To handle large datasets without crashing the application, the retrieval architecture must meet these constraints:

• Constant Memory Footprint: The JVM must only hold the number of records requested for the current page, regardless of the total dataset size.
• Database-Level Slicing: The LIMIT and OFFSET (or equivalent dialect syntax) must be executed by the database engine.
• Deterministic Ordering: Results must be sorted by a unique, non-nullable, and indexed column to prevent records from shifting between pages.

The Smallest Suitable Design: Two-Step Fetching

The most reliable way to paginate entities with collections is to decouple the identification of the parent IDs from the fetching of the associated data. This avoids the Cartesian product during the pagination phase.

Step 1: Paginated ID Retrieval

Execute a query that selects only the primary keys of the parent entity. Since no collections are fetched, Hibernate can safely append pagination clauses to the SQL.

// Run on Application Server | Permissions: Read-only DB access
// Assumption: Hibernate 5.x or 6.x
List<Long> ids = session.createQuery(
    "SELECT p.id FROM Parent p WHERE p.status = :status ORDER BY p.id ASC", Long.class)
    .setParameter("status", Status.ACTIVE)
    .setFirstResult(0)
    .setMaxResults(20)
    .getResultList();

Step 2: Entity Graph Fetching

Use the retrieved IDs to fetch the full entity graphs. By applying JOIN FETCH here, the database only joins children for the specific subset of parents identified in Step 1.

// Run on Application Server
List<Parent> parents = session.createQuery(
    "SELECT p FROM Parent p JOIN FETCH p.children WHERE p.id IN :ids ORDER BY p.id ASC", Parent.class)
    .setParameter("ids", ids)
    .getResultList();

Trust and Data Boundaries

To protect the system from malicious or accidental resource exhaustion, implement the following boundaries at the API and Session layers:

• Hard Page Limit: Enforce a maximum maxResults (e.g., 100) in the service layer. This prevents a request for maxResults(1000000) from overloading the JVM during the second step.
• Query Timeouts: Set a timeout on the Query object to terminate long-running scans that occur if the ORDER BY column is not properly indexed.

Comparison: Offset vs. Keyset Pagination

While the two-step fetch solves memory issues, the performance of setFirstResult() (Offset pagination) degrades as the page depth increases because the database must scan and discard skipped rows.

Strategy	SQL Mechanism	Complexity	Performance Trend
Offset-based	OFFSET X LIMIT Y	Low	Linear degradation as X increases
Keyset (Seek)	WHERE id > :lastId LIMIT Y	Medium	Constant performance regardless of depth

Operational Checks and Failure Modes

Diagnostic Check

To verify if in-memory pagination is occurring, monitor application logs for the following warning:

HHH000104: firstResult/maxResults specified with collection fetch; applying in-memory pagination

Execution Plan Verification

Run the generated SQL in a database console. If the execution plan shows a Full Table Scan or high cost associated with the OFFSET clause, the query will become a bottleneck as the table grows.

Failure Modes

• Non-Deterministic Sort: Using a non-unique column in ORDER BY can cause records to appear on multiple pages or be skipped entirely.
• IN Clause Limits: Some databases (e.g., Oracle) limit the number of elements in an IN clause (typically 1,000). If the page size exceeds this, the second step of the fetch will fail.

Conditions for Design Change

The two-step fetch is optimized for UI-driven pagination. If the requirement shifts to bulk data processing (e.g., exporting 1 million rows to CSV), migrate to ScrollableResults or a StatelessSession. This avoids the overhead of the Hibernate Persistence Context and the memory pressure of loading large lists into the JVM.