Solving the Deep Pagination Performance Gap in Grails GORM

The Memory Trap of Large Datasets

When building Grails applications, it is tempting to rely on GORM's built-in pagination to handle large tables. However, as your dataset grows from thousands to millions of rows, you will likely notice a specific pattern: the first few pages load instantly, but page 500 feels like it is hanging. This happens because standard offset-based pagination forces the database to scan and discard thousands of rows before returning the requested slice, leading to increased CPU usage and potential memory exhaustion.

The goal is to move the pagination logic from "skipping rows" to "seeking values," ensuring that page 1,000 loads as quickly as page one.

Offset-Based Pagination: The Standard Approach

GORM provides a straightforward way to paginate using max (the page size) and offset (the number of records to skip). This is handled at the database level via LIMIT and OFFSET clauses in SQL.

This approach is ideal for small datasets or administrative panels where users rarely navigate deep into the results. It allows for random access—meaning a user can jump directly to page 10—but it scales linearly in terms of performance cost.

Implementation Example

In a Grails controller, you can implement a basic paginated list by passing request parameters directly into a GORM criteria query. This example assumes a Product domain class and Grails 5+.

// ProductController.groovy

def index(Integer offset, Integer max) {
    // Default values to prevent unbounded queries
    Integer pageOffset = offset ?: 0
    Integer pageSize = max ?: 20

    def results = Product.withCriteria {
        it.orderBy('name', 'asc')
        it.max(pageSize)
        it.offset(pageOffset)
    }

    // Calculate total for the UI pager
    long totalCount = Product.count()

    [results: results, total: totalCount, offset: pageOffset, max: pageSize]
}

Verification: To verify this is working at the database level, enable SQL logging in application.yml. You should see a query ending in LIMIT 20 OFFSET 0. If you see the entire table being loaded into memory, your max and offset parameters are not being passed correctly to the criteria builder.

Keyset Pagination: The High-Performance Alternative

To avoid the O(n) performance hit of OFFSET, you can use Keyset Pagination (also known as the Seek Method). Instead of telling the database to skip 10,000 rows, you tell it to find all rows where the unique ID is greater than the last ID of the previous page.

This transforms the operation from a full scan to an index lookup, keeping response times constant regardless of how deep the user paginates.

Comparison: Offset vs. Keyset

Feature	Offset Pagination	Keyset Pagination
Random Access	Supported (Jump to Page X)	Not Supported (Next/Prev only)
Performance	Degrades as offset increases	Constant time (O(log n))
Data Consistency	Items can shift if rows are deleted	Stable relative to the last seen ID

Implementation Trade-offs and Risks

While Keyset pagination is faster, it introduces specific constraints:

• Ordering Requirements: You must sort by a unique, indexed column (usually the Primary Key). If you sort by a non-unique column like category, you must include the ID as a secondary tie-breaker to prevent records from being skipped or duplicated.
• UI Limitations: You cannot provide a "Jump to Page 50" button because the application does not know the starting ID of page 50 without scanning the previous 49 pages.
• State Management: The client must send back the ID of the last record received to request the next page.

Actionable Summary

When deciding on a pagination strategy in Grails, follow these rules of thumb:

1. Use Offset Pagination for small tables (< 10,000 rows) or when random page access is a hard requirement.
2. Use Keyset Pagination for large-scale datasets or infinite-scroll interfaces where performance is critical.
3. Always set a hard maximum for the max parameter to prevent a user from requesting max=1000000 and triggering an OutOfMemoryError.