Keyset Pagination in Go and Why Offset Gets Slow Quickly

Every list in an application eventually gets a "next page" button, and the first implementation is almost always limit 20 offset 40. It works on a small table and then degrades in a way that is easy to miss: page one is fast, so nobody notices that page 40,000 is not. It also returns wrong results when data changes while someone is paging. Keyset pagination, also called cursor or seek pagination, fixes both. This article measures them on a table of a million rows with Go 1.25.6 and PostgreSQL 18.6, and shows the details that make keyset correct: a unique sort key, row comparisons and an index that matches.

It is a technique from the database cornerstone, and its API side, how to encode a cursor into a response, is part of paginating an API.

What offset does

offset 800000 limit 20 does not jump to row 800,000. The database produces the rows in order, counts them, throws away the first 800,000 and returns the next 20. With an index on the sort key it avoids a sort, and it still walks 800,020 index entries and fetches the table rows to apply the offset. Work grows with the page number.

The experiment

package main

import (
	"context"
	"database/sql"
	"fmt"
	"log"
	"time"

	_ "github.com/jackc/pgx/v5/stdlib"
)

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

func timed(db *sql.DB, label, q string, args ...any) (last int64, ids []int64) {
	start := time.Now()
	rows, err := db.Query(q, args...)
	if err != nil {
		log.Fatal(err)
	}
	for rows.Next() {
		var id int64
		var s string
		if err := rows.Scan(&id, &s); err != nil {
			log.Fatal(err)
		}
		ids = append(ids, id)
		last = id
	}
	rows.Close()
	fmt.Printf("%-44s %-10v first id %d\n", label, time.Since(start).Round(10*time.Microsecond), ids[0])
	return last, ids
}

func main() {
	ctx := context.Background()
	db, err := sql.Open("pgx", dsn)
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()
	for _, q := range []string{
		"drop table if exists feed618",
		"create table feed618 (id bigserial primary key, created_at timestamptz not null, title text not null)",
		"insert into feed618 (created_at, title) select now() - (i || ' seconds')::interval, 'post ' || i from generate_series(1, 1000000) i",
		"create index feed618_created on feed618 (created_at desc, id desc)",
		"analyze feed618",
	} {
		if _, err := db.ExecContext(ctx, q); err != nil {
			log.Fatal(err)
		}
	}

	const size = 20
	fmt.Println("timing a page of 20 rows, ordered newest first, 1,000,000 rows")
	for _, page := range []int{1, 100, 5000, 40000} {
		timed(db, fmt.Sprintf("offset, page %d", page), "select id, title from feed618 order by created_at desc, id desc limit $1 offset $2", size, (page-1)*size)
	}
	// keyset: carry the last (created_at, id) of the previous page.
	var lastAt time.Time
	var lastID int64
	_ = db.QueryRow("select created_at, id from feed618 order by created_at desc, id desc offset $1 limit 1", (40000-1)*size-1).Scan(&lastAt, &lastID)
	timed(db, "keyset, the same position as page 40000", "select id, title from feed618 where (created_at, id) < ($1, $2) order by created_at desc, id desc limit $3", lastAt, lastID, size)
	_ = db.QueryRow("select created_at, id from feed618 order by created_at desc, id desc limit 1").Scan(&lastAt, &lastID)
	timed(db, "keyset, first page uses no cursor", "select id, title from feed618 order by created_at desc, id desc limit $1", size)

	// Stability: a new row arrives between page 1 and page 2.
	fmt.Println("a new post arrives between the first and second page")
	_, firstPage := timed(db, "page 1", "select id, title from feed618 order by created_at desc, id desc limit $1", size)
	var cur time.Time
	var curID int64
	_ = db.QueryRow("select created_at, id from feed618 where id = $1", firstPage[len(firstPage)-1]).Scan(&cur, &curID)
	if _, err := db.Exec("insert into feed618 (created_at, title) values (now() + interval '1 hour', 'breaking news')"); err != nil {
		log.Fatal(err)
	}
	_, offsetPage2 := timed(db, "offset page 2", "select id, title from feed618 order by created_at desc, id desc limit $1 offset $2", size, size)
	_, keysetPage2 := timed(db, "keyset page 2", "select id, title from feed618 where (created_at, id) < ($1, $2) order by created_at desc, id desc limit $3", cur, curID, size)
	seen := map[int64]bool{}
	for _, id := range firstPage {
		seen[id] = true
	}
	dupOffset, dupKeyset := 0, 0
	for _, id := range offsetPage2 {
		if seen[id] {
			dupOffset++
		}
	}
	for _, id := range keysetPage2 {
		if seen[id] {
			dupKeyset++
		}
	}
	fmt.Printf("rows of page 1 repeated on page 2: offset %d, keyset %d\n", dupOffset, dupKeyset)
}

timing a page of 20 rows, ordered newest first, 1,000,000 rows
offset, page 1                               1.8ms      first id 1
offset, page 100                             580µs      first id 1981
offset, page 5000                            15.29ms    first id 99981
offset, page 40000                           132.88ms   first id 799981
keyset, the same position as page 40000      4.56ms     first id 799981
keyset, first page uses no cursor            1.52ms     first id 1
a new post arrives between the first and second page
page 1                                       550µs      first id 1
offset page 2                                440µs      first id 20
keyset page 2                                590µs      first id 21
rows of page 1 repeated on page 2: offset 1, keyset 0

The program builds feed618, a million posts with a timestamp one second apart, indexed on (created_at desc, id desc), then times a page of 20 rows in several ways.

The first two offset rows are in the noise: page 1 paid for a cold cache and page 100 did not. The trend is the point. Page 5,000 is about 25 times slower than page 100, and page 40,000 is about 230 times slower. The keyset query for the same position as page 40,000 took 4.56ms, close to the first page, because it does not count anything: it goes straight to the position in the index. The more rows the table has and the deeper the page, the larger the gap, and on a table that does not fit in memory the offset case is worse still because the skipped rows must be read from disk.

How keyset works

The client sends back the sort key of the last row it saw, and the query asks for what comes after it:

select id, title from feed618
where (created_at, id) < ($1, $2)
order by created_at desc, id desc
limit $3

(created_at, id) < ($1, $2) is a row comparison: it means created_at < $1 or (created_at = $1 and id < $2) and PostgreSQL can use it as an index condition when the index is on the same columns in the same order. That is why the index is (created_at desc, id desc). The planner seeks to the cursor in the index and reads 20 entries forward.

The tie breaker is not optional. If two posts share a created_at and the key is only the timestamp, < $1 skips the second one, or <= $1 repeats the first. A unique final column, the primary key, makes the order total, so every row has exactly one position. In the lab the timestamps are unique per row anyway, and the habit matters the first time a bulk import writes a thousand rows with the same now().

In Go the loop is short: query with the cursor, scan the rows, and build the next cursor from the last row returned. For the first page there is no cursor, so use the query without the where clause, as the lab does for "keyset, first page uses no cursor". Keep two query strings rather than building one with a sentinel value like the zero time, which breaks the index use.

Stability under change

The last block of output shows the second bug of offset. After the first page was fetched, one new post arrived with a newer timestamp, so it sorted to the top of the feed. Then both styles asked for page two:

• Offset page 2 started at id 20, which was the last row of page 1: 1 row was repeated. Everything shifted down by one when the new row appeared at the front, so the row at position 20 is now at position 21.
• Keyset page 2 started at id 21, no repeats. It continues after the last row the client saw, whatever has been inserted before it.

With deletes the offset error goes the other way and rows are skipped, which is worse, since nobody sees that anything is missing. For feeds, logs, notifications and exports, keyset is correct, and offset is only approximately right.

What you give up

• No jumping to page N. A cursor leads to the next or previous page. Interfaces that need "page 37 of 214" cannot be built on it, though few users want page 37 of anything and search is a better fit for finding things.
• No total count for free. count(*) over a large table is its own cost. Show "more results" without a number, or maintain an approximate count (pg_class.reltuples) for display.
• Sorting is fixed to the index. Each sort order a user can pick needs its own index and its own cursor shape. Offer a few sort options and index each, rather than any column.
• Going backwards needs the comparison reversed and the order flipped, then the rows reversed in Go. It is easy to get wrong, so test it with a table whose ordering has ties.
• Cursors are tied to the data. If the sort key of a row can change (an updated_at order), a row can move between pages. Sort by something immutable such as the creation time and the id.

Treat the cursor as opaque to clients: encode the two values into a base64 string (JSON then base64.RawURLEncoding is common), and sign or validate it, so users cannot craft cursors that make the query do something unexpected. The values still travel as bind parameters, so there is no injection risk, only a correctness one.

When offset is fine

A small table, an admin screen used by a few people, or a result set capped at a few hundred rows can use offset without trouble, and the interface is simpler. The case against it is deep paging on big tables and anything with live inserts. If the page number is capped, say at 10, the worst cost is 200 rows and the rest of this article does not apply. To see why an index is or is not used by either query, read index basics and EXPLAIN. The technique and its trade-offs are described on Use The Index, Luke, and row value comparison is documented in the PostgreSQL manual's row constructor comparison.