Hibernate Pagination: When setFirstResult Helps and When It Hurts

You wire up a simple admin table with a JPA query and setMaxResults(50). Page one loads instantly. A user jumps to page 300 and the request stalls for seconds before timing out. The table is indexed, the database is healthy, and the code looks correct. The problem is not the query itself; it is the offset you asked the database to skip.

The takeaway: setFirstResult and setMaxResults are the standard way to paginate in Hibernate, but they push work onto the database that grows with the page number. Understanding when that trade-off is acceptable—and when to reach for keyset pagination instead—keeps deep result sets from becoming production incidents.

How Hibernate translates page numbers into SQL

In Hibernate, pagination is applied at the query level, not on a cached entity graph. When you call setFirstResult and setMaxResults on a Query or TypedQuery, Hibernate rewrites the underlying SQL for your configured dialect. A PostgreSQL database receives LIMIT ? OFFSET ?. SQL Server receives OFFSET ? ROWS FETCH NEXT ? ROWS ONLY. Oracle and other dialects receive their own equivalent syntax. The translation is automatic, but the semantics are the same everywhere: the database must produce the full result set, then discard the first n rows before returning the page.

These methods have been stable across Hibernate 5.x and 6.x, though the exact SQL emitted still depends on your configured dialect and database version. The offset calculation is mechanical but easy to get wrong. If your API exposes a one-based page number, the database offset is zero-based:

offset = (pageNumber - 1) * pageSize

Off-by-one errors here produce duplicate or missing records at page boundaries, so it is worth asserting the math in a unit test before it reaches production.

A worked example with verification

Suppose you have an Order entity and want the third page of ten most recent orders. The following snippet runs inside a transaction or with an open EntityManager:

int pageNumber = 3;
int pageSize = 10;
int offset = (pageNumber - 1) * pageSize;

List<Order> orders = entityManager
    .createQuery("SELECT o FROM Order o ORDER BY o.createdAt DESC", Order.class)
    .setFirstResult(offset)
    .setMaxResults(pageSize)
    .getResultList();

This asks for rows 21 through 30. If the table contains only 25 orders, you will receive five entities. To confirm that Hibernate emitted the correct dialect clause, enable SQL logging. Set the logger org.hibernate.SQL to DEBUG (or set hibernate.show_sql=true in your configuration) and inspect the generated statement. You should see a limit, offset, or fetch clause that matches your database. If the clause is missing, pagination is not being applied and the entire result set is being loaded into memory.

Risk: without an explicit ORDER BY, the database is free to return rows in any order. Two calls with the same offset can produce overlapping or missing records. Always pair pagination with a deterministic sort.

The offset tax: why deep pages get slow

Offset pagination asks the database to walk and discard rows. Even with an index on the ORDER BY column, the engine must traverse the skipped portion of the index or heap. The cost is linear: page 1 is cheap, page 100 is noticeable, and page 10,000 can be catastrophic. This is not a Hibernate limitation; it is a property of how SQL offset clauses work.

For unbounded feeds, infinite scroll, or exports, keyset pagination (also called seek pagination) avoids the offset entirely. Instead of skipping rows, you remember the last value of the ordering column from the previous page and filter on it:

SELECT o FROM Order o
WHERE o.createdAt < :lastSeenCreatedAt
ORDER BY o.createdAt DESC

Keyset pagination has a clear limitation: it does not support arbitrary "jump to page N" navigation. It requires a stable, ideally unique sort key, and it complicates filtering when the sort column is nullable. For admin dashboards with a known, bounded number of pages, offset pagination remains the simpler and correct choice.

Fetch strategy and the session boundary

Pagination limits the rows returned by the root query. It does not limit associated collections or proxies. Two practical consequences follow:

• Collection join fetch: if your HQL uses JOIN FETCH on a collection and you also paginate, the database limit applies to the joined SQL rows, not to distinct root entities. Depending on your Hibernate version, this can return fewer root entities than pageSize. Verify the actual row count returned by your query with a collection fetch before shipping it.
• Lazy loading: if you paginate inside a request and then access a lazy association after the EntityManager closes, you will trigger additional queries if the session is still open, or a LazyInitializationException if it is closed. Keep the session open exactly as long as your fetch plan requires, and prefer explicit fetch joins over lazy access in view code.

Practical checklist

1. Calculate offset as (pageNumber - 1) * pageSize and test boundary values.
2. Always include a deterministic ORDER BY.
3. Enable org.hibernate.SQL DEBUG logging and confirm the dialect clause appears.
4. Measure page latency as page number grows; if page 50 is already slow, move that use case to keyset pagination.
5. Decide your fetch strategy before paginating, not after.

Offset pagination in Hibernate is not broken; it is simply a tool with a sharp edge. Use it for bounded navigation, verify the generated SQL, and switch strategies before deep offsets turn a list view into a timeout.