Hibernate Pagination: setFirstResult/setMaxResults, the Fetch-Join Trap, and When to Switch to Keyset

Your endpoint returns a page of orders, and the code looks fine: setFirstResult(0), setMaxResults(20). Then someone adds a join fetch on the order lines to fix an N+1 problem, page sizes quietly balloon, and memory usage climbs. The pagination didn't break — it moved from the database into the JVM, and the only evidence is a single log line most teams never see.

The takeaway up front: Hibernate pagination is database-side only when the query shape allows it. Paginate root entities, fetch collections separately, and consider keyset pagination once offsets get deep.

What setFirstResult and setMaxResults actually do

Both methods exist on Hibernate's Query and on the JPA Query/TypedQuery interfaces, so the same code paginates identically whichever API you use:

List<Order> page = session.createQuery(
        "select o from Order o order by o.id", Order.class)
    .setFirstResult(40)   // offset: skip 40 rows
    .setMaxResults(20)    // page size
    .getResultList();

The Hibernate dialect translates this into the target database's pagination syntax: LIMIT ? OFFSET ? on PostgreSQL and MySQL, OFFSET ... FETCH NEXT ... on dialects supporting the SQL standard, and window-function or TOP-based rewriting on older SQL Server versions. The exact SQL varies by dialect and Hibernate version (this reflects 5.x/6.x behavior), so don't hard-code assumptions — verify with SQL logging.

To check what's really happening, enable hibernate.show_sql=true (or set the org.hibernate.SQL logger to DEBUG) in a dev environment and confirm the generated statement contains the LIMIT/OFFSET clause rather than a full select. That one check catches most pagination misconfigurations.

The fetch-join trap: silent in-memory pagination

The hazard appears when you combine pagination with a collection fetch join:

// Dangerous: pagination + collection fetch join
List<Order> page = session.createQuery(
        "select o from Order o join fetch o.lines order by o.id",
        Order.class)
    .setFirstResult(40)
    .setMaxResults(20)
    .getResultList();

A fetch join on a @OneToMany multiplies each parent row by its children, so row 41 of the result is no longer "order 41" — SQL-level offset would slice through the middle of a parent's rows. Hibernate knows this, refuses to paginate in SQL, and instead fetches all matching rows and paginates in memory. It logs a warning along the lines of firstResult/maxResults specified with collection fetch; applying in memory. If your logging for org.hibernate is suppressed in production, you'll never see it — you'll just see heap pressure on popular pages.

The standard workaround is a two-step query, keeping pagination in the database:

// Step 1: paginate IDs only — safe for SQL-level LIMIT/OFFSET
List<Long> ids = session.createQuery(
        "select o.id from Order o order by o.id", Long.class)
    .setFirstResult(40)
    .setMaxResults(20)
    .getResultList();

// Step 2: fetch those entities with their collections
List<Order> page = session.createQuery(
        "select distinct o from Order o join fetch o.lines "
        + "where o.id in :ids order by o.id", Order.class)
    .setParameter("ids", ids)
    .getResultList();

One requirement: the sort must be deterministic. If you order by a non-unique column like createdAt, add a unique tiebreaker (order by o.createdAt, o.id), or pages can overlap or skip rows between the two queries.

Deep offsets and the keyset alternative

Even without fetch joins, offset pagination has a database-level cost: OFFSET 500000 still scans and discards half a million rows. Page depth makes it linearly slower, and that's a property of the database, not a Hibernate bug.

Keyset (seek) pagination avoids this by filtering on the sort column instead of skipping rows:

List<Order> page = session.createQuery(
        "select o from Order o where o.id > :lastSeenId "
        + "order by o.id", Order.class)
    .setParameter("lastSeenId", lastSeenIdFromPreviousPage)
    .setMaxResults(20)
    .getResultList();

With an index on the sort column, page 1 and page 25,000 cost roughly the same. The trade-off: you lose random page access ("jump to page 37") because each page depends on the previous page's last value. For infinite-scroll UIs and API cursors that's fine; for classic numbered page grids, offset is simpler.

Either way, remember that Hibernate never computes the total count for you. If the UI needs "page 3 of 42", pair the paginated query with a separate select count(o) from Order o ... using the same filters.

How to verify your pagination actually works

Don't trust the code shape — test the behavior:

• Integration test with known data: insert 25 rows, page size 10, and assert pages 1–3 contain the expected IDs and page 4 is empty.
• SQL logging: confirm the generated statement includes LIMIT/OFFSET or the dialect equivalent.
• Warning check: run any paginated fetch-join query in a test and watch for the "applying in memory" warning — if you have such a query in production, that log line is your smoking gun.
• Offset cost: on a populated test table, compare execution time at offset 0 versus a deep offset to see whether keyset pagination is worth the added complexity.

Pagination in Hibernate is a one-line API with two sharp edges: fetch joins silently move it into memory, and deep offsets silently degrade. Paginate IDs or plain root entities, fetch collections in a second query, add a deterministic sort, and switch to keyset when pages get deep. Then prove it with SQL logging and a small integration test — five minutes of verification beats a production memory incident.