GORM Pagination Filtering and Sorting for Go APIs Done Well

Every list endpoint ends up needing the same things: a filter or two, a sort the caller can choose, and pages. It is easy to write a version that works on a hundred rows and one that stays correct and fast on five hundred thousand takes a few decisions. This article writes the query with GORM, rejects a hostile sort parameter, and measures offset against keyset paging on PostgreSQL 18.6 with half a million rows.

It builds on scopes, which package the same pieces as reusable functions. The SQL level argument for keyset paging is in keyset pagination in Go and the sqlc version is sqlc pagination. How an API exposes it (cursors, links, headers) is covered in the database cornerstone and the API articles.

One struct for the request

The Page struct holds everything a list call can vary: an optional Kind filter, a Sort column, a Dir, a Limit, and an After cursor. A handler parses the query string into it, applies defaults and limits, and passes it to the store. Keeping the parameters in one struct means the store has one entry point to test, and a new filter is a new field.

The function that builds the query does three things worth looking at. Here is the whole program:

package main

import (
	"errors"
	"fmt"
	"log"
	"strings"
	"time"

	"gorm.io/driver/postgres"
	"gorm.io/gorm"
	"gorm.io/gorm/logger"
)

func must(err error) {
	if err != nil {
		log.SetFlags(0)
		log.Fatal(err)
	}
}

// connect recreates a schema and returns a connection whose search_path points at it.
func connect(schema string, cfg *gorm.Config) *gorm.DB {
	base := "host=127.0.0.1 port=55432 user=postgres password=secret dbname=lab sslmode=disable"
	admin, err := gorm.Open(postgres.Open(base), &gorm.Config{Logger: logger.Discard})
	must(err)
	must(admin.Exec("drop schema if exists " + schema + " cascade").Error)
	must(admin.Exec("create schema " + schema).Error)
	if cfg == nil {
		cfg = &gorm.Config{Logger: logger.Discard}
	}
	db, err := gorm.Open(postgres.Open(base+" search_path="+schema), cfg)
	must(err)
	return db
}

type Event struct {
	ID       uint
	Kind     string `gorm:"index"`
	Priority int
	Title    string
}

var sortable = map[string]string{"id": "id", "priority": "priority", "title": "title"}

type Page struct {
	Kind  string
	Sort  string
	Dir   string
	Limit int
	After uint // keyset cursor
}

func list(db *gorm.DB, p Page) ([]Event, error) {
	col, ok := sortable[p.Sort]
	if !ok {
		return nil, errors.New("unknown sort column")
	}
	dir := "asc"
	if strings.EqualFold(p.Dir, "desc") {
		dir = "desc"
	}
	q := db.Model(&Event{})
	if p.Kind != "" {
		q = q.Where("kind = ?", p.Kind)
	}
	if p.After > 0 {
		q = q.Where("id > ?", p.After)
	}
	var out []Event
	err := q.Order(col + " " + dir + ", id " + dir).Limit(p.Limit).Find(&out).Error
	return out, err
}

func main() {
	db := connect("l0717", nil)
	must(db.AutoMigrate(&Event{}))
	must(db.Exec(`insert into events (kind, priority, title)
		select (array['a','b','c','d'])[1 + g % 4], g % 7, 'event ' || g from generate_series(1, 500000) g`).Error)
	must(db.Exec("analyze events").Error)

	// Filter plus sort, and rejecting an unknown column.
	got, err := list(db, Page{Kind: "b", Sort: "priority", Dir: "desc", Limit: 3})
	must(err)
	for _, e := range got {
		fmt.Println("row:", e.ID, e.Kind, e.Priority)
	}
	_, err = list(db, Page{Sort: "title; drop table events", Limit: 3})
	fmt.Println("hostile sort:", err)

	// Total count and offset pages.
	var total int64
	db.Model(&Event{}).Where("kind = ?", "b").Count(&total)
	fmt.Println("total for kind b:", total)

	timeIt := func(label string, fn func()) {
		best := time.Hour
		for i := 0; i < 5; i++ {
			s := time.Now()
			fn()
			if d := time.Since(s); d < best {
				best = d
			}
		}
		fmt.Printf("%-34s %s\n", label, best.Round(10*time.Microsecond))
	}
	var page []Event
	timeIt("offset 0", func() { db.Order("id").Offset(0).Limit(50).Find(&page) })
	timeIt("offset 400000", func() { db.Order("id").Offset(400000).Limit(50).Find(&page) })
	timeIt("keyset after id 400000", func() { db.Where("id > ?", 400000).Order("id").Limit(50).Find(&page) })
	timeIt("count(*) of all rows", func() { var n int64; db.Model(&Event{}).Count(&n) })

	// A keyset walk: the cursor is the last id of the previous page.
	var cursor uint
	pages := 0
	for {
		rows, err := list(db, Page{Kind: "d", Sort: "id", Limit: 40000, After: cursor})
		must(err)
		if len(rows) == 0 {
			break
		}
		pages++
		cursor = rows[len(rows)-1].ID
	}
	fmt.Println("keyset walk over kind d, pages of 40000:", pages)
}

row: 499981 b 6
row: 499953 b 6
row: 499925 b 6
hostile sort: unknown sort column
total for kind b: 125000
offset 0                           310µs
offset 400000                      21.77ms
keyset after id 400000             360µs
count(*) of all rows               13.14ms
keyset walk over kind d, pages of 40000: 4

Filters are optional Where calls

if p.Kind != "" { q = q.Where("kind = ?", p.Kind) } adds the condition only when the caller supplied one. The value is a bind parameter. The empty string means "no filter", which is fine for text; for a numeric filter where 0 is meaningful, make the field a pointer so absent and zero differ.

Sort comes from an allow list

A sort parameter is a column name, and a column name cannot be a bind parameter. The lab keeps a map from public sort keys to real column names, sortable, and looks the request value up in it. Anything not in the map returns an error, which the handler maps to a 400. The hostile request in the lab, title; drop table events, came back as hostile sort: unknown sort column. The input never reached the SQL text because only the map's own values do.

The map also decouples the API from the schema: a public "newest" can map to created_at and be renamed in the database later. The direction is handled the same way: anything other than desc (compared case insensitively) becomes asc. Do not pass the raw direction through.

Add a tie breaker

The order clause is col dir, id dir. A sort by priority has only seven distinct values, so thousands of rows tie, and PostgreSQL may return tied rows in a different order on each query. Pages then overlap or skip rows. Adding the unique id as the last sort key makes the order total and the paging stable. The first three result rows in the lab show it working: priority 6, ids 499981, 499953, 499925, descending.

Any paged query needs an order that includes a unique column. Without it, "page 2" has no fixed meaning.

Offset versus keyset on half a million rows

The lab inserted 500000 events with a single insert ... select ... from generate_series, ran analyze, and timed four queries, taking the best of five runs each:

Offset gets slower with depth. OFFSET 400000 makes PostgreSQL walk past 400000 rows and throw them away before returning 50. Page 1 took 310 µs, page 8000 took 21.77 ms, about 70 times longer for the same 50 rows. The cost grows linearly with the offset, and it is paid on every request.

Keyset paging does not. The page after id 400000 took 360 µs, equal to page one, because the query is an index range scan that starts at the cursor. The cost depends on the page size, not the position. The numbers come from one machine with warm caches, so read them as a ratio. The shape (flat versus linear) is the point, and it holds anywhere.

The trade is what each can do:

Use offset for small, bounded lists where the user clicks page numbers. Use keyset for feeds, exports and any API that clients walk to the end. The lab's walk over one kind in pages of 40000 took 4 requests: each used the last id of the previous page as After, and the loop ended on an empty page.

Keyset with a non unique sort

The lab's After is an id, which works when the sort is by id. For a sort by priority, the cursor must carry both values, and the condition becomes a tuple comparison: (priority, id) < (?, ?) for a descending sort. PostgreSQL supports row comparisons and can use a composite index on (priority, id) for them. Encode the pair into an opaque cursor string (base64 of the two values) so that clients treat it as a token and the format can change.

The total count

A numbered pager needs the total. Count on the whole table took 13.14 ms here, and it grows with the table, because PostgreSQL has to visit the rows (or an index) to count them. With a filter it is faster or slower depending on the index. Options:

• Skip it. Keyset APIs return a next cursor and no total. Most feeds do not need one.
• Cap it. Count up to a limit (select count(*) from (select 1 from events where ... limit 10001) t) and show "10000+".
• Estimate it. For an unfiltered table, pg_class.reltuples is close enough for a pager and costs nothing.
• Cache it for a short time, per filter.

Run the count on a base query without Limit, Offset or Order, as described in the scopes article, and use Session(&gorm.Session{}) to reuse the filtered base.

Limits belong on the server

Clamp Limit to a maximum (100 is a common ceiling) and apply a default. A client that sends limit=1000000 should get the maximum, not a million rows into memory. Reject a negative offset and a page number so large it overflows when multiplied by the size. These checks are boring, and they decide whether an endpoint can be used to take a service down.

Index for the sort you offer

Each sort option needs an index that serves it, or the query sorts the whole filtered set on every request. The lab's kind filter has an index. A request sorted by priority within a kind would use a composite index (kind, priority, id) to avoid a sort. Check with EXPLAIN that the plan uses it (the explain article shows how), and offer only the sorts you have indexed. Allow listing sort columns is also the way to keep that list short.

Checklist

• Parse the request into a struct, with defaults and a maximum limit.
• Look up the sort column in a map. Never concatenate request text into an order clause.
• Add a unique tie breaker to every order.
• Use keyset paging for feeds, exports and deep lists, with an opaque cursor.
• Skip, cap or estimate the total count unless a pager needs it.
• Index what you let callers sort and filter by.

The query methods are described in the GORM query guide, and the reasoning about keyset paging in Use The Index, Luke.