Paging Grails Data the Right Way: GORM max/offset and the paginate Tag

Your Grails list page worked fine in development with twelve seed rows. Then production hit 80,000 rows and the page took eleven seconds to load because the controller was fetching everything and the browser was drowning. The fix is not caching or a bigger server — it is letting the database return one page at a time, which GORM already knows how to do if you ask it correctly.

This post walks through the standard Grails pagination pattern: list(max:offset:) plus count() in the controller, and the <g:paginate> tag in the GSP. It is the same pattern scaffolding generates, but understanding what it actually does — and where it breaks down — saves you from two common production surprises.

What max and offset really do

When you write Book.list(max: 20, offset: 40), GORM translates that into SQL LIMIT 20 OFFSET 40 (or the dialect equivalent). Only twenty rows cross the wire and enter the heap. That is the entire point: memory stays flat whether the table has a hundred rows or a hundred million.

The contrast worth internalizing is the naive alternative — Book.list() followed by Groovy slicing like books[40..59]. That loads every row into memory as domain instances, then throws most of them away. It looks identical in the view and behaves catastrophically differently under load.

The controller pattern, with the safety clamp

A conventional controller action reads params.max and params.offset. Grails data binding coerces them to integers for you. The one thing you must add yourself is an upper bound, because max comes from the request and a client asking for max=100000 will get it if you let them:

def index() {
    int max = Math.min((params.int('max') ?: 10), 100)
    int offset = params.int('offset') ?: 0

    def books = Book.list(max: max, offset: offset,
                          sort: 'title', order: 'asc')
    int total = Book.count()

    [books: books, bookCount: total]
}

Two things to notice. First, Book.count() issues a separate SELECT COUNT(*) query — the tag needs a total to know how many page links to draw, and GORM does not compute it for free. Second, the same max/offset arguments work on criteria queries and where queries, so filtered searches paginate identically:

def results = Book.where {
    genre == 'sci-fi' && yearPublished > 2000
}.list(max: max, offset: offset, sort: 'title')

The view side: g:paginate does more than it looks

In the GSP, the tag is one line:

<g:paginate total="${bookCount}" />

The underappreciated detail: <g:paginate> preserves the current request parameters when it builds page links. If the user arrived at /book/index?genre=sci-fi&sort=title, clicking "page 3" keeps the genre filter and sort. You do not have to thread those params through manually — which is exactly why filtered pagination in Grails is less painful than in frameworks where you build the links yourself.

Run this in a scaffolded app to see it end to end: generate scaffolding for one domain class, seed a hundred rows in BootStrap.groovy, and the generated list view already paginates. To confirm the database is doing the work, enable SQL logging (logSql: true on the datasource in application.yml) and watch for a LIMIT/OFFSET query followed by a separate COUNT query on each page load.

Where this pattern runs out of road

Offset pagination has a known scaling flaw: OFFSET 500000 still makes the database scan and discard half a million rows before returning yours. Page 1 is fast; page 25,000 is not. The standard remedy is keyset (seek) pagination — filtering on id > lastSeenId instead of offsetting — but GORM's list() has no built-in support for it. You would write it as a where query with a gt constraint and lose the "jump to page N" UI, which is often an acceptable trade for infinite-scroll feeds.

The second cost is the count query itself. On a heavily filtered search over a large table, COUNT(*) with the same joins and predicates can rival the page query in cost. If profiling shows it hurting, options include caching the count, showing an approximate total, or dropping the total entirely in favor of "next/previous" controls.

Check your version, then ship it

The parameter names and tag attributes described here are stable across Grails 3 through 6, but GORM internals — default sorting behavior, detached criteria semantics — vary between GORM versions, so verify against your stack before relying on edge behavior. A quick verification pass: request your list action with ?max=5&offset=10 and confirm you get exactly five records starting at the eleventh row, then hit it with ?max=100000 and confirm your clamp caps it. If both hold and SQL logging shows LIMIT/OFFSET plus one COUNT query, your pagination is doing what you think it is — and it will still be doing it when the table grows by three orders of magnitude.