Implementing Server-Side Pagination with Hibernate: Avoiding Memory Exhaustion

The Problem: JVM Memory Exhaustion via Large Result Sets

When retrieving large datasets using an Object-Relational Mapper (ORM) like Hibernate, the default behavior is often to load the entire result set into the Java Virtual Machine (JVM) memory. For tables with thousands or millions of rows, this leads to OutOfMemoryError (OOME) and severe application latency. To maintain stability, the application must delegate the slicing of data to the database engine rather than filtering the collection in the application layer.

The Minimal Design: Offset-Based Pagination

The most straightforward way to implement server-side pagination in Hibernate is using setFirstResult() and setMaxResults(). These methods instruct Hibernate to append the appropriate LIMIT and OFFSET clauses (or equivalent dialect-specific syntax) to the generated SQL.

Implementation Example:

// Run this in your Service or DAO layer with appropriate Transactional permissions
public List<User> getPaginatedUsers(int pageNumber, int pageSize) {
    // Calculate the starting row (0-indexed)
    int firstResult = (pageNumber - 1) * pageSize;

    return session.createQuery("FROM User u ORDER BY u.id ASC", User.class)
                  .setFirstResult(firstResult)
                  .setMaxResults(pageSize)
                  .getResultList();
}

Critical Warning: Avoid using List.subList() on a result set returned by Hibernate. subList() performs client-side pagination, meaning the database still sends all records to the JVM, defeating the optimization entirely.

Trust and Data Boundaries

Pagination parameters are typically passed via API request queries (e.g., ?page=1&size=20). These inputs must be treated as untrusted data to prevent Denial of Service (DoS) attacks.

• Page Size Caps: Enforce a hard maximum for pageSize (e.g., 100). Without a cap, a malicious user could request size=1000000, forcing the database to allocate massive buffers and potentially crashing the application.
• Index Validation: Ensure pageNumber is greater than zero to prevent negative offset calculations.

Operational Checks and Verification

To verify that pagination is occurring at the database level and not in the JVM, you must inspect the generated SQL.

1. Enable SQL Logging: Set hibernate.show_sql=true or use a logging framework to capture org.hibernate.SQL logs.
2. Verify Keywords: Check the logs for the presence of LIMIT and OFFSET (PostgreSQL/MySQL) or OFFSET...FETCH NEXT (SQL Server).
3. Execution Plan Analysis: For high offset values, run an EXPLAIN ANALYZE on the generated query. If you see a "Full Index Scan" or "Sequential Scan" where the database reads thousands of rows only to discard them, you have encountered the "Deep Paging" problem.

Failure Modes and Stability

Offset-based pagination is susceptible to Result Set Drift. This occurs when data is inserted or deleted between two page requests.

• The Scenario: A user is on page 1. A new record is inserted at the top of the sorted list. When the user requests page 2, the last item from page 1 shifts to the first position of page 2, causing the user to see a duplicate record.
• Mitigation: Always use a stable, unique sort order (e.g., ORDER BY u.id ASC). While this doesn't prevent the drift, it ensures the sequence is deterministic.

When to Pivot the Design

Offset-based pagination performance degrades linearly as the offset increases. The database must still scan all preceding rows before returning the requested slice.

Metric	Offset-Based (Current)	Keyset/Seek Method (Pivot)
Complexity	O(N) - Slows down as page increases	O(1) - Constant time lookup
Requirement	Page number (e.g., Page 500)	Last seen ID (e.g., ID > 5000)
Use Case	Small to medium datasets	Millions of rows / Infinite scroll

If your dataset grows to a scale where deep paging causes timeouts, transition to Keyset Pagination. Instead of setFirstResult(), add a WHERE` clause to the query: WHERE u.id > :lastSeenId ORDER BY u.id ASC LIMIT :pageSize.