GORM Pagination API and Hibernate SQL Execution: Bounding Large Datasets with max and offset

Direct Answer

Bound max in the controller or service layer, not in GORM globally. Append a unique tie-breaker—usually the primary key—to every sort on non-unique properties. For large datasets, use manual keyset (seek) queries instead of deep offset paging.

Confirmed Behavior vs. Assumptions

Confirmed: GORM’s list method binds max and offset to Hibernate’s setMaxResults and setFirstResult, producing LIMIT/OFFSET SQL on dialects that support it. Omitting max returns every matching row. Ordering by a non-unique column does not guarantee a stable row order between queries, so duplicates or gaps can appear across page boundaries. GORM does not ship first-class keyset pagination.

Likely explanation: The performance cost of large offsets is a database execution characteristic—skipped rows are scanned and discarded—not a GORM defect. A global default max would break legitimate batch, export, or internal processing use cases, so bounding is better placed where the caller understands the intent. Exact SQL generation and LIMIT/OFFSET support depend on the Hibernate version and database dialect; verify on your specific stack before relying on it.

Steps for This Case

1. Cap results at the call site. Never pass an unbounded list() from a request-scoped controller. Clamp the incoming parameter to a safe ceiling:

   def pageSize = params.int('max') ?: 25
   pageSize = Math.min(pageSize, 100)
   def results = Book.list(max: pageSize, offset: params.int('offset') ?: 0, sort: 'title,id')

2. Add a deterministic tie-breaker. When sorting by a non-unique property, append the primary key so consecutive pages remain stable:

   def results = Book.list(sort: 'authorName,id', max: 50, offset: 100)

   Verify that the composite ordering aligns with business requirements and existing indexes.

3. Switch to keyset for deep traversal. When the dataset is large or offsets grow deep, replace offset with a predicate on the last seen key:

   def lastId = params.long('lastId') ?: 0L
   def results = Book.createCriteria().list(max: 50) {
       gt('id', lastId)
       order('id', 'asc')
   }

   This avoids scanning discarded rows and keeps paging cost roughly constant.

4. Verify generated SQL. Enable Hibernate SQL logging and call Book.list(max: 10, offset: 100) to confirm the dialect emits LIMIT/OFFSET. On a large table, compare execution time for offset: 10000 against the criteria query above to measure the difference.

Offset vs. Keyset Trade-off

Offset paging is acceptable for shallow, bounded navigation—admin tables, small reference data, or UIs where users rarely click past the first few pages. Once the expected offset reaches thousands of rows, the linear scan-and-discard cost makes manual seek queries the safer default. The trade-off is code complexity: keyset queries bypass GORM’s dynamic finders and require explicit criteria or HQL, increasing testing burden.

What is the maximum offset depth your API must support? If it stays in the low hundreds, offset with a tie-breaker is sufficient; if it routinely exceeds a few thousand, plan for keyset pagination now.