Deep Pagination in Hibernate Without Killing the Database

The problem: page one is fast, page one thousand is not

You inherit a Spring Boot service that lists customer orders. The first page loads in milliseconds. By page 500, response times creep past two seconds. By page 5,000, the request times out. The database is not under heavy load, and indexes exist on the sorted columns. Something in the query plan gets worse as the offset grows.

The likely culprit is offset-based pagination. Hibernate translates setFirstResult() and setMaxResults() into database-specific LIMIT and OFFSET clauses. To return rows 10,000 through 10,020, the database must still read and discard the first 10,000 matching rows before it can return the next twenty. Cost grows roughly linearly with the offset.

The practical fix is twofold: project only the columns you need into a DTO, and replace OFFSET with a keyset cursor that resumes after the last ID seen.

Why joining collections makes it worse

A common reaction is to fetch the order's line items in the same query using JOIN FETCH. Hibernate warns that combining firstResult/maxResults with a collection fetch can force it to load all matching rows into memory and paginate there, rather than pushing the limit to the database. You trade one performance problem for an out-of-memory risk.

Worked example: DTO projection with keyset pagination

Instead of returning managed Order entities, project only the fields the UI needs into a DTO. Then replace OFFSET with a keyset cursor: remember the last ID seen, and ask for rows greater than that ID.

public record OrderSummary(
    Long id,
    String orderNumber,
    BigDecimal total,
    LocalDateTime createdAt
) {}

@Query("""
    SELECT new com.example.OrderSummary(
        o.id, o.orderNumber, o.total, o.createdAt
    )
    FROM Order o
    WHERE o.id > :lastId
    ORDER BY o.id ASC
""")
Page findAfter(@Param("lastId") Long lastId,
                             Pageable pageable);

Call it like this:

Page firstPage = orderRepository.findAfter(null, PageRequest.of(0, 20));
Long lastId = firstPage.getContent().get(firstPage.getContent().size() - 1).id();
Page nextPage = orderRepository.findAfter(lastId, PageRequest.of(0, 20));

Because the query selects only four columns and filters on an indexed ID column, the database can use an index range scan instead of scanning and discarding skipped rows. There is no managed entity overhead, so the persistence context stays small.

How to verify the generated SQL

Enable SQL logging in your application properties:

hibernate.show_sql=true
hibernate.format_sql=true

Run the application locally. Standard database read access is enough; no special operating-system permissions are required. Look for LIMIT and OFFSET in the logs. If you see OFFSET climbing into the tens of thousands, offset pagination is doing the work. If you see a WHERE o.id > ? predicate with a LIMIT, keyset pagination is active.

Risk: do not enable hibernate.show_sql=true in a high-traffic production instance without evaluating log volume. It can fill disk space and slow the application.

Trade-offs and limitations

• Keyset pagination works well for "next page" navigation, but it is awkward for "jump to page 42" because there is no stable offset to calculate.
• It requires a stable, unique, monotonic sort column. A database ID works; a timestamp with ties does not unless you add a tiebreaker.
• DTO projections return unmanaged objects. You cannot call order.getCustomer().getName() and expect lazy loading to work; the DTO must contain everything the view needs.

Practical check: run the same query against a table with at least 100,000 rows. Compare response time at offset 10 versus offset 90,000. If offset-based time grows noticeably while keyset time stays flat, the change is working.

Closing

Start with DTO projections to remove entity-management overhead. Switch to keyset pagination for deep pages where OFFSET becomes expensive. Keep offset-based pagination only when users truly need random page access, and monitor SQL logs to confirm the database—not Hibernate's memory—is doing the paging.