Does Hibernate offset pagination or ScrollableResults scale better for a 10M-row table?

Question

I am working with a Hibernate ORM 6 application backed by a relational database, and one entity table is expected to grow past 10 million rows. A reporting job must read every row once, while the UI only needs small pages of 20 records at a time.

For the UI I currently use a TypedQuery with setFirstResult() and setMaxResults(), which the dialect translates into LIMIT/OFFSET. I understand the database still has to scan and skip rows as the offset grows, so deep pages could get slow. For the batch job I am weighing ScrollableResults against paging through the table in chunks, since loading the full list would exhaust the heap and fill the persistence context.

My uncertainty is how to bound both access patterns correctly:

At what offset does keyset (seek) pagination become clearly preferable to setFirstResult(), and is that threshold measurable in a dialect-independent way?
When streaming with ScrollableResults, do I still need periodic session.clear() calls to keep memory flat, or does scrolling alone prevent persistence-context growth?
Is a unique column in the ORDER BY strictly required to guarantee stable pages across both approaches?

Tasadduq Burney · Answer

For a 10M-row table, keyset (seek) pagination scales best for UI access, while ScrollableResults with periodic session.clear() is required for full-table batch processing.The choice depends on whether your access pattern is random (UI paging) or sequential (batch reporting). At 10 million rows, standard offset pagination becomes a bottleneck because the database must scan and discard skipped rows.

1. Keyset Pagination vs. Offset
Using setFirstResult() and setMaxResults() translates to LIMIT/OFFSET. In deep pagination, the database must traverse all $N$ rows before returning the next $M$. For a 10M-row table, performance degrades linearly as the offset increases.
The Threshold: Keyset (seek) pagination becomes clearly preferable once the offset exceeds 1,000 to 5,000 rows. There is no dialect-independent constant, but the shift is measurable: when EXPLAIN ANALYZE shows a high cost for an index scan versus an index seek, you should switch. Keyset uses a WHERE id > :last_seen_id clause to jump directly to the next chunk, maintaining O(log N) performance.

2. ScrollableResults and Memory Management
For the batch job, ScrollableResults streams rows through the JDBC result set. However, it does not prevent the persistence context from growing. Every entity fetched is attached to the Hibernate Session until it is closed or cleared.
The Requirement: You must call session.clear() periodically (e.g., every 500–1000 rows) to keep the heap usage flat. Without this, the application will eventually encounter an OutOfMemoryError despite the streaming.

3. Stability and ORDER BY
Yes, a unique column in the ORDER BY clause is strictly required for both approaches. Without a unique key (like a Primary Key), the database does not guarantee the order of rows with identical values. This leads to duplicate records or missing rows when moving between pages or if data is modified during the scan.

Summary of Implementation Steps

UI (20 records): Use keyset pagination (WHERE id > ?) to avoid deep-page offset lag.
  Batch (Full read): Use ScrollableResults with a loop that calls session.clear() periodically.
  Requirement: Ensure ORDER BY includes a unique identifier to guarantee stability.

Diagnostic check: Does your table have a single unique, indexed column that can be used for seeking?

Does Hibernate offset pagination or ScrollableResults scale better for a 10M-row table?

1 answer

For a 10M-row table, keyset (seek) pagination scales best for UI access, while ScrollableResults with periodic session.clear() is required for full-table batch processing.

1. Keyset Pagination vs. Offset

2. ScrollableResults and Memory Management

3. Stability and ORDER BY

Summary of Implementation Steps

Your perspective belongs here.

0 question comments