Hibernate Pagination: Why setFirstResult and setMaxResults Aren't Always What You Expect

The Pagination Trap

You add setFirstResult(0) and setMaxResults(20) to your JPQL query, deploy to production, and everything works—until the dataset grows. Suddenly the same query that returned in 50ms takes 5 seconds. The database execution plan shows a full table scan despite the LIMIT 20 OFFSET 0 clause you verified in the logs.

The issue isn't the API. It's that setFirstResult and setMaxResults translate to OFFSET/LIMIT (or vendor-specific equivalents), and OFFSET performance degrades linearly with the offset value. Page 1 is fast. Page 10,000 forces the database to read and discard 10,000 rows before returning yours.

How the API Actually Works

On Query or TypedQuery, the two methods are zero-indexed:

• setFirstResult(int startPosition) — rows to skip (0 = first row)
• setMaxResults(int maxResult) — maximum rows to return

Combined, they produce a window: setFirstResult(20).setMaxResults(10) returns rows 21–30. The JPA provider rewrites the SQL accordingly—LIMIT 10 OFFSET 20 on PostgreSQL/MySQL, OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY on SQL Server 2012+, ROWNUM filtering on older Oracle.

But the translation isn't free. Some providers (Hibernate included) may still materialize the full result set in memory before applying the window if the query structure prevents pushdown—especially with complex joins, DISTINCT, or certain fetch strategies.

Worked Example: Paginating an Order History

Suppose you're building an order history endpoint. The naive implementation:

@Repository
public class OrderRepository {
    @PersistenceContext
    private EntityManager em;

    public List<Order> findRecentOrders(int page, int size) {
        TypedQuery<Order> query = em.createQuery(
            "SELECT o FROM Order o WHERE o.customerId = :cid ORDER BY o.createdAt DESC",
            Order.class
        );
        query.setParameter("cid", currentCustomerId());
        query.setFirstResult(page * size);
        query.setMaxResults(size);
        return query.getResultList();
    }
}

This works for page 0. On page 500 with size=20, the database executes:

SELECT * FROM orders 
WHERE customer_id = ? 
ORDER BY created_at DESC 
LIMIT 20 OFFSET 10000;

PostgreSQL must scan the index for that customer, walk 10,000 entries, then return 20. The cost grows with OFFSET, not LIMIT.

Better Approach: Keyset (Cursor) Pagination

Replace offset with a predicate on the ordering column:

public List<Order> findOrdersAfter(Instant cursor, int size) {
    TypedQuery<Order> query = em.createQuery(
        "SELECT o FROM Order o " +
        "WHERE o.customerId = :cid AND o.createdAt < :cursor " +
        "ORDER BY o.createdAt DESC",
        Order.class
    );
    query.setParameter("cid", currentCustomerId());
    query.setParameter("cursor", cursor);
    query.setMaxResults(size);
    return query.getResultList();
}

The client sends the createdAt of the last item seen (or a composite cursor with tie-breakers). The query becomes:

SELECT * FROM orders 
WHERE customer_id = ? AND created_at < ? 
ORDER BY created_at DESC 
LIMIT 20;

No OFFSET. The index seek starts exactly at the cursor. Page 500 is as fast as page 1.

Trade-offs and Limitations

• No random access — users can't jump to page 42 directly. UI must use "load more" or infinite scroll.
• Cursor stability — if createdAt isn't unique, add a tie-breaker (AND (createdAt, id) < (:cursor, :lastId)) to avoid missing or duplicate rows.
• Native queries — setMaxResults on a native SQL query (createNativeQuery) may be ignored by some JPA providers. Test your specific version.
• Large setMaxResults — values over ~1000 risk memory pressure in the persistence context. Batch processing should use scrollable results or stream APIs instead.

Verify Before You Trust

Run two checks in your environment:

1. SQL logging — enable hibernate.show_sql=true and hibernate.format_sql=true. Confirm LIMIT/OFFSET (or vendor equivalent) appears and that no extra columns are fetched.
2. EXPLAIN ANALYZE — run the generated query with a realistic offset (e.g., 50,000). Look for "Limit" node cost and whether the planner uses an index scan or sequential scan.

If the plan shows a full scan at high offsets, switch to keyset pagination for that query. The API change is small; the performance difference at scale is not.