Stopping the Memory Leak: Implementing Efficient Pagination in Grails GORM

The Problem: When .list() Stops Scaling

It starts with a simple .list() call in a Grails controller. When your table has 100 records, it works fine. When it grows to 100,000, the application stutters and may eventually throw an OutOfMemoryError. The cause is straightforward: GORM (Grails Object Relational Mapping) loads every matching row into the JVM heap as a managed object before the view renders.

The fix is server-side pagination. Instead of fetching the entire dataset and slicing it in the UI, you tell the database to return only the slice needed for the current page. This moves the heavy lifting from the application server to the database engine.

How GORM Pagination Works

GORM provides a built-in paginate method that handles offset math for you. When you call paginate, GORM issues two queries: one to count total matching records and one to fetch the current page. The result is a PagedResult object containing the data list plus metadata such as total count and current page.

For simple lookups, dynamic finders accept max and offset parameters. For complex filtering, Criteria queries offer more control. Both translate directly to SQL LIMIT and OFFSET clauses, so only a small number of rows travel over the network.

Example: Paginated Product Search

Assume a Product domain class backed by a large table. The following controller action uses a Criteria query with pagination. This example assumes Grails 5+ with GORM for Hibernate.

// ProductController.groovy
def index(Integer page = 0, Integer pageSize = 20, String searchTerm = "") {
    def pageIndex = page < 0 ? 0 : page
    def results = Product.withCriteria {
        if (searchTerm) {
            ilike('name', "%${searchTerm}%")
        }
        order('createdAt', 'desc')
    }.paginate(page: pageIndex, max: pageSize)
    [results: results]
}

Run this in a controller or service. No special permissions are required beyond normal GORM access. The placeholders are page, pageSize, and searchTerm.

Verifying the SQL

Enable SQL logging in application.yml to confirm database-level pagination:

# application.yml
hibernate:
    show_sql: true
    format_sql: true

You should see two statements: a SELECT count(*) and a SELECT ... LIMIT 20 OFFSET X. If the second query lacks LIMIT/OFFSET, pagination is not being applied at the database level.

Trade-offs: Deep Pagination

Pagination fixes memory pressure but introduces a performance ceiling known as deep pagination. As the OFFSET grows—say page 1,000—the database still scans all preceding rows before discarding them to reach the requested start point.

Metric	Shallow Pages (1–10)	Deep Pages (1000+)
DB Execution Time	Fast (index seek)	Slow (scan skipped rows)
JVM Memory	Low/constant	Low/constant
Network Payload	Small	Small

For very large datasets, prefer Next/Previous navigation or keyset pagination (filtering by the last seen ID) rather than allowing arbitrary page jumps.

Sorting Is Not Optional

Pagination without an explicit order clause is non-deterministic. Databases do not guarantee row order unless you specify it. Without order('id') or a similar unique sort, records can duplicate or disappear across page boundaries when the underlying data changes between requests.

Actionable Checklist

• Replace .list() with .paginate() for any table that can grow beyond a few hundred rows.
• Always sort by a unique property such as id to keep page boundaries stable.
• Inspect SQL logs to confirm LIMIT and OFFSET appear in the fetch query.
• Cap pageSize on the server so a client cannot request an unbounded page and exhaust heap.