Handling Large Dataset Retrieval with Hibernate Limit and Offset

The Problem: Memory Exhaustion and Query Latency

Retrieving thousands of entities from a database into a Java application often leads to OutOfMemoryError (OOME) because the Hibernate Session attempts to manage all retrieved objects in its persistence context. To prevent this, developers must implement pagination to fetch only a small subset of data at a time.

The primary takeaway is that while setFirstResult() and setMaxResults() provide a simple API for pagination, they introduce a performance cliff known as "deep paging" where the database must scan and discard thousands of rows before returning the requested page.

The Minimal Design: Offset-Based Pagination

The smallest suitable design for pagination in Hibernate uses the Query interface. This approach delegates the slicing of the result set to the database engine via the LIMIT and OFFSET clauses (or equivalent syntax depending on the SQL dialect).

Implementation Example

Assuming a Spring Data JPA environment or standard Hibernate 5.x/6.x, the implementation follows this pattern:

// Run this within a @Transactional service method
// Required permissions: Read access to the target entity table
public List<User> fetchUserPage(int pageNumber, int pageSize) {
    int offset = pageNumber * pageSize;
    
    return session.createQuery("FROM User u ORDER BY u.id ASC", User.class)
                  .setFirstResult(offset) // The starting position (OFFSET)
                  .setMaxResults(pageSize) // The number of records to fetch (LIMIT)
                  .getResultList();
}

Expected Check: To verify the behavior, set hibernate.show_sql=true in your configuration. You should see a query similar to SELECT ... FROM users ORDER BY id ASC LIMIT 20 OFFSET 100.

Trust and Data Boundaries

The ORM layer acts as the boundary between the application's logical page request and the database's physical row retrieval. The application trusts the database to handle the sorting and slicing. However, a critical boundary risk exists when using Join Fetches.

If you use JOIN FETCH to load collections (one-to-many) while applying setFirstResult(), Hibernate cannot safely paginate in SQL because the join creates duplicate root entities in the result set. In these cases, Hibernate may perform in-memory pagination. It fetches every single row from the database and filters them in the JVM, which bypasses the memory protection pagination was intended to provide and can crash the application.

Operational Checks and Failure Modes

Offset-based pagination is not a constant-time operation. Its performance degrades linearly as the offset increases.

Performance Degradation (Deep Paging)

When a user requests page 1,000 with a page size of 20, the database must still scan 20,000 rows, discard the first 19,980, and return the last 20. This results in increased I/O and CPU usage.

Data Drifting

Because offset pagination relies on the position of rows, the result set can "drift" if data is inserted or deleted between requests. If a row is deleted from page 1 while a user is moving to page 2, the first item of page 2 shifts to page 1, and the user sees a duplicate entry on page 2.

Diagnostic Decision Matrix

Symptom	Likely Cause	Verification Method
Slow response on high page numbers	Deep Paging / Sequential Scan	Check DB Execution Plan for "Index Scan" vs "Seq Scan"
OOME despite using setMaxResults()	In-memory pagination via Join Fetch	Check logs for "firstResult/maxResults specified with collection fetch; applying in memory!"
Duplicate items across pages	Data Drifting	Insert a record into the first page and refresh the second page

Conditions for Design Change

You should move away from setFirstResult() and setMaxResults() when any of the following conditions are met:

• Dataset Scale: The table grows to millions of rows where users frequently access deep pages.
• Strict Consistency: The application cannot tolerate duplicate or skipped items during pagination.
• Performance SLAs: Query latency must remain constant regardless of the page depth.

The alternative is Keyset Pagination (The Seek Method). Instead of an offset, the query filters by the last seen ID: WHERE u.id > :lastSeenId ORDER BY u.id ASC LIMIT 20. This allows the database to jump directly to the record using an index, maintaining constant performance.

Rollback and Recovery

Since pagination is a read-only operation, there is no state change to roll back. However, if a deep-paging query causes a database CPU spike, the immediate recovery action is to kill the long-running PID (Process ID) on the database server to restore service to other users.