Cursor pagination

Offset pagination has two structural problems on large or frequently mutated tables: OFFSET n computes and throws away the first n rows on every request (O(n), page 400 means combing through 8000 rows), and under concurrent inserts the pages drift: the same row shows up on two pages, or on none. Cursor (keyset) pagination solves both: the client receives an opaque cursor encoding the sort key of the last row it saw, and the next page continues from there with an indexed seek (WHERE (created_at, id) < (...)), at near-constant cost, however deep you page.

func NormalizeCursor(cursor *string, limit *int, sort string, opts ...Option) (CursorParams, error)

type CursorParams struct{ Limit int /* + decoded keys */ }
func (p CursorParams) HasCursor() bool          // was a cursor supplied, i.e. is this a page after the first
func (p CursorParams) DecodeKey(dests ...any) error // read out the key values, Scan-style
func (p CursorParams) FetchLimit() int          // Limit+1, how we detect whether another page follows
func (p CursorParams) NextCursor(keys ...any) (string, error)

func NewCursorPage[T any](rows []T, p CursorParams, key func(last T) []any) (CursorPage[T], error)

type CursorPage[T any] struct {
    Items      []T
    NextCursor string // "" on the last page
    HasMore    bool
}

var ErrInvalidCursor // errs definition: HTTP 400, code: paginate_invalid_cursor

NormalizeCursor clamps limit with the same WithDefaultPerPage/WithMaxPerPage options as Normalize (WithMaxPage is ignored, there is no page number here). The sort argument must be exactly the ORDER BY expression the query uses: its fingerprint is stamped into issued cursors, and a cursor issued for a different sort is later rejected.

The shop product listing, end to end

The full path of GET /api/v1/shop/products?cursor=...&limit=... in the sample app, handler → service → repository → response:

func (h *Handler) ListProducts(ctx context.Context, req gen.ListProductsRequest) (gen.ListProductsResponse, error) {
    page, err := h.svc.ListProducts(ctx, req.Params.Cursor, req.Params.Limit)
    if err != nil {
        return nil, service.ErrListProducts.Wrap(err, "api: list products")
    }
    return mapper.ToProductList(page), nil // paginate.CursorPage[Product] → gen.CursorPageProduct
}

const productSort = "created_at DESC, id DESC" // must match the repository's ORDER BY

func (s *Service) ListProducts(ctx context.Context, cursor *string, limit *int) (paginate.CursorPage[Product], error) {
    p, err := paginate.NormalizeCursor(cursor, limit, productSort)
    if err != nil {
        return paginate.CursorPage[Product]{}, err // ErrInvalidCursor → HTTP 400
    }
    return s.products.List(ctx, p)
}

func (r *Products) List(ctx context.Context, p paginate.CursorParams) (paginate.CursorPage[Product], error) {
    query := `SELECT id, name, price_cents, created_at FROM products`
    args := []any{p.FetchLimit()}

    if p.HasCursor() {
        var createdAt time.Time
        var id string
        if err := p.DecodeKey(&createdAt, &id); err != nil {
            return paginate.CursorPage[Product]{}, err
        }
        query += ` WHERE (created_at, id) < ($2, $3)`
        args = append(args, createdAt, id)
    }
    query += ` ORDER BY created_at DESC, id DESC LIMIT $1`

    rows, err := r.db.Query(ctx, query, args...)
    if err != nil {
        return paginate.CursorPage[Product]{}, errs.Wrap(err, "products: list")
    }
    products, err := pgx.CollectRows(rows, pgx.RowToStructByName[Product])
    if err != nil {
        return paginate.CursorPage[Product]{}, errs.Wrap(err, "products: scan")
    }

    return paginate.NewCursorPage(products, p, func(last Product) []any {
        return []any{last.CreatedAt, last.ID}
    })
}

The key to the mechanics is the +1 row: the query requests FetchLimit() (= Limit+1), and NewCursorPage cuts the page down to Limit items. If the extra row was present, HasMore = true, and NextCursor is built from the key of the last actually returned item: no separate COUNT query. The key function must return exactly the values, in exactly the order, used by the ORDER BY / seek predicate.

Requirements of the seek predicate

The Postgres row-value comparison ((created_at, id) < ($2, $3)) is only correct and fast when:

• the sort direction is uniform across all key columns: mixed ASC/DESC cannot be expressed with a tuple comparison;
• every key column is NOT NULL: a NULL tuple element makes the whole comparison NULL, silently dropping the row from every page;
• the last key is a unique tiebreaker: typically the primary key, so a non-unique leading column (e.g. created_at) cannot cause skipped or duplicated rows at page boundaries;
• a composite index matching the ORDER BY exists (in the shop: CREATE INDEX ON products (created_at DESC, id DESC)), so the seek stays an index scan.

The opaque cursor

Towards the client the cursor is an opaque string: a base64url-encoded, versioned JSON envelope carrying the key values and a fingerprint of the sort spec. NormalizeCursor rejects with ErrInvalidCursor (HTTP 400, code paginate_invalid_cursor) any cursor that fails to decode, was made for a different version, or was issued for a different sort order. It never silently falls back to the first page, because that would hide the client's bug. Two consequences:

• changing an endpoint's default sort invalidates outstanding cursors, by design;
• the cursor is not encrypted or signed: its key values (timestamp, id) are plainly decodable, and nothing stops a client from hand-editing one. Treat it as an efficient continuation token, not a tamper-proof capability: don't put anything in it the client must not see, and don't use it as an authorization boundary. The sort fingerprint only guards against a cursor being replayed against the wrong ORDER BY, not against a client forging key values outright; if forged values point past a row the caller shouldn't see, your query's own authorization filter (a tenant or ownership WHERE clause) is what has to catch that, exactly as it would for a hand-crafted OFFSET.

Which one, when

Rule of thumb from the shop: the public product listing (large, growing, infinite scroll) uses cursors; an admin-side list of a few hundred rows is more comfortable with page numbers on offset.

Patterns used

• Opaque, tamper-resistant cursor (base64url + sha256 sort fingerprint): Design patterns. "Tamper-resistant" here refers specifically to the sort-fingerprint check above, not to the key values, which travel in the clear; see the integrity note above.
• Generic result wrapper (CursorPage[T]), the sibling of offset's Page[T]: see Overview.

Where to next

• Overview: offset pagination (Normalize, Page[T]), install, and the standalone example.