Hibernate Pagination: Why Offset Kills Performance and How to Fix It

The Offset Problem

You've built a REST endpoint that returns paginated results. The first page loads in 50ms. Page 100 takes 2 seconds. Page 1,000 times out. The culprit isn't your database indexes—it's how setFirstResult and setMaxResults translate to SQL.

When you call query.setFirstResult(1000).setMaxResults(20), Hibernate generates a query with OFFSET 1000 LIMIT 20 (or the dialect equivalent). The database must still scan and discard those first 1,000 rows before returning your 20. With complex joins, that discarded work multiplies.

How setMaxResults and setFirstResult Actually Work

Hibernate's pagination methods map directly to SQL's LIMIT/OFFSET (or FETCH FIRST/OFFSET in standard SQL). The JDBC driver receives the full result set up to the offset plus limit, then Hibernate materializes only the requested window. For small offsets this is fine. Beyond a few hundred rows, the database does work proportional to the offset, not the page size.

Two version-specific behaviors matter:

• Hibernate 3.x–4.x: Second-level cache configurations could silently alter row counts in paginated queries because cached entities were counted before the offset was applied.
• Hibernate 5.x–6.x: The query translator now respects the offset at the SQL level consistently, but eager joins with setMaxResults can still produce Cartesian products that inflate the row count before limiting.

Cursor-Based Pagination: The Better Alternative

Instead of counting rows to skip, use a monotonic unique identifier (typically an auto-increment ID or creation timestamp with a tiebreaker) as a cursor. The client sends the last-seen ID; the server queries for rows after that value.

Worked Example: JPQL Cursor Pagination

// Repository method
public List<Order> findOrdersAfter(Long lastOrderId, int pageSize) {
    String jpql = "select o from Order o where o.id > :cursor order by o.id asc";
    return entityManager.createQuery(jpql, Order.class)
        .setParameter("cursor", lastOrderId)
        .setMaxResults(pageSize)
        .getResultList();
}

Run this on a table with 100k rows and an index on id. The execution plan shows an index range scan starting at lastOrderId, reading exactly pageSize entries. No offset scan, no discarded rows.

Client usage: First request omits the cursor. Subsequent requests pass the id of the last item from the previous page.

GET /api/orders?size=20
GET /api/orders?size=20&cursor=10045
GET /api/orders?size=20&cursor=10065

Why This Works in Hibernate 5.x and 6.x

The WHERE id > :cursor predicate lets the optimizer seek directly to the starting point. Combined with setMaxResults, the generated SQL includes both the seek predicate and the limit clause. Hibernate's query translator preserves this structure without injecting an offset.

Trade-offs and Version Gotchas

Approach	Pros	Cons
Offset (setFirstResult)	Simple; supports random page jumps; stateless	Performance degrades superlinearly with offset; breaks with concurrent inserts/deletes
Cursor (seek predicate)	Constant-time page loads; stable under concurrent writes	No random access; requires monotonic unique column; client must track cursor

Eager join warning: If your Order entity has @OneToMany(fetch = FetchType.EAGER) on lineItems, the cursor query will still multiply rows per order before setMaxResults applies. Fix: fetch associations in a separate query or use @BatchSize with a subselect.

Native SQL trap: Calling createNativeQuery(...).setMaxResults(20) often bypasses Hibernate's dialect-specific limit handling. Some dialects (e.g., older Oracle) ignore the limit entirely. Stick to JPQL or Criteria API for portable pagination.

How to Verify Your Pagination Strategy

1. Enable SQL logging (hibernate.show_sql=true and hibernate.format_sql=true) and inspect the generated query for OFFSET vs. WHERE ... LIMIT.
2. Run EXPLAIN ANALYZE on the generated SQL with a realistic dataset (100k+ rows). Confirm the plan shows an index range scan, not a sequential scan with offset.
3. Benchmark: measure latency at offsets 0, 100, 1,000, 5,000. Offset-based queries should show superlinear growth; cursor-based should stay flat.

Actionable Closing

If your pagination uses setFirstResult beyond the first few pages, migrate to cursor-based pagination now. Start by adding a cursor request parameter to your endpoint, defaulting to 0 (or Long.MIN_VALUE) for the first page. Keep the offset implementation as a fallback for clients that genuinely need random page access, but document its performance ceiling. Your database—and your users—will notice the difference.