Every API that returns a list eventually has to answer the same question: how do you hand out a million rows twenty at a time? The default answer most of us reach for — page numbers and OFFSET — works beautifully right up until the dataset gets large or the data gets busy. Then it fails in two distinct ways at once: queries get slower the deeper clients page, and the pages themselves quietly shift underneath the reader as rows are inserted and deleted. Cursor-based (keyset) pagination fixes both problems, but it does so by trading away conveniences you might not realize you rely on.
This post walks through the full design: why offset degrades, how keyset seeks work at the index level, the composite-cursor pattern that keeps ordering stable, how to wrap the whole thing in an opaque token your clients can’t misuse, and a working Go implementation against PostgreSQL. The pitfalls section at the end covers the details that bite in production — NULLs, non-unique sort keys, and filters that break your index.
The Two Ways Offset Fails
The performance problem first. OFFSET 10000 LIMIT 20 does not mean “jump to row 10,001.” It means “produce rows 1 through 10,020, throw the first 10,000 away.” The database still has to visit every skipped row — in an index scan, that’s 10,000 index-entry traversals plus heap fetches before the first useful row comes back. Page 1 is fast, page 50 is slower, page 500 is a timeout waiting to happen. The cost grows linearly with depth, and no amount of caching fixes the geometry.
The correctness problem is sneakier. Offsets are positions, not identities. Suppose a client is reading page 3 (rows 41–60) and someone inserts a new row near the top of the result set. Every row shifts down by one; the client’s next request for page 4 silently skips what used to be row 61 and shows 62 onward — one item lost. A deletion in the same window causes a duplicate instead. On a busy feed, inserts happen continuously, so deep pages are not just slow, they’re wrong in ways neither the client nor the server can detect from the payload alone.
Keyset Mechanics: Seek, Don’t Skip
Keyset pagination replaces “give me row 10,001” with “give me the next 20 rows after this one,” where “this one” is the last row of the previous page, identified by its sort-key values. For a feed ordered newest-first, that reads:
SELECT id, title, created_at
FROM posts
WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT 20;The (created_at, id) < ($1, $2) form is a row-value comparison: lexicographic tuple comparison, exactly like comparing tuples in most programming languages. It’s shorthand for created_at < $1 OR (created_at = $1 AND id < $2), and PostgreSQL can use it as an index condition directly. The two-column predicate matters because created_at alone is not unique — two posts published in the same transaction share a timestamp, and a single-column boundary would make rows flip between pages nondeterministically. The id tiebreaker guarantees a total order.
The performance story hinges on the index. With a composite index matching the sort tuple, the predicate becomes a seek: the engine positions the scan directly after the boundary values and reads 20 entries. Cost is constant regardless of depth — page 1 and page 5,000 do identical work. Note that the index must match the sort direction; for the query above:
CREATE INDEX idx_posts_keyset
ON posts (created_at DESC, id DESC);Always confirm with EXPLAIN ANALYZE that you’re getting an index scan, not a sort-plus-scan. If the plan shows an explicit Sort node above the scan, your index doesn’t match the query’s ordering and the constant-time property is gone — you’re paying a full sort on every page request. (The id direction rarely matters for correctness, since the PK is unique, but matching both columns keeps the plan unambiguous.)
Cursors Are an API Contract, Not a Database Value
Resist the temptation to expose ?after=2026-09-01T10:00:00Z directly. A raw sort-key value in the URL invites clients to construct their own boundaries, which couples them to your internal sort order forever. The better contract — the one the GraphQL connection specification formalized — treats the cursor as an opaque token: the server encodes the boundary values plus enough metadata to interpret them, and the client echoes it back untouched.
A pragmatic encoding is base64 over a small JSON object that carries a version field:
package cursor
import (
"encoding/base64"
"encoding/json"
"fmt"
"time"
)
type Cursor struct {
V int `json:"v"` // format version
CreatedAt time.Time `json:"ts"` // boundary: sort key
ID int64 `json:"id"` // boundary: tiebreaker
}
func Encode(c Cursor) (string, error) {
raw, err := json.Marshal(c)
if err != nil {
return "", fmt.Errorf("marshal cursor: %w", err)
}
return base64.RawURLEncoding.EncodeToString(raw), nil
}
func Decode(s string) (Cursor, error) {
raw, err := base64.RawURLEncoding.DecodeString(s)
if err != nil {
return Cursor{}, fmt.Errorf("bad cursor encoding: %w", err)
}
var c Cursor
if err := json.Unmarshal(raw, &c); err != nil {
return Cursor{}, fmt.Errorf("bad cursor payload: %w", err)
}
if c.V != 1 {
return Cursor{}, fmt.Errorf("unsupported cursor version %d", c.V)
}
return c, nil
}Three design notes on this little type. First, the version field: when you eventually change the sort order — adding a column, flipping direction — old cursors already sitting in clients’ URL bars must fail loudly and cleanly, not decode into a semantically different boundary. Second, validation errors should map to a 400-class response with a hint to restart pagination, because a stale cursor after a sort-order change is a client-recoverable condition. Third, opacity is a side effect, not the goal: the real win is that the server, not the client, decides what a boundary means.
The Endpoint: Putting It Together in Go
Here is the HTTP handler. It decodes the cursor, runs the keyset query, fetches one extra row to compute has_more without a separate count query, and returns the next cursor:
package main
import (
"database/sql"
"encoding/json"
"net/http"
"time"
"example.com/api/cursor"
_ "github.com/jackc/pgx/v5/stdlib"
)
type post struct {
ID int64 `json:"id"`
Title string `json:"title"`
CreatedAt time.Time `json:"created_at"`
}
type pageResponse struct {
Items []post `json:"items"`
NextPage *string `json:"next_page,omitempty"`
}
func listPosts(db *sql.DB) http.HandlerFunc {
const pageSize = 20
return func(w http.ResponseWriter, r *http.Request) {
var (
boundaryTS time.Time
boundaryID int64
)
q := r.URL.Query().Get("cursor")
if q != "" {
c, err := cursor.Decode(q)
if err != nil {
http.Error(w, "invalid cursor; restart pagination", http.StatusBadRequest)
return
}
boundaryTS, boundaryID = c.CreatedAt, c.ID
} else {
boundaryTS = time.Now().Add(time.Hour) // start: newer than anything
boundaryID = 0
}
// pageSize + 1 rows: the extra row answers has_more without COUNT(*).
rows, err := db.QueryContext(r.Context(), `
SELECT id, title, created_at
FROM posts
WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT $3`, boundaryTS, boundaryID, pageSize+1)
if err != nil {
http.Error(w, "query failed", http.StatusInternalServerError)
return
}
defer rows.Close()
items := make([]post, 0, pageSize)
for rows.Next() {
var p post
if err := rows.Scan(&p.ID, &p.Title, &p.CreatedAt); err != nil {
http.Error(w, "scan failed", http.StatusInternalServerError)
return
}
items = append(items, p)
}
if err := rows.Err(); err != nil {
http.Error(w, "iteration failed", http.StatusInternalServerError)
return
}
resp := pageResponse{Items: items}
if len(items) > pageSize {
items = items[:pageSize]
last := items[pageSize-1]
token, err := cursor.Encode(cursor.Cursor{
V: 1,
CreatedAt: last.CreatedAt,
ID: last.ID,
})
if err != nil {
http.Error(w, "cursor encode failed", http.StatusInternalServerError)
return
}
resp.NextPage = &token
}
w.Header().Set("Content-Type", "application/json")
if err := json.NewEncoder(w).Encode(resp); err != nil {
return
}
}
}
func main() {
db, err := sql.Open("pgx", "postgres://app:YOUR_PASSWORD@localhost:5432/appdb")
if err != nil {
panic(err)
}
http.HandleFunc("/posts", listPosts(db))
if err := http.ListenAndServe(":8080", nil); err != nil {
panic(err)
}
}The LIMIT pageSize + 1 trick deserves a callout: counting total rows to decide whether another page exists is one of the most common pagination performance mistakes, and on large tables a COUNT(*) per page request can cost more than the page query itself. Fetching one extra row answers the question for free. It also sidesteps the awkward fact that “total” is unstable in a live dataset anyway — the count can change between page requests, which makes any pagination UI built on it subtly lie.
The Pitfalls That Bite in Production
- Non-unique sort keys. Already covered, but worth repeating because it’s the most common bug: every keyset ordering needs a unique tiebreaker column appended, or rows with equal sort values will appear on two pages or on none.
- NULLs in the sort column. In PostgreSQL, NULL sorts after non-NULL values in ascending order by default, and
NULL < anythingis never true — so rows with NULL sort keys simply vanish from keyset queries. Either make the columnNOT NULLwith a default, orCOALESCEin both the predicate and the ordering (and index the expression). - Filters that don’t match the index. A keyset query filtered by
statusneeds the filter column leading the index:(status, created_at DESC, id DESC). With equality-only filters, column order among the leading equality columns is flexible, but the sort columns must come last and in sort order. - Backward pagination. “Previous page” is the same query with the comparison flipped:
(created_at, id) > ($1, $2), ascending order, then reverse the result before returning it. The Relay convention offirst/afterpluslast/beforeparameters exists precisely to make this symmetric at the API level. - Client-constructed cursors. If cursors are opaque and validated, a corrupted one is a 400. If they’re raw timestamps, a corrupted one is a silent scan from the wrong position — and eventually a support ticket.
When Offset Is Still Fine
Cursor pagination has real costs: no page numbers, no jump-to-page, no stable total count, and clients that must hold state between requests. If the dataset is small (thousands of rows, not millions), the list is append-mostly and read shallowly, or the UI genuinely needs page-number navigation — an admin back-office, for example — offset pagination is the simpler and perfectly adequate choice. The mistake isn’t using offset somewhere; it’s using it everywhere by default and discovering the linear-scan tax from a production timeout graph.
The decision rule: any list endpoint that clients crawl deeply, that sits on a large table, or that mutates while being read should be keyset from day one. Retrofitting cursors after clients have built UIs on page numbers is a much harder conversation than shipping them upfront — and the composite index you need is the same one a well-tuned offset deployment would want anyway.