Pagination in Go APIs With Offset Cursor and Keyset

Every list endpoint ends up paginated, and almost every first version is LIMIT 20 OFFSET 40 behind a ?page=3. It works in the demo, then a table grows, a feed gets new rows every minute, and someone writes a script that walks the whole collection. At that point offset paging does two bad things at once: it shows readers the same row twice, and it gets slower with every page, until the script that was supposed to take a minute takes an hour.

This article compares the three techniques on a real table, with numbers from a SQLite database. It shows offset paging failing under inserts, keyset paging fixing that, the subtle way keyset paging loses rows when the key is not unique, how to make a cursor opaque and tamper-proof, and what the response should look like. The pagination envelope is the one from structuring JSON responses, and the endpoint around it is built in the JSON API cornerstone. Everything ran on Go 1.25.6 and modernc.org/sqlite v1.50.0.

Three ways to page

Offset and limit is ?limit=20&offset=40 or its friendlier cousin ?page=3. It is easy to explain, supports jumping to any page, and maps to a single SQL clause. The database must still produce and discard the first 40 rows on every request.

Keyset (also called seek pagination) remembers where the last page ended and asks for the rows after that position: WHERE (created, id) < (?, ?). The database jumps straight to the position through an index, so every page costs the same.

Cursor is how you present a keyset to clients: an opaque string in the response that the client sends back to get the next page. A cursor does not have to be a keyset, but in practice it nearly always is. The name describes the contract, "here is a bookmark, return it to me", and keyset describes the query behind it.

The rest of this article uses one table, items, with an id, a created timestamp and a title. Four items share each second, so created is deliberately not unique, which is how real data behaves.

The database layer

package main

import (
	"database/sql"
	"fmt"
	"sync/atomic"

	_ "modernc.org/sqlite"
)

var dbs atomic.Int64

type Item struct {
	ID      int64  `json:"id"`
	Created int64  `json:"created"`
	Title   string `json:"title"`
}

// Open builds an in-memory table of n items. Four items share each second, so created is deliberately not unique.
func Open(n int) *sql.DB {
	db, err := sql.Open("sqlite", fmt.Sprintf("file:pg%d?mode=memory&cache=shared", dbs.Add(1)))
	if err != nil {
		panic(err)
	}
	db.SetMaxOpenConns(1)
	for _, q := range []string{
		`CREATE TABLE items (id INTEGER PRIMARY KEY, created INTEGER NOT NULL, title TEXT NOT NULL)`,
		`CREATE INDEX items_created_id ON items (created, id)`,
	} {
		if _, err := db.Exec(q); err != nil {
			panic(err)
		}
	}
	tx, _ := db.Begin()
	st, _ := tx.Prepare(`INSERT INTO items (created, title) VALUES (?, ?)`)
	for i := 0; i < n; i++ {
		st.Exec(1_700_000_000+int64(i/4), fmt.Sprintf("item %d", i))
	}
	st.Close()
	if err := tx.Commit(); err != nil {
		panic(err)
	}
	return db
}

func scan(rows *sql.Rows) []Item {
	defer rows.Close()
	out := []Item{}
	for rows.Next() {
		var it Item
		if err := rows.Scan(&it.ID, &it.Created, &it.Title); err != nil {
			panic(err)
		}
		out = append(out, it)
	}
	return out
}

// Newest first. The order is total because id breaks ties in created.
const order = ` ORDER BY created DESC, id DESC`

func OffsetPage(db *sql.DB, limit, offset int) []Item {
	rows, err := db.Query(`SELECT id, created, title FROM items`+order+` LIMIT ? OFFSET ?`, limit, offset)
	if err != nil {
		panic(err)
	}
	return scan(rows)
}

// KeysetPage asks for limit+1 rows so the caller can tell whether another page exists.
func KeysetPage(db *sql.DB, limit int, after *Cursor) []Item {
	q, args := `SELECT id, created, title FROM items`, []any{}
	if after != nil {
		q += ` WHERE (created, id) < (?, ?)` // row-value comparison; one index range scan
		args = append(args, after.Created, after.ID)
	}
	rows, err := db.Query(q+order+` LIMIT ?`, append(args, limit+1)...)
	if err != nil {
		panic(err)
	}
	return scan(rows)
}

// NaiveKeysetPage is the common mistake: a key that is not unique.
func NaiveKeysetPage(db *sql.DB, limit int, createdBefore int64) []Item {
	rows, err := db.Query(`SELECT id, created, title FROM items WHERE created < ?`+order+` LIMIT ?`, createdBefore, limit)
	if err != nil {
		panic(err)
	}
	return scan(rows)
}

OffsetPage is the version everyone writes first. KeysetPage takes an optional cursor, adds a WHERE (created, id) < (?, ?) when there is one, and asks for limit + 1 rows. The extra row is the cheapest way to know whether a next page exists: if the query returns more than limit rows, there is one, and the caller trims the extra before responding. A COUNT(*) for the same purpose scans the table or an index and is the thing to avoid on a large one.

The comparison (created, id) < (?, ?) is a row-value comparison. It means "created is less, or created is equal and id is less", written once, and SQLite and PostgreSQL can answer it with a single range scan on an index over (created, id), which the table has. Both columns are sorted in the same direction by ORDER BY created DESC, id DESC, which is what lets one index serve both directions of the walk. If you mix directions (created DESC, id ASC), the row-value shortcut stops being valid and you need the longer expanded form with explicit OR, and an index declared with the same mixed order. Check your own database's query plan with EXPLAIN before relying on it, since planners differ in how they treat row values.

NaiveKeysetPage is the mistake, kept in the lab so the damage can be measured.

Offset paging under change

The first demonstration inserts two rows between the first and second request.

package main

import (
	"fmt"
)

func ids(items []Item) []int64 {
	out := make([]int64, len(items))
	for i, it := range items {
		out[i] = it.ID
	}
	return out
}

func main() {
	db := Open(100)

	fmt.Println("== offset paging while a row is inserted between requests")
	p1 := OffsetPage(db, 5, 0)
	fmt.Println("page 1:", ids(p1))
	db.Exec(`INSERT INTO items (created, title) VALUES (1800000000, 'breaking news')`)
	db.Exec(`INSERT INTO items (created, title) VALUES (1800000001, 'more news')`)
	p2 := OffsetPage(db, 5, 5)
	fmt.Println("page 2:", ids(p2), " <- the last two of page 1 are shown again")

	fmt.Println("\n== keyset paging, same insert in the middle")
	db2 := Open(100)
	k1 := KeysetPage(db2, 5, nil)
	next := Cursor{k1[4].Created, k1[4].ID}
	fmt.Println("page 1:", ids(k1[:5]))
	db2.Exec(`INSERT INTO items (created, title) VALUES (1800000000, 'breaking news')`)
	db2.Exec(`INSERT INTO items (created, title) VALUES (1800000001, 'more news')`)
	k2 := KeysetPage(db2, 5, &next)
	fmt.Println("page 2:", ids(k2[:5]), " <- continues exactly where page 1 stopped")

	fmt.Println("\n== walking all 100 rows five at a time")
	walk := func(name string, fetch func(after *Cursor) []Item) {
		seen, pages := map[int64]int{}, 0
		var after *Cursor
		for {
			page := fetch(after)
			pages++
			n := len(page)
			if n > 5 {
				n = 5
			}
			for _, it := range page[:n] {
				seen[it.ID]++
			}
			if len(page) <= 5 {
				break
			}
			last := page[4]
			after = &Cursor{last.Created, last.ID}
		}
		fmt.Printf("%-28s pages=%d distinct rows=%d\n", name, pages, len(seen))
	}
	db3 := Open(100)
	walk("keyset on (created, id)", func(a *Cursor) []Item { return KeysetPage(db3, 5, a) })

	fmt.Println("\n== the non-unique key mistake: WHERE created < last.created")
	seen, before, pages := map[int64]bool{}, int64(1<<62), 0
	for {
		page := NaiveKeysetPage(db3, 5, before)
		if len(page) == 0 {
			break
		}
		pages++
		for _, it := range page {
			seen[it.ID] = true
		}
		before = page[len(page)-1].Created
	}
	fmt.Printf("pages=%d distinct rows=%d of 100 (rows tied at a page boundary were skipped)\n", pages, len(seen))

	fmt.Println("\n== the opaque cursor")
	c := Cursor{1700000024, 97}.Encode()
	fmt.Println("cursor:", c)
	_, err := Decode(c)
	fmt.Println("decode:", err)
	_, err = Decode("eyJjIjoxLCJpIjoxfQ.AAAAAAAAAAAAAAAA")
	fmt.Println("forged:", err)
}

== offset paging while a row is inserted between requests
page 1: [100 99 98 97 96]
page 2: [97 96 95 94 93]  <- the last two of page 1 are shown again

== keyset paging, same insert in the middle
page 1: [100 99 98 97 96]
page 2: [95 94 93 92 91]  <- continues exactly where page 1 stopped

== walking all 100 rows five at a time
keyset on (created, id)      pages=20 distinct rows=100

== the non-unique key mistake: WHERE created < last.created
pages=13 distinct rows=64 of 100 (rows tied at a page boundary were skipped)

== the opaque cursor
cursor: eyJjIjoxNzAwMDAwMDI0LCJpIjo5N30.2SuJYcSQWPFlO5D2
decode: <nil>
forged: invalid cursor

Look at the first two blocks. The offset client fetches page 1 and gets items 100 to 96. Two newer items arrive. The client asks for the second page with offset 5 and receives 97, 96, 95, 94, 93. Items 97 and 96 appeared twice, because the two inserts pushed everything down by two positions. Delete two rows instead and the reader skips two rows without knowing. For a feed that updates, this is the normal state of affairs, not a corner case.

The keyset client has the same two inserts happen and receives 95 to 91 next. It continues exactly where it stopped, because its request says "after item 96 at this timestamp", not "skip five rows". New items appear at the top where the reader started, and the reader does not see them until they refresh, which is the behavior people expect from a timeline.

The tied key mistake

The most common way to write keyset paging is WHERE created < ? with the timestamp of the last row. It looks right, and it loses data. The third block of output walks a hundred items five at a time with the correct key, (created, id), and gets all 100 in 20 pages. The fourth block walks the same table using only created, and returns 64 distinct rows in 13 pages. Whenever a page boundary fell in the middle of a group of rows that shared a timestamp, the rest of that group was excluded by created < last.created and never returned.

The fix is a tie-breaker: add a column that makes the sort order total, almost always the primary key. A unique order is a precondition for keyset paging. If you sort by price, the cursor is (price, id); by name, (name, id). If the sort column can be null, decide where nulls sort and encode that in the cursor, since NULL < x is not true in SQL and rows with nulls vanish from the walk in the same silent way.

The second thing the lost rows teach is that this failure does not raise an error. The output looked plausible, the pages were the right size, and nothing in the response suggests anything was skipped. Tests that walk a whole collection and count are the only reliable defence, and the test file below does exactly that.

An opaque cursor

A client should treat the cursor as a string it received and returns unchanged. That freedom lets you change the implementation later, from (created, id) to (score, created, id) or to a different table, without a new API version.

package main

import (
	"crypto/hmac"
	"crypto/sha256"
	"encoding/base64"
	"encoding/json"
	"errors"
	"strings"
)

// Cursor is what the next page resumes after. Clients never see this shape, only the opaque string.
type Cursor struct {
	Created int64 `json:"c"`
	ID      int64 `json:"i"`
}

var ErrBadCursor = errors.New("invalid cursor")

var secret = []byte("dev-only-signing-key")

func sign(payload string) string {
	m := hmac.New(sha256.New, secret)
	m.Write([]byte(payload))
	return base64.RawURLEncoding.EncodeToString(m.Sum(nil)[:12])
}

// Encode produces payload.signature, so a client that edits the cursor gets a 400 and not someone else's data.
func (c Cursor) Encode() string {
	b, _ := json.Marshal(c)
	p := base64.RawURLEncoding.EncodeToString(b)
	return p + "." + sign(p)
}

func Decode(s string) (*Cursor, error) {
	p, sig, ok := strings.Cut(s, ".")
	if !ok || !hmac.Equal([]byte(sig), []byte(sign(p))) {
		return nil, ErrBadCursor
	}
	b, err := base64.RawURLEncoding.DecodeString(p)
	if err != nil {
		return nil, ErrBadCursor
	}
	var c Cursor
	if err := json.Unmarshal(b, &c); err != nil {
		return nil, ErrBadCursor
	}
	return &c, nil
}

The cursor here is the JSON of the position, base64url-encoded, followed by a dot and a truncated HMAC-SHA256 of the payload. The output shows one cursor, eyJjIjoxNzAwMDAwMDI0LCJpIjo5N30.2SuJYcSQWPFlO5D2, decoding cleanly, and a forged one rejected as invalid cursor. The signature matters for a reason that is easy to underestimate: an unsigned cursor lets a client write their own position, and if the cursor carries a filter or a tenant, they can edit that too. Signing means the server only accepts cursors it issued. It does not hide the contents, since base64 is not encryption. If the position itself is sensitive, for instance a user id the client should not learn, encrypt the payload with an AEAD instead, as in envelope encryption and the crypto/cipher AEAD interfaces.

Three design choices to make with the cursor:

• Bind it to the query. If the client changes ?status=open to ?status=closed and reuses the cursor, the position is meaningless. Include a hash of the filter and sort in the payload and reject a mismatch with a 400.
• Give it an expiry if the position can become invalid. For plain keyset it never expires, since the key is a value rather than a row, and a deleted row does not break the cursor. For pagination that uses a snapshot, such as a search engine's scroll context, it does.
• Compare with hmac.Equal. The standard library's constant-time comparison avoids leaking the signature through timing, and it costs nothing here.

Tests that walk the collection

package main

import "testing"

func TestKeysetVisitsEveryRowOnceInOrder(t *testing.T) {
	db := Open(103)
	var after *Cursor
	var got []int64
	for {
		page := KeysetPage(db, 10, after)
		n := min(len(page), 10)
		for _, it := range page[:n] {
			got = append(got, it.ID)
		}
		if len(page) <= 10 {
			break
		}
		after = &Cursor{page[9].Created, page[9].ID}
	}
	if len(got) != 103 {
		t.Fatalf("got %d rows", len(got))
	}
	for i := 1; i < len(got); i++ {
		if got[i] >= got[i-1] {
			t.Fatalf("order broken at %d: %d then %d", i, got[i-1], got[i])
		}
	}
}

func TestForgedCursorIsRejected(t *testing.T) {
	good := Cursor{5, 5}.Encode()
	if _, err := Decode(good); err != nil {
		t.Fatal(err)
	}
	for _, bad := range []string{"", "x", "a.b", good + "0", good[:len(good)-2]} {
		if _, err := Decode(bad); err == nil {
			t.Errorf("%q was accepted", bad)
		}
	}
}

=== RUN   TestKeysetVisitsEveryRowOnceInOrder
--- PASS: TestKeysetVisitsEveryRowOnceInOrder (0.02s)
=== RUN   TestForgedCursorIsRejected
--- PASS: TestForgedCursorIsRejected (0.00s)
PASS
ok  	example.test/lab	1.386s

The first test pages through 103 items ten at a time, collects the ids and asserts there are 103, in strictly descending order. 103 is chosen on purpose to leave a short last page, since an off-by-one in the limit + 1 logic most often shows up there. The second test throws malformed cursors at the decoder, including a valid cursor with a character appended and one with a character removed. Run the walk test against your real database in CI (integration tests against a real database); a fake store with simple slice arithmetic would pass for any key.

What it costs

package main

import (
	"database/sql"
	"sync"
	"testing"
)

// Seeded on first use, so the unit tests do not pay for 200,000 rows.
var bigDB = sync.OnceValue(func() *sql.DB { return Open(200_000) })

func BenchmarkOffsetDeep(b *testing.B) {
	db := bigDB()
	b.ResetTimer()
	for b.Loop() {
		OffsetPage(db, 20, 180_000)
	}
}

func BenchmarkKeysetDeep(b *testing.B) {
	after := &Cursor{Created: 1_700_000_000 + 20_000/4, ID: 20_000}
	db := bigDB()
	b.ResetTimer()
	for b.Loop() {
		KeysetPage(db, 20, after)
	}
}

func BenchmarkOffsetFirstPage(b *testing.B) {
	db := bigDB()
	b.ResetTimer()
	for b.Loop() {
		OffsetPage(db, 20, 0)
	}
}

goos: darwin
goarch: arm64
pkg: example.test/lab
cpu: Apple M1 Max
BenchmarkOffsetDeep-10         	     151	   7936806 ns/op	    4281 B/op	     141 allocs/op
BenchmarkKeysetDeep-10         	   39009	     30700 ns/op	    4664 B/op	     150 allocs/op
BenchmarkOffsetFirstPage-10    	   48314	     24629 ns/op	    4264 B/op	     139 allocs/op
PASS
ok  	example.test/lab	4.231s

The table in the benchmark has 200,000 rows. The first page by offset costs 24.6 µs. A page at offset 180,000 costs 7.94 ms, about 320 times more, because SQLite walks and discards 180,000 index entries to reach it. The keyset page at the equivalent position, after item 20,000, costs 30.7 µs, close to the first page, because the index seek goes straight there. The ratio is roughly 260 to 1 at that depth.

Two caveats keep this honest. These numbers come from an in-memory SQLite on one machine, so the absolute values are not what your database will do; the shape of the result, flat for keyset and linear for offset, is what carries over. And most clients never ask for page 9,000. If your largest collection is a few thousand rows and humans read it, offset is fine. The cost appears when a client walks everything (an export, a sync, a crawler), which is exactly the client that tends to arrive after you have launched.

Designing the response

The envelope from the earlier article fits directly: {"data": [...], "next_cursor": "..."}, with next_cursor omitted on the last page. The handler asks the store for limit + 1 items, trims the extra, and if there was one, builds the cursor from the last item it is actually returning, not from the extra one. Using the wrong row is a classic off-by-one that skips one item at every boundary, and the walk test catches it.

A few practical rules for the parameters:

• Cap the page size. Default to 20 or 50, accept up to 100, return a 400 for more. An unbounded limit is a free denial of service.
• Name the parameters for what they are. limit and cursor are widely understood. If you keep offset for some endpoints, give it a different parameter so a client cannot confuse the two.
• Do not return a total by default. A count over a large filtered table is expensive and drifts as soon as it is computed. Offer it on request, as an optional pointer field. When a UI needs "page 3 of 40", that is the case for offset, and it is a legitimate one.
• Backward paging needs the reverse query. To go to the previous page, query with the comparison flipped and the order reversed, then reverse the result in memory. Return a prev_cursor only if your clients need it; most feeds only move forward.
• Say what is guaranteed. Document that results are ordered by a fixed key, that a client walking all pages sees every item that existed throughout the walk exactly once, and that items created during the walk may or may not appear. That is what keyset gives, and it is a better promise than offset can make.

Link headers are an alternative to putting the cursor in the body. GitHub's API does this with rel="next". It keeps bodies as bare arrays, at the price discussed in the response structure article: clients that only read the body cannot see the paging data. I put it in the body.

Sorting and filtering

A paginated endpoint almost always sorts and filters as well, and the three interact. The cursor must contain the sort key, so a sort=-price request produces cursors of the form (price, id). Sort columns must come from an allowlist and never from client text pasted into SQL, which is the subject of filtering and sorting without SQL injection. Filters narrow the set before the position is applied, and the index has to serve them: an index on (created, id) does not help a query filtered by owner_id, which wants (owner_id, created, id). Look at the plan for each combination you expose, and expose fewer combinations than you are tempted to.

For search results ranked by relevance, keyset still works if the score is stable; if it is not, the honest option is a scroll or snapshot cursor that fixes the result set up front, with an expiry, which is a different feature with its own limits.

Common mistakes

Paging by timestamp alone. The 64-of-100 result above is the proof. Add the id.

Computing the next cursor from the extra row. Take it from the last row you return.

Trusting the client's cursor. Sign it, bind it to the query, and reject it with a 400 when it fails.

Offset on a feed. Duplicates and gaps appear the first time the table changes between requests. Use keyset for anything live.

Counting every time. Make totals opt-in.

Different sort for the same cursor. Changing ORDER BY without invalidating old cursors sends clients to nonsense positions. Version the cursor format inside the payload when you change it.

The row-value syntax and index behavior are documented in the SQLite manual, and the database interface used here is in the database/sql package documentation. For the other pieces around a list endpoint, see filtering and sorting and caching API responses.