Hibernate Pagination Architecture: Bounded Retrieval with setFirstResult and setMaxResults

The problem: unbounded result sets exhaust heap

When a Hibernate query returns every row of a large table, the persistence context—the first-level cache that tracks managed entity instances—and the JVM heap absorb the entire dataset. For tables with millions of rows, this causes OutOfMemoryError or forces the garbage collector into sustained thrashing. The useful takeaway is that setFirstResult and setMaxResults let you bound the working set, but only when the query has a deterministic ORDER BY and the offset arithmetic is correct.

Requirements

• Bounded memory: No query should materialize more rows than the caller can process in one step.
• Deterministic traversal: Successive pages must not duplicate or skip rows when the underlying data is stable.
• Predictable latency: Page size and offset must be chosen so that a single database round-trip stays within an acceptable response window.
• Verifiable progress: The caller needs a cheap way to confirm that the total row count and the retrieved pages agree.

The smallest suitable design

Start with a single HQL or JPQL query that selects the entity, applies an ORDER BY on a unique column (usually the primary key), and sets a fixed page size. The caller computes a zero-based offset: page 1 is offset 0, page 2 is offset pageSize, and so on.

In Hibernate 6.x, the org.hibernate.query.Query interface exposes setFirstResult(int) and setMaxResults(int). The following pattern is the minimal viable pagination loop:

// Run inside a read-only transaction
Session session = sessionFactory.openSession();
Transaction tx = session.beginTransaction();

String hql = "FROM Product p ORDER BY p.id";
Query<Product> query = session.createQuery(hql, Product.class);

int pageIndex = 0;      // zero-based
int pageSize = 500;     // bounded working set

query.setFirstResult(pageIndex * pageSize);
query.setMaxResults(pageSize);

List<Product> page = query.list();

// Expected check: page.size() <= pageSize
tx.commit();
session.close();

The offset is zero-based. Calling setFirstResult(1) skips the first row and returns the second row as the first element. If your API exposes one-based page numbers to clients, translate them before calling the query: offset = (userPageNumber - 1) * pageSize.

Keep the page size bounded. While setMaxResults prevents unbounded heap growth, very large values (for example, greater than 1,000 rows per fetch) can increase database cursor overhead and network transfer time without improving throughput. A page size between 100 and 1,000 is a common starting point, but the right value depends on row width and entity graph complexity.

Trust and data boundaries

Ordering is a contract, not a default. SQL result sets are unordered unless an ORDER BY clause is present. Without it, the database may return rows in a different sequence on each execution, especially after vacuuming, index changes, or parallel scans. The application must not trust the database to preserve an implicit order across pagination rounds.

The offset is caller-owned arithmetic. The persistence layer trusts the integer passed to setFirstResult. An off-by-one error in the caller—confusing a zero-based offset with a one-based page number—silently shifts every page by one row. Validate the offset at the API boundary: reject negative values and cap the maximum offset if your use case allows it.

Count queries are approximate under concurrency. A separate SELECT COUNT(*) query tells you the total number of rows at the moment it executes. If writes occur between the count and the page fetch, the total may be stale. Use the count for progress reporting and sanity checks, not as a transactional guarantee.

Operational checks

Before shipping a paginated endpoint, run these checks in a non-production environment that mirrors production data volume.

Check	How to run it	Expected result
First-page sanity	Execute the query with setFirstResult(0) and setMaxResults(1).	Exactly one row is returned, and it matches the first row of a full ordered scan.
Page continuity	Iterate pages 0 through N-1 using the computed offset. Collect all IDs.	The union of IDs has no duplicates and no gaps relative to a known COUNT query.
SQL inspection	Enable Hibernate SQL logging (hibernate.show_sql=true) or use a datasource proxy.	The generated SQL contains LIMIT and OFFSET (or database-equivalent) clauses, not a full table scan.
Memory profile	Run the loop under a profiler with a page size equal to the production value.	Heap usage remains flat across pages; the persistence context does not grow without bound.

These checks require only read permissions on the target schema. Run them inside a transaction marked read-only if your JPA provider supports it, so the database can optimize accordingly.

Failure modes

• Missing or non-unique ORDER BY: If the sort column contains duplicates, rows with the same value may appear on multiple pages or vanish entirely. Add the primary key as a tiebreaker: ORDER BY createdAt, id.
• Off-by-one offset: A one-based page number passed directly as the offset drops the first row of every page. The symptom is a missing record at each page boundary.
• Deep offset degradation: As the offset grows, the database must still count and discard skipped rows. On very large tables, page 10,000 can be slower than page 1, even with a small page size.
• Redundant round-trips: Re-fetching the same page without an application-level cache multiplies database load. If users navigate backward frequently, cache recently retrieved pages by their offset key.
• Oversized pages: A page size that is too large negates the memory benefit and can cause the database to spill cursor state to disk.

Conditions that would change the design

Offset pagination is the right default for bounded administrative screens, batch processors, and reports where users jump to arbitrary pages. Change the approach when:

• Deep pagination dominates: If users routinely request offsets in the hundreds of thousands, switch to keyset (seek) pagination. Instead of OFFSET n, filter on the last seen key: WHERE id > :lastId ORDER BY id. This avoids counting skipped rows.
• Streaming export is required: For one-pass exports, use a Hibernate ScrollableResults or a JPA Stream with a forward-only cursor rather than discrete pages.
• The sort column is unindexed: An ORDER BY on a non-indexed column forces a full sort on every page request. Either add an index or paginate on an indexed unique key.
• Strong consistency is required: If pages must reflect a single point-in-time snapshot, run the traversal inside a repeatable-read transaction or use a database snapshot, because independent page queries observe independent states.

Verification in practice

After implementing the loop, run it against a staging table with a known row count. Compare the number of distinct primary keys collected across all pages against the count query. If the numbers differ, inspect the ORDER BY clause first, then the offset arithmetic. Log the generated SQL for the first and last pages to confirm that LIMIT and OFFSET scale with the page index rather than disappearing on deep pages.