Hibernate Query Pagination: Offset Limits, Deep-Page Costs, and Keyset Alternatives

The hidden cost of deep offset pages

Offset pagination feels straightforward: ask for page 1, then page 2, and so on. In Hibernate ORM, you express this with setFirstResult(offset) and setMaxResults(limit) on a Query or TypedQuery. The framework pushes these values into the database's LIMIT/OFFSET clause (or the dialect equivalent), so your application heap stays bounded. The problem is that the database still has to produce every row before the offset. As page depth grows, latency typically grows with it, even though Hibernate only materializes the requested slice.

Prerequisites and dialect assumptions

Before writing pagination code, confirm three things:

• A mapped entity with a persistent identifier (usually annotated with @Id).
• A configured SessionFactory or EntityManagerFactory that can create queries.
• A Hibernate dialect that supports row-limiting syntax. Most mainstream databases (PostgreSQL, MySQL, SQL Server, Oracle, H2) have built-in dialects that translate setMaxResults into the correct SQL. Niche or older databases may not; check the dialect documentation for your specific database version.

Implementing offset pagination

Build a JPQL or HQL query, apply predicates, then chain the pagination methods before execution. The following example uses a CustomerOrder entity inside a repository or DAO class:

TypedQuery<CustomerOrder> query = entityManager.createQuery(
    "SELECT o FROM CustomerOrder o WHERE o.status = :status " +
    "ORDER BY o.createdAt DESC, o.id DESC",
    CustomerOrder.class
);
query.setParameter("status", OrderStatus.SHIPPED);
query.setFirstResult(pageNumber * pageSize);  // offset
query.setMaxResults(pageSize);                 // limit
List<CustomerOrder> orders = query.getResultList();

Run this code inside a transaction or with an open EntityManager if you plan to access lazy associations after the query returns. The pageNumber and pageSize values are placeholders supplied by your calling code.

Why the ORDER BY needs a unique tie-breaker

If createdAt is not unique, rows with identical timestamps can swap positions between queries. Adding o.id as a secondary sort column makes the order deterministic. Without a unique tie-breaker, concurrent inserts or deletes can produce duplicate or missing rows across page requests.

The join fetch trap

Combining setMaxResults with join fetch requires care. Hibernate applies the limit to the SQL result set, but a join fetch can return multiple rows per root entity. If Hibernate limits the raw SQL rows before deduplicating in memory, you may receive fewer distinct entities than pageSize. Validate the returned collection size in tests, and consider fetching associations in a second query if the page size is inconsistent.

Keyset pagination for large, stable datasets

When a table grows large and page depth matters, replace the numeric offset with a keyset (also called seek) predicate. Order by a unique, indexed column and filter rows relative to the last value seen on the previous page:

TypedQuery<CustomerOrder> query = entityManager.createQuery(
    "SELECT o FROM CustomerOrder o WHERE o.status = :status " +
    "AND o.id < :lastSeenId ORDER BY o.id DESC",
    CustomerOrder.class
);
query.setParameter("status", OrderStatus.SHIPPED);
query.setParameter("lastSeenId", lastSeenId);
query.setMaxResults(pageSize);
List<CustomerOrder> orders = query.getResultList();

Because the database can seek directly to the index position for :lastSeenId, it does not generate and discard preceding rows. This keeps latency stable as users page deeper. The trade-off is that keyset pagination does not support arbitrary page jumps; you can only move forward or backward from a known cursor value.

Verifying the generated SQL and database plan

Enable SQL logging to confirm Hibernate emits the correct row-limiting clause. In persistence.xml or application.properties, set:

<property name="hibernate.show_sql" value="true"/>
<property name="hibernate.format_sql" value="true"/>

Run your application and inspect the log. The executed statement should contain LIMIT/OFFSET (or the equivalent for your database) with the values you passed to setFirstResult and setMaxResults.

Next, copy the logged SQL and run EXPLAIN in your database client (for example, psql, mysql, or SQL*Plus). You need read access to the table and its statistics. Compare the plan for a shallow offset and a deep offset:

-- Run in your database client
EXPLAIN SELECT * FROM customer_order
WHERE status = 'SHIPPED'
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 10000;

If the plan shows an index scan or index seek that stops after the limit, the query is efficient. If it shows a scan-and-discard pattern that grows with the offset value, deep paging will degrade.

Integration checks

Write an integration test that fetches the first page and a later page, then asserts the returned entity identifiers match the expected ordered slice. For example, assert that the first page contains IDs [100, 99, 98] and the second page contains [97, 96, 95] when ordered by id DESC. Also assert the collection size equals pageSize (or the remaining row count on the final page). This catches non-deterministic ordering and join fetch deduplication bugs early.

Recovery options when deep paging is slow

• Switch to keyset pagination if the use case allows sequential traversal.
• Add composite indexes on the filter and sort columns. For the offset example, an index on (status, created_at, id) can help the database avoid a full scan.
• Use scroll() or stream() for batch processing that does not need random page access. These methods use a database cursor and avoid offset generation entirely.

Limitations to plan for

Offset pagination is not stable under concurrent writes. If rows are inserted or deleted between page requests, the offset window shifts and users may see duplicates or miss records. Keyset pagination avoids this for append-mostly data but requires a stable sort key. Dialect support for LIMIT/OFFSET is broad but not universal; if your database lacks it, Hibernate may fall back to scrollable results or require a custom dialect. Always verify the generated SQL for your specific Hibernate and database version rather than assuming behavior.