Codeac and Hibernate pagination: why offset pagination is a liability at scale

An API that returns page 1 in 40 ms and page 10,000 in 4 seconds is not a network problem. It is offset pagination in Hibernate. Codeac surfaces this pattern as a performance and stability risk because Query.setFirstResult() and Query.setMaxResults() translate to LIMIT … OFFSET …, and the database must scan and discard all preceding rows even when you only need the next 20.

The problem Codeac flags in Hibernate code

Offset-based pagination is simple to implement. A typical Spring Data repository method ends up as JPQL with setFirstResult and setMaxResults:

// run in application code, requires read access to the entity
TypedQuery<Entity> q = em.createQuery("SELECT e FROM Entity e ORDER BY e.id", Entity.class);
q.setFirstResult(page * size);   // offset
q.setMaxResults(size);           // limit
List<Entity> page = q.getResultList();

Codeac treats this as a review signal when the query touches a large table without a keyset boundary. The risk is linear degradation: as offset grows, the database work grows. With legacy dialects, setFirstResult can also trigger in-memory pagination, which risks OutOfMemoryError for large result sets.

Offset pagination also drifts. If rows are inserted or deleted between requests, items can be skipped or duplicated because the offset is relative to a moving window.

Keyset pagination as a stable alternative

Keyset pagination, also called seek method pagination, filters on a unique ordered column instead of skipping rows.

// run in application code, requires read access to the entity
TypedQuery<Entity> q = em.createQuery(
  "SELECT e FROM Entity e WHERE e.id > :lastSeenId ORDER BY e.id ASC", Entity.class);
q.setParameter("lastSeenId", lastSeenId);
q.setMaxResults(pageSize);
List<Entity> nextPage = q.getResultList();

The database can use an index on id to seek directly to lastSeenId and read the next pageSize rows. Execution time stays stable regardless of page depth, provided the ordering column is indexed and unique.

Keyset pagination does not support random access to an arbitrary page number without knowing the last seen key from the previous page. UIs that require "jump to page 10" need a different strategy.

Cursor processing for bulk work

For batch jobs that need to stream a large table without loading it into memory, Hibernate ScrollableResults provides cursor-based processing.

// run in application code, requires read access to the entity
ScrollableResults scroll = em.createQuery("SELECT e FROM Entity e ORDER BY e.id")
  .setReadOnly(true)
  .scroll(ScrollMode.FORWARD_ONLY);
// iterate row by row, flush and clear the persistence context periodically

This avoids building a full List in the JVM. Memory footprint is lower than list-based pagination, but the session remains open for the duration of the scan, which has transactional and connection-holding implications.

Trade-off and how to check

The engineering decision is access pattern versus performance stability. Offset pagination supports arbitrary page numbers but degrades and drifts. Keyset pagination is stable and drift-resistant but requires sequential navigation and a reliable ordering key.

Practical verification steps for review:

• Run EXPLAIN for a high offset query, e.g., offset 100,000, in a database client with read permission. Look for full index scans and increasing row estimates.
• Implement a keyset variant for the same query and compare execution plans. Expect a seek on the indexed ordering column.
• Compare memory usage of list-based pagination versus ScrollableResults using a JVM profiler in a test environment. Do not run profiling in production.

Verification is required because behavior depends on Hibernate version, dialect, and database optimizer. Treat findings as review signals, not guarantees.

Actionable closing

When Codeac highlights setFirstResult on a large, mutable table, prefer keyset pagination for sequential UIs and ScrollableResults for bulk processing. Keep offset pagination only where random page access is a hard requirement and the table is small and stable. Document the ordering key and index requirements alongside the query.