Preventing Memory Exhaustion in Hibernate Pagination

The Problem: In-Memory Pagination

When developers implement pagination in Hibernate using setFirstResult() and setMaxResults(), they expect the database to return only the requested slice of data. However, certain query configurations—specifically those using JOIN FETCH on collection associations—force Hibernate to fetch the entire result set into the JVM memory to perform the slicing manually. This leads to OutOfMemoryError (OOME) as the dataset grows, regardless of the page size requested.

The Smallest Suitable Design

To ensure pagination happens at the database level, the persistence layer must generate SQL that includes LIMIT and OFFSET (or equivalent dialect-specific keywords). The most reliable design separates the retrieval of entity IDs from the retrieval of the full entity graphs.

Recommended Implementation Pattern:

1. Execute a paginated query to retrieve only the primary keys (IDs) of the entities for the current page.
2. Use those IDs in a second query to fetch the full entities and their associated collections using JOIN FETCH.

// Step 1: Fetch IDs only (Database-level pagination) 
List<Long> ids = entityManager.createQuery(
    "SELECT e.id FROM Entity e", Long.class)
    .setFirstResult(offset)
    .setMaxResults(pageSize)
    .getResultList();

// Step 2: Fetch full data for those IDs
List<Entity> results = entityManager.createQuery(
    "SELECT e FROM Entity e LEFT JOIN FETCH e.collections WHERE e.id IN :ids", Entity.class)
    .setParameter("ids", ids)
    .getResultList();

Trust and Data Boundaries

The boundary of data trust must be established at the Repository/DAO layer. The application service layer should never receive a List that could potentially contain the entire database table. By enforcing setMaxResults() at the persistence boundary, you prevent the JVM from attempting to instantiate thousands of Hibernate proxies that would otherwise saturate the heap.

Operational Checks and Verification

To verify that pagination is occurring in the database and not in memory, enable SQL logging in your application.properties or persistence.xml:

hibernate.show_sql=true
hibernateate.format_sql=true

Diagnostic Checklist:

• SQL Inspection: Check the logs for the presence of LIMIT and OFFSET. If these are missing but setFirstResult was called, Hibernate is paginating in memory.
• Query Count: Verify that the number of queries per page request is constant. If the number of queries increases linearly with the number of entities on the page, you have an "N+1" problem where lazy-loaded collections are being fetched individually.
• Heap Monitoring: Use a profiler (like VisualVM) to monitor heap usage when requesting a "deep page" (e.g., page 10,000). A spike in memory usage indicates a failure in the pagination boundary.

Failure Modes

Failure Mode	Cause	Symptom
In-Memory Slicing	JOIN FETCH on a OneToMany collection	Hibernate log warning: "firstResult/maxResults specified with collection fetch; applying in memory!"
Deep Offset Degradation	High setFirstResult values	Increasing query response times as page numbers increase (DB scans and discards rows).
N+1 Selects	Missing JOIN FETCH on required associations	Hundreds of small SQL queries executed for a single page load.

When to Change the Design

The OFFSET-based approach is suitable for small to medium datasets. However, you must transition to Keyset Pagination (also known as the Seek Method) when the following conditions are met:

• The dataset grows to millions of rows, making deep offsets prohibitively slow.
• The user interface supports "Infinite Scroll" rather than specific page numbers.
• The sorting column is indexed and unique (e.g., a timestamp or ID).

In Keyset Pagination, instead of OFFSET 10000, the query uses a WHERE` clause: WHERE e.id > :last_seen_id LIMIT 20. This allows the database to jump directly to the next set of rows using an index.