Solving the Hibernate Pagination Performance Trap

The Hidden Cost of Offset Pagination

When building data-heavy applications, the instinct is to use setFirstResult() and setMaxResults() to implement pagination. While these methods are standard in Hibernate, they often introduce a performance cliff. As a user navigates to page 100 or 1,000, the application slows down significantly, even if the page size remains small.

The problem is that offset-based pagination (the default behavior of these methods) requires the database to scan and discard all preceding rows before returning the requested window. If you request an offset of 10,000, the database must still read those 10,000 rows from the disk, only to throw them away.

How Hibernate Translates Pagination

Hibernate abstracts the pagination logic through its Dialect system. Depending on your database, setFirstResult(int) and setMaxResults(int) are translated into different SQL patterns:

• PostgreSQL/MySQL: Translated to LIMIT and OFFSET.
• Oracle: Translated to OFFSET-FETCH (in newer versions) or complex ROWNUM subqueries (in older versions).

Because this happens at the database level, it is vastly more efficient than fetching all records into the Java Virtual Machine (JVM) and filtering them in a list. However, the linear degradation of the offset remains a physical limitation of the database engine.

The "In-Memory" Warning Danger

A common engineering mistake occurs when combining pagination with JOIN FETCH on a One-to-Many relationship. If you attempt to paginate a query that fetches a collection, Hibernate cannot accurately determine the number of root entities because the join duplicates the root rows in the result set.

When this happens, Hibernate logs a critical warning: HHH000104: firstResult/maxResults specified with collection fetch; applying in memory!

This is a performance disaster. Hibernate will fetch every single record matching the query from the database into the application memory and then perform the pagination in Java. For large tables, this leads to OutOfMemoryError crashes.

Worked Example: Offset vs. Keyset Pagination

To avoid the performance decay of high offsets, use Keyset Pagination (also known as the Seek Method). Instead of telling the database how many rows to skip, you tell it exactly where the last page ended based on a unique, indexed column (usually the Primary Key).

Inefficient Offset Approach

// Run on Application Server | Permissions: Standard App User
// This becomes slower as 'firstResult' increases
Query query = session.createQuery("FROM Order o ORDER BY o.id ASC");
query.setFirstResult(10000); // The database scans 10k rows first
query.setMaxResults(20);
List results = query.list();

Efficient Keyset Approach

// Run on Application Server | Permissions: Standard App User
// This remains constant speed regardless of depth
long lastSeenId = 10000; // The ID of the last item on the previous page
Query query = session.createQuery("FROM Order o WHERE o.id > :lastId ORDER BY o.id ASC");
query.setParameter("lastId", lastSeenId);
query.setMaxResults(20);
List results = query.list();

Trade-offs and Limitations

Feature	Offset Pagination	Keyset Pagination
Random Access	Easy (Jump to Page 5)	Impossible (Next/Prev only)
Performance	Degrades linearly	Constant time (O(log N))
Data Stability	Items shift if rows are deleted	Consistent anchor point

Verification and Diagnostics

To verify if your pagination is behaving correctly, enable SQL logging in your application.properties or hibernate.cfg.xml:

hibernate.show_sql=true

Check your console logs for the following:

1. SQL Clause: Ensure you see LIMIT or OFFSET in the generated SQL. If you don't, but you used setFirstResult, Hibernate may be paginating in memory.
2. Log Warnings: Search for HHH000104. If this appears, you must remove the JOIN FETCH from your paginated query and instead use a separate query to fetch collections or use @BatchSize.
3. Execution Time: Run a query with an offset of 0, then 10,000, then 100,000. If the execution time increases linearly, you are using offset pagination and should consider the Keyset method for deep paging.