Efficient Large‑Dataset Pagination with Hibernate ORM

Desired outcome

Retrieve large result sets from a relational database using Hibernate without loading the entire dataset into memory and without suffering the performance collapse that occurs when OFFSET grows beyond a few thousand rows.

Prerequisites

• Hibernate 5.6 or 6.2 (behaviour of LIMIT/OFFSET translation is stable across these versions).
• A database that supports cursor‑style pagination (PostgreSQL, MySQL 8+, SQL Server, Oracle).
• An entity with a unique, monotonic column – typically the primary key (id) or a version/timestamp column that never decreases.
• Access to modify Hibernate configuration (persistence.xml, Spring application.yml, or programmatic SessionFactory build).
• Permission to enable SQL logging (usually a developer or DBA role).

Pagination strategies compared

Strategy	How it works	Typical cost	When to use
Offset‑based (HQL/Criteria setFirstResult/setMaxResults)	Appends LIMIT n OFFSET m (or vendor equivalent) to the generated SQL.	Full scan of the first m rows; cost grows linearly with offset.	Small pages (first few hundred rows) or ad‑hoc UI paging where deep links are rare.
Cursor‑based (keyset pagination)	Adds a WHERE id > :lastId ORDER BY id LIMIT n clause; no OFFSET.	Constant‑time seek using the index on the monotonic column.	Infinite scroll, API pagination, any scenario where the client only moves forward.
Fetch size hint (Query.setFetchSize(int))	Asks the JDBC driver to pre‑fetch n rows per round‑trip.	Reduces network round‑trips; may be ignored by some drivers.	Batch processing or when streaming via ScrollableResultSet.
ScrollableResultSet (Hibernate ScrollMode.FORWARD_ONLY)	Streams rows one‑by‑one without materialising the full list.	Memory O(1) for the result set; still requires a cursor on the server.	ETL jobs, report generation, any long‑running read‑only workload.

Focused procedure – implementing cursor‑based pagination

1. Define the entity with a monotonic primary key.

   @Entity
   @Table(name = "app_user")
   public class User {
       @Id @GeneratedValue(strategy = GenerationType.IDENTITY)
       private Long id;
       private String email;
       // getters / setters omitted
   }

2. Create a repository method that accepts the last seen id and page size.

   public List findNextPage(Long lastId, int pageSize) {
       String hql = "FROM User u WHERE u.id > :lastId ORDER BY u.id";
       TypedQuery q = entityManager.createQuery(hql, User.class);
       q.setParameter("lastId", lastId == null ? 0L : lastId);
       q.setMaxResults(pageSize);
       // optional: reduce round‑trips
       q.setHint(QueryHints.HINT_FETCH_SIZE, pageSize);
       return q.getResultList();
   }

3. Enable SQL logging to verify the generated statement.

   # persistence.xml or Spring Boot application.yml
   spring:
     jpa:
       properties:
         hibernate:
           show_sql: true
           format_sql: true
           use_sql_comments: true
   logging:
     level:
       org.hibernate.SQL: DEBUG
       org.hibernate.type.descriptor.sql.BasicBinder: TRACE

   Run the method with lastId = 0 and pageSize = 20. Expected log shows a statement similar to:

   SELECT u.id, u.email FROM app_user u WHERE u.id > 0 ORDER BY u.id LIMIT 20

4. Benchmark – execute two test runs in the same transaction context:
   • Offset query: entityManager.createQuery("FROM User").setFirstResult(10000).setMaxResults(10)
   • Cursor query: the method above with lastId = 10000.
   Record execution time (e.g., via System.nanoTime()) and compare. The cursor query should be an order of magnitude faster once the offset exceeds a few thousand rows.
5. Handle the end of data – when the returned list size is < pageSize, the client knows there are no more pages.

Expected checks

• SQL shape – verify that the logged query contains WHERE id > ? ORDER BY id LIMIT ? and no OFFSET clause.
• Execution plan – run EXPLAIN ANALYZE (PostgreSQL) or the vendor equivalent; the plan should show an Index Scan on the primary key with a Limit node.
• Memory profile – monitor heap during a full scroll of 1 M rows using ScrollableResultSet; heap should stay flat (only the current row object).
• Fetch size effect – increase setFetchSize(500) and observe network round‑trips with a packet capture (e.g., tcpdump -i lo port 5432). Some drivers (MySQL Connector/J) honour the hint; others (Oracle JDBC) may ignore it.

Limitations & practical verification

• Offset pagination becomes prohibitively slow when the offset exceeds ~5 000 rows on typical indexed tables; the exact threshold depends on index selectivity and buffer pool size.
• setFetchSize is a hint – an excessively large value (e.g., 10 000) can cause client‑side memory pressure or server‑side cursor overflow.
• Eager‑fetched @OneToMany collections combined with HQL pagination produce row multiplication; the returned list size may exceed setMaxResults. Use @BatchSize or fetch the collection in a separate query.
• Cursor pagination only works for forward‑only navigation. If the UI requires random page jumps, keep offset pagination for the first few pages and switch to cursor after a configurable threshold.

Recovery options

• If a cursor query starts returning duplicate rows (possible when the monotonic column is not strictly increasing, e.g., a timestamp with millisecond granularity), add a secondary tie‑breaker: WHERE (created_at, id) > (:lastCreated, :lastId).
• When the database does not support LIMIT (legacy Oracle 11g), fall back to ROWNUM or OFFSET ... ROWS FETCH NEXT ... ROWS ONLY via a native query.
• If ScrollableResultSet throws SQLException: Result set is closed, ensure the underlying transaction remains open for the whole scroll; consider using a read‑only transaction with @Transactional(readOnly = true).

Quick verification checklist

1. Enable hibernate.show_sql=true and confirm the generated SQL matches the intended pagination clause.
2. Run EXPLAIN on both offset and cursor queries; verify index usage and absence of full table scans.
3. Measure latency for page 1, page 100, page 10 000 using both strategies; cursor latency should stay flat.
4. Profile heap while scrolling 500 k rows with ScrollableResultSet; ensure no OOM.

Summary

For large‑dataset retrieval, replace offset‑based pagination with a keyset (cursor) approach that leverages a monotonic primary key. Combine it with a modest setFetchSize hint and, when streaming is required, use ScrollableResultSet to keep memory constant. Validate the generated SQL and execution plans in your specific Hibernate version and database, and keep offset pagination only for shallow UI pages.