Implementing Server-Side Pagination in Grails with GORM

The Problem: Memory Exhaustion from Large Result Sets

Loading thousands of domain objects into the Java Virtual Machine (JVM) to display a simple list causes high heap memory consumption and slow response times. Without server-side pagination, Grails attempts to fetch every matching record from the database into memory, which frequently leads to OutOfMemoryError as the dataset grows.

The solution is to shift the filtering logic to the database using max (limiting the result set size) and offset (skipping a specific number of rows). This ensures the application only handles a small, manageable slice of data per request.

Prerequisites

• A Grails project (version 3.x, 4.x, or 5.x) with GORM configured.
• A domain class (e.g., Product) populated with a sufficient number of records to test page transitions.
• Database logging enabled in application.yml to verify the generated SQL (e.g., hibernate.show_sql: true).

Implementing Pagination in a Service

Pagination logic should reside in a service layer to keep controllers thin and ensure the business logic is reusable. The following implementation uses a GORM dynamic finder to retrieve a specific page of records.

class ProductService {
    
    /**
     * Retrieves a paginated list of products
     * @param page The current page number (starting at 1)
     * @param pageSize The number of records per page
     */
    List<Product> getPaginatedProducts(int page, int pageSize) {
        // Prevent negative values or zero to avoid SQL errors
        int validatedPage = page < 1 ? 1 : page
        int validatedSize = pageSize < 1 ? 10 : pageSize
        
        // Cap the max size to prevent DoS attacks via huge page requests
        if (validatedSize > 100) {
            validatedSize = 100
        }

        // Calculate offset: (Page 1 = 0, Page 2 = pageSize, etc.)
        int offset = (validatedPage - 1) * validatedSize

        return Product.findAll(
            max: validatedSize, 
            offset: offset, 
            sort: "name", 
            order: "asc"
        )
    }
}

Comparison: Offset vs. Full Load

Metric	Full Load (findAll())	Paginated Load (max/offset)
SQL Generated	SELECT * FROM product	SELECT * FROM product LIMIT 20 OFFSET 40
JVM Memory	Proportional to total table size	Constant (proportional to page size)
DB Latency	High for large tables	Low for early pages; increases for deep offsets

Verification and Diagnostics

To verify the implementation, execute the following checks in your environment:

1. First Page Check: Call the service with page: 1, pageSize: 10. Verify that exactly 10 records are returned and that they are the first 10 based on your sort criteria.
2. Second Page Check: Call the service with page: 2, pageSize: 10. Verify that the results start from the 11th record in the database.
3. SQL Inspection: Check the console logs. You should see the LIMIT and OFFSET keywords (or the database equivalent, such as TOP or ROWNUM) in the generated Hibernate SQL.

Critical Limitations

• Deep Pagination Performance: As the offset value increases (e.g., offset 100,000), the database must still scan through all preceding rows before returning the requested slice. This can lead to significant performance degradation.
• Data Drift: If records are inserted or deleted between two page requests, a user may see the same record twice or skip a record entirely because the row indices have shifted.

Rollback and Recovery

Since this implementation changes how data is queried rather than altering the database schema, there is no state to roll back. To revert to the previous behavior, remove the max and offset parameters from the findAll method. However, be aware that doing so on a production dataset may trigger an immediate OutOfMemoryError if the table is large.