Hibernate Pagination: Why setMaxResults Alone Is Not Enough for Deep Pages

When a user clicks "last page" on a large results table and the request stalls, the instinct is to blame Hibernate for loading too many objects into the JVM. Often that is not what is happening. The real issue is that offset-based pagination asks the database to walk through every row it is about to skip, and if the sort order is not stable, the rows that finally arrive can be different from the ones the user saw a moment ago.

Hibernate's Query interface provides setFirstResult() and setMaxResults() to move pagination into the database. They are the right default for bounded result sets, but they carry a hidden cost that grows with the offset, and they are only reliable when paired with a deterministic ORDER BY.

How Hibernate pushes limits into SQL

setFirstResult(int) tells the database how many rows to skip. setMaxResults(int) caps the number of rows returned. Because these calls live on the Query interface, the same HQL (Hibernate Query Language) or Criteria code works regardless of the relational database underneath. Hibernate translates the calls into the dialect the database expects: PostgreSQL receives LIMIT and OFFSET, while Oracle receives FETCH FIRST and OFFSET clauses. The JVM never materializes the full table; only the requested page crosses the wire. The methods are available on Hibernate's native Query interface and its JPA counterpart across recent versions, though exact SQL rendering depends on the configured dialect and database version.

A worked example and the offset tax

Suppose you need the third page of users, ten rows at a time. Using HQL, the code looks like this:

// Run inside a transaction with an open org.hibernate.Session
Query<User> query = session.createQuery("from User order by id", User.class);
query.setFirstResult(20);   // OFFSET: skip the first 20 rows
query.setMaxResults(10);    // LIMIT: return at most 10 rows
List<User> page = query.list();

On PostgreSQL, the generated SQL typically resembles SELECT ... FROM users ORDER BY id LIMIT 10 OFFSET 20. On Oracle, Hibernate renders an equivalent FETCH FIRST 10 ROWS ONLY statement with an offset clause. You can confirm the exact translation by enabling SQL logging in your persistence configuration and inspecting the statements before they execute.

Even though only ten rows return, the database may still scan the twenty skipped rows to reach the offset. As the offset grows, that scanning work grows with it. This is a structural property of offset-based pagination, not a Hibernate defect: the database must still account for the rows it will not return. On high-volume tables, deep offsets can introduce significant query latency.

Sort stability and verification

The second risk is consistency. Without an ORDER BY clause, setMaxResults() returns an arbitrary subset of rows. If a record is inserted or deleted between two page requests, the same offset can return overlapping or missing rows. A stable sort key—ideally a unique column such as a primary key—makes page boundaries deterministic. If your application allows users to jump directly to high page numbers, offset pagination may be the wrong tool, and a keyset or seek-based strategy that filters on the last seen value will scale better.

Three practical checks confirm that pagination is working as intended. First, execute a query with setFirstResult(10) and setMaxResults(10) and inspect the SQL logs to verify the dialect-specific limit clause. Second, compare the heap footprint of a paginated query against a full list() retrieval using a Java profiler; the paginated call should hold far fewer managed entities. Third, insert a record into the table between two consecutive page requests. If the sort order is stable, the pages should not shift; if it is not, you will see duplication or gaps.

Trade-offs and an actionable rule

The trade-off is simplicity versus scale. setFirstResult() and setMaxResults() are portable, concise, and easy to reason about. They work well for administrative screens, reports, and any interface where users browse the first few dozen pages. They work poorly for deep navigation on large tables. Use them to bound every list query, always include a deterministic ORDER BY, and measure the latency at your deepest expected offset before shipping the feature.