sqlc Pagination Patterns for Keyset and Offset Queries

Pagination looks like a solved problem until a table gets big and page 5,000 takes longer than page 1 by a factor of a hundred. The fix is well known (keyset pagination), and it fits sqlc well, because the query is a fixed shape with typed arguments. This article measures both approaches on a million row table, then shows the sqlc query and what to watch for.

The numbers come from PostgreSQL 18.6 with sqlc v1.30.0, pgx v5.11.0 and Go 1.25.6. The idea in plain SQL and database/sql form is in the keyset pagination article, and this one is its sqlc companion. It follows the dynamic queries article and belongs to the database cornerstone. To read a plan like the one below, see the explain and index article.

The table and the queries

A million posts, one per minute, with an index that matches the sort order:

create table posts (
    id      bigserial primary key,
    title   text not null,
    created timestamptz not null
);
insert into posts (title, created)
select 'post ' || i, timestamptz '2026-01-01' + (i * interval '1 minute') from generate_series(1, 1000000) i;
create index posts_created_id on posts (created desc, id desc);
analyze posts;

The index is (created desc, id desc). The query orders by the same columns in the same directions, so PostgreSQL can read rows straight off the index in order and stop after the limit. id is in the index and in the order because created alone is not unique, and a pagination key that has ties can skip or repeat rows at the page boundary.

-- name: PageOffset :many
select id, title, created from posts order by created desc, id desc limit $1 offset $2;

-- name: PageKeyset :many
select id, title, created from posts
where (created, id) < (sqlc.arg('after_created')::timestamptz, sqlc.arg('after_id')::bigint)
order by created desc, id desc limit sqlc.arg('page_size');

-- name: FirstPage :many
select id, title, created from posts order by created desc, id desc limit $1;

-- name: Total :one
select count(*) from posts;

PageOffset is the familiar limit $1 offset $2. PageKeyset replaces the offset with a condition: give me the rows that sort strictly after the last row I showed. The condition is a row comparison, (created, id) < (after_created, after_id), which PostgreSQL evaluates as "created is less, or created is equal and id is less". With a descending index, "after" means "less than". sqlc names the arguments with sqlc.arg(...) and the casts give them types, so the generated parameter struct has AfterCreated, AfterID and PageSize fields.

version: "2"
sql:
  - engine: postgresql
    schema: schema.sql
    queries: query.sql
    gen:
      go:
        package: main
        out: .
        sql_package: pgx/v5

The measurement

package main

import (
	"context"
	"fmt"
	"log"
	"time"

	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/pgxpool"
)

const dsn = "postgres://postgres:[email protected]:55432/sqlc0700?sslmode=disable"

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

func best(f func()) time.Duration {
	d := time.Hour
	for i := 0; i < 5; i++ {
		t := time.Now()
		f()
		if e := time.Since(t); e < d {
			d = e
		}
	}
	return d
}

func main() {
	ctx := context.Background()
	pool, err := pgxpool.New(ctx, dsn)
	must(err)
	defer pool.Close()
	q := New(pool)
	const size = 20

	first, err := q.FirstPage(ctx, size)
	must(err)
	fmt.Println("1. first page:", len(first), "rows, newest id", first[0].ID)

	fmt.Println("2. best of 5 for one page of 20 rows (1,000,000 rows, index on created desc, id desc):")
	for _, off := range []int32{0, 1000, 100000, 900000} {
		d := best(func() { _, err := q.PageOffset(ctx, PageOffsetParams{Limit: size, Offset: off}); must(err) })
		fmt.Printf("   OFFSET %-7d %8.2f ms\n", off, float64(d.Microseconds())/1000)
	}

	// Walk 40000 pages with keyset, each starting from the last row of the one before.
	cur := first[len(first)-1]
	for i := 0; i < 40000; i++ {
		page, err := q.PageKeyset(ctx, PageKeysetParams{AfterCreated: cur.Created, AfterID: cur.ID, PageSize: size})
		must(err)
		cur = page[len(page)-1]
	}
	const depth = 20 + 40000*20
	next, err := q.PageKeyset(ctx, PageKeysetParams{AfterCreated: cur.Created, AfterID: cur.ID, PageSize: size})
	must(err)
	viaOffset, err := q.PageOffset(ctx, PageOffsetParams{Limit: size, Offset: depth})
	must(err)
	fmt.Println("3. 40000 keyset pages later, both methods agree on the next page:", next[0].ID == viaOffset[0].ID, "(first id", next[0].ID, ")")

	dk := best(func() {
		_, err := q.PageKeyset(ctx, PageKeysetParams{AfterCreated: cur.Created, AfterID: cur.ID, PageSize: size})
		must(err)
	})
	do := best(func() { _, err := q.PageOffset(ctx, PageOffsetParams{Limit: size, Offset: depth}); must(err) })
	fmt.Printf("4. at depth %d rows: keyset %.2f ms, OFFSET %.2f ms\n", depth, float64(dk.Microseconds())/1000, float64(do.Microseconds())/1000)

	rows, err := pool.Query(ctx, "explain (analyze, costs off, timing off, summary off) select id from posts order by created desc, id desc limit 20 offset 100000")
	must(err)
	lines, err := pgx.CollectRows(rows, pgx.RowTo[string])
	must(err)
	fmt.Println("5. plan for OFFSET 100000:")
	for _, l := range lines {
		fmt.Println("   ", l)
	}
	n, _ := q.Total(ctx)
	fmt.Println("6. rows:", n)
}

set -e
export PGOPTIONS='-c client_min_messages=warning'
DB=sqlc0700
docker exec -e PGOPTIONS="$PGOPTIONS" gh-lab-pg psql -U postgres -qc "drop database if exists $DB" -c "create database $DB"
docker exec -i -e PGOPTIONS="$PGOPTIONS" gh-lab-pg psql -U postgres -d $DB -q < schema.sql
"$HOME/go/bin/sqlc" generate
go run .

1. first page: 20 rows, newest id 1000000
2. best of 5 for one page of 20 rows (1,000,000 rows, index on created desc, id desc):
   OFFSET 0           0.37 ms
   OFFSET 1000        0.45 ms
   OFFSET 100000      5.73 ms
   OFFSET 900000     42.76 ms
3. 40000 keyset pages later, both methods agree on the next page: true (first id 199980 )
4. at depth 800020 rows: keyset 0.33 ms, OFFSET 38.85 ms
5. plan for OFFSET 100000:
    Limit (actual rows=20.00 loops=1)
      Buffers: shared hit=387
      ->  Index Only Scan using posts_created_id on posts (actual rows=100020.00 loops=1)
            Heap Fetches: 0
            Index Searches: 1
            Buffers: shared hit=387
6. rows: 1000000

Each figure is the best of five runs on a warm cache, on one machine with the database in a container on loopback, so read the shape and not the digits. Page time rises with the offset, from under half a millisecond to over forty, and it does so on a table with an index that matches the order, which is the best case for OFFSET.

Why it grows. PostgreSQL cannot jump to the 900,000th row. To skip rows it must produce them and throw them away. The plan the lab printed for OFFSET 100000 shows it: an index only scan that returned 100,020 rows (actual rows=100020) under a Limit that kept 20. The scan touched 387 buffers and no heap pages, since the index covered the query, and that is the cheap version of the cost. If the query had needed columns outside the index, every skipped row would have meant a heap fetch too.

Keyset stays flat. The program then walked the table with the keyset query: 40,000 pages, each started from the last row of the one before. At that depth (800,020 rows in) a keyset page took 0.33 ms, close to the page at the start, while the equivalent OFFSET 800020 took 38.85 ms. The two methods returned the same first id for the next page (199980), so they describe the same ordering. The keyset query asks the index to start at a position and read 20 rows, which costs the same wherever the position is.

Using it in an API

The cursor is the last row's sort key. An API returns it with the page, and the client sends it back:

page, err := q.PageKeyset(ctx, PageKeysetParams{
	AfterCreated: cursor.Created,
	AfterID:      cursor.ID,
	PageSize:     20,
})

Three practical points.

Encode the cursor. Send it as an opaque string (base64 of the two values, or a signed token), not as two query parameters. That keeps clients from building their own and lets you change the key later without breaking them.

The first page has no cursor. The lab has a separate FirstPage query for it, which is the same SQL without the condition. The alternative is one query with the optional filter pattern from the dynamic queries article, at the cost of the plan problems described there. Two small queries are simpler and plan better.

Fetch one more than you show. Ask for 21 rows to learn whether a next page exists, show 20, and use the 20th for the cursor. That avoids a count and avoids an empty last page.

What keyset cannot do

The limits are the reason OFFSET survives.

• No jumping. There is no way to ask for page 400 without having read pages before it. A UI with numbered pages, or "go to the last page", cannot use keyset alone.
• No total. count(*) over a large table is itself a scan. The lab's Total query exists only to print the row count. Show "more" rather than "page 3 of 50,000", or keep an estimate (reltuples from pg_class) when an approximate number is acceptable.
• A total order is required. The sort key must be unique and stable. Sorting by created alone repeats or skips rows at ties. Always add the primary key as the last column, in the index and in the order by and in the comparison.
• Mixed directions are awkward. A row comparison needs every column to sort the same way. order by created desc, id asc cannot use a single row comparison, and the condition has to be spelled as an or of two branches, which the planner handles less well.
• Sorting by a nullable column needs care, since NULL sorts last by default and (a, b) < (null, ...) is not true. Avoid nullable sort keys, or coalesce them in the index.
• Changing data moves the ground. If rows are inserted ahead of the cursor between requests, keyset does not repeat or skip anything it had already shown, which is its advantage over OFFSET: with OFFSET, an insert at the top shifts every later row down by one, and the next page repeats a row.

When OFFSET is fine

OFFSET is not wrong. For small tables, shallow pages and admin screens it is the right answer, because it is the simplest and the one that supports page numbers. The lab's numbers say the cost is under half a millisecond for the first thousand rows, which is more depth than most people scroll. A rule that works: allow OFFSET where the page number is capped (say, 100 pages of 20) and use keyset for anything that can run unbounded, such as feeds, exports and "load more" lists. For a bounded OFFSET you can enforce the cap in the handler and return a 400 above it.

A third option sits between them, for UIs that need page numbers over a large table: keyset with a seek by page for the jump (read the key at the start of the target page using a narrow index only scan), and keyset from there. It is more code and rarely worth it.

Checklist

• Match the index to the order by, with the unique column last.
• Use (a, b) < ($1, $2) for a descending order, > for ascending, and the same direction on every column.
• Return an opaque cursor, and fetch one extra row to detect the next page.
• Keep OFFSET for bounded, shallow lists, and cap the page number.
• Do not show exact totals on large tables unless a cheap source exists.
• Measure on realistic row counts, as this article does, since a table of 10,000 rows hides the problem.

PostgreSQL's own explanation of the cost is in its LIMIT and OFFSET documentation, which says the rows skipped by OFFSET are still computed, and the comparison syntax in row constructor comparison. sqlc's named argument syntax is in its named parameters how-to.