Server-Side Pagination in Grails with GORM: Avoiding the Memory Wall

The Memory Wall: Why Fetching All Records Fails

When a Grails controller retrieves a full list of domain objects and lets the UI handle pagination, the application loads every matching row into the JVM heap. As the table grows to hundreds of thousands of records, this causes high memory consumption, frequent garbage collection, and can trigger OutOfMemoryError.

Using GORM’s Max and Offset for Server‑Side Pagination

GORM exposes the max and offset parameters on dynamic finders, criteria queries, and where queries. These map directly to the SQL LIMIT and OFFSET clauses, so the database returns only the requested slice.

Example Service Method

// BookService.groovy
class BookService {
    static final int PAGE_SIZE = 20

    PagedResult listBooks(Integer page, String genre) {
        // page numbers start at 1; calculate zero‑based offset
        int offset = ((page ?: 1) - 1) * PAGE_SIZE

        // total count for UI controls
        long total = Book.createCriteria().list {
            if (genre) { eq('genre', genre) }
            projections { count('id') }
        }[0]

        // fetch the page of data
        List results = Book.createCriteria().list {
            if (genre) { eq('genre', genre) }
            max PAGE_SIZE
            offset offset
            order('title', 'asc')
        }

        return new PagedResult(results: results, totalCount: total, page: page)
    }
}

class PagedResult {
    List results
    long totalCount
    Integer page
}

Verifying the Generated SQL

To confirm that pagination is pushed to the database, enable Hibernate SQL logging:

log4j.category.org.hibernate.SQL=DEBUG

When calling listBooks, look for lines containing LIMIT and OFFSET in the console or log file. If those clauses are missing, the query is pulling more data than needed.
Trade‑offs and Limitations
The offset-based approach works well for early pages but suffers from deep paging performance: as the offset grows, the database must scan and discard increasing numbers of rows before returning the slice, leading to linear slowdown.
Additionally, the separate count() query required for pagination UI can become costly on very large tables without proper indexes on the filtered and sorted columns.
Mitigation Strategies

Ensure columns used in where and order by are indexed.
For extremely large datasets consider keyset (seek) pagination: store the last seen identifier and fetch the next page with gt('id', lastId) instead of an offset.

Actionable Checklist

Define a constant PAGE_SIZE in your service.
Compute offset as (page - 1) * PAGE_SIZE.
Use max and offset inside a GORM criteria or where query.
Run a separate count projection to supply totalCount for the UI.
Enable SQL debug logging and verify the presence of LIMIT/OFFSET.
Monitor response times; if deep paging becomes an issue, evaluate keyset pagination.