Choosing Hibernate Pagination for Large Result Sets: Offset vs Cursor – A Practical Architecture Note

The problem: paging through millions of rows without stalling

When a Hibernate query can return millions of rows, naive pagination becomes a bottleneck. The two mainstream strategies — offset-based paging via setFirstResult/setMaxResults and cursor-based paging keyed on a stable identifier — behave very differently at depth. This note lays out the requirements, the smallest design that satisfies them, where the trust and data boundaries sit, how to verify the behavior operationally, and the failure modes that should push you from one design to the other.

Requirements

• Return bounded pages (for example 10–100 rows) from tables that may hold millions of rows.
• Keep query latency roughly flat as users page deeper, or at least document where it degrades.
• Tolerate concurrent inserts and deletes without silently skipping or duplicating rows where that matters.
• Avoid materializing the full result set in memory before slicing it.

The smallest suitable design

Offset pagination: the default for shallow paging

Offset pagination is the smallest design when users only browse the first few pages. Hibernate exposes it directly on both HQL and Criteria queries:

// Run in your service/repository layer; needs a managed Session or EntityManager
List<Order> page = session.createQuery(
        "from Order o order by o.id", Order.class)
    .setFirstResult(10_000)   // rows to skip
    .setMaxResults(10)        // page size
    .getResultList();

Hibernate translates this into the dialect's limit/offset SQL (for example LIMIT 10 OFFSET 10000). The catch: the database still scans and discards the preceding 10,000 rows, so cost grows roughly linearly with the offset. For page 1–50 this is usually invisible; for page 5,000 it is not.

Cursor pagination: the smallest design for deep or unbounded paging

Cursor (keyset) pagination adds a WHERE predicate on a stable, unique, indexed column — typically the primary key — instead of skipping rows:

// lastSeenId comes from the last row of the previous page
List<Order> page = session.createQuery(
        "from Order o where o.id > :cursor order by o.id", Order.class)
    .setParameter("cursor", lastSeenId)
    .setMaxResults(10)
    .getResultList();

With an index on the cursor column, each page costs about the same regardless of depth. The trade-offs: no random page jumps (you cannot go straight to page 5,000), and the cursor column must be unique and monotonic with your sort order. If you sort by a non-unique column such as createdAt, add the primary key as a tiebreaker and build a composite cursor.

Trust and data boundaries

Treat the cursor value and page size as untrusted client input. Validate that the cursor is a well-formed identifier for the entity being paged, and cap maxResults server-side — an unbounded page size is an invitation to an OutOfMemoryError if the driver materializes the full result set before limits apply. Never let a client-supplied cursor flow into HQL via string concatenation; always bind it as a parameter.

The second boundary is consistency between calls. Offset pagination assumes a stable row ordering: rows inserted or deleted between page requests shift the offset, so a client can see duplicates or miss rows. Cursor pagination is resilient to inserts before the cursor but still reflects deletes. If clients need a truly stable snapshot, that requires a repeatable-read transaction or a materialized snapshot — a different, heavier design.

Fetch plans and fetch size: two quiet foot-guns

Mixing collection fetch joins with pagination is a classic Hibernate trap. A query like from Order o join fetch o.lines with setMaxResults cannot be limited in SQL, because one root row spans multiple joined rows. Hibernate falls back to in-memory pagination — fetching everything, then slicing — which defeats the purpose and can exhaust memory. Page on the root entity IDs first, then fetch associations for that page in a second query.

Separately, hibernate.jdbc.fetch_size hints at how many rows the JDBC driver buffers per round trip. Drivers interpret this differently and some impose their own limits or ignore the hint, so treat it as a tuning knob to verify against your actual driver, not a guarantee.

Operational checks

1. Verify SQL shape. Enable SQL logging (hibernate.show_sql or a logging category for org.hibernate.SQL) in a staging environment and confirm the offset query emits limit/offset SQL, and the cursor query emits WHERE id > ? with no offset.
2. Verify page integrity. With setFirstResult(10000)/setMaxResults(10), assert the returned list size is exactly 10 and that consecutive pages do not overlap.
3. Verify deep-page cost. Compare execution time of page 1 versus page 1,000 for both strategies on production-like data volume. Flat timing confirms the cursor path is using the index; check the query plan (EXPLAIN) for an index range scan rather than a full scan.
4. Verify no in-memory pagination. Watch for Hibernate warnings about firstResult/maxResults with collection fetch, and confirm the generated SQL actually contains the limit clause when fetch joins are absent.

Failure modes

• Offset degradation: latency climbs with page depth; databases may also sort large intermediate results if the ordering lacks a supporting index.
• Skipped/duplicated rows: concurrent writes between offset page calls shift positions.
• Memory exhaustion: collection fetch joins or driver behavior materializing the whole result set before limits apply.
• Driver fetch-size truncation: a driver-imposed limit can silently cap rows per fetch, producing short pages or extra round trips; verify against your specific driver.
• Lazy-loading surprises: paging entities with uninitialized associations can trigger N+1 queries or missing-entity errors when rows were deleted between page fetches.

What would change the design

Stay on offset pagination while paging stays shallow and users need numbered pages. Switch to cursor pagination when deep paging is routine, when the dataset churns enough that offset drift corrupts results, or when profiling shows offset queries dominating database time. Move to a snapshot or search-index design (for example an external search engine) when clients need stable paginated views over rapidly changing data or arbitrary sort dimensions — at that point neither relational strategy is the right tool. Behavior described here reflects long-standing Hibernate 5/6 semantics; confirm dialect-specific limit/offset SQL against your version before rollout.