Sooner or later, every API designer hits the pagination wall. The endpoint works fine in staging, then production traffic arrives with a table of twenty million rows, and suddenly “just return everything” stops being a design and becomes an incident. Pagination looks like the simplest feature in the world — pass a number, get a page — yet it is also one of the most common sources of subtle production bugs: duplicated rows, silently missing records, memory explosions, and queries that degrade from milliseconds to seconds as data grows. This post walks through the major pagination strategies, why offset pagination breaks down at scale, and how to build cursor-based pagination that stays correct under concurrent writes.
The choice matters more than most tutorials suggest, because pagination is not really about slicing data — it is about defining a stable iteration contract over a collection that keeps changing while the client reads it. Get the contract wrong and you ship APIs that occasionally skip or repeat records, and nobody can reproduce the bug because it only appears under concurrent load.
Offset pagination: familiar, convenient, and fragile
Offset pagination is the default everyone reaches for: the client asks for “rows 40 through 59” via limit and offset (or page and page_size) parameters. It is trivial to implement and it supports “jump to page 7,” which is why it remains everywhere — most list APIs, most admin panels, most ORMs default to it.
It has two structural problems. The first is instability under concurrent writes. The offset is a position, not a bookmark. If a row is inserted or deleted between two page requests, every subsequent row shifts, and pages either duplicate or skip items. Insert one row at the top of the table while a client is paging, and they will see the same record twice — once as the last item of page 1 and again as the first item of page 2. Delete a row mid-iteration and something gets silently skipped.
The second problem is quadratic cost on deep pages. Databases must scan and discard all rows before the offset, so page 5,000 of a 20-million-row table reads 100,000 rows to return 20. You can see this directly with PostgreSQL’s EXPLAIN ANALYZE:
-- Shallow page: fast, because only 20 rows are read
EXPLAIN ANALYZE
SELECT id, created_at FROM events
ORDER BY created_at DESC
LIMIT 20 OFFSET 0;
-- Deep page: the Offset step discards 100,000 rows that were
-- still fetched, sorted, and thrown away
EXPLAIN ANALYZE
SELECT id, created_at FROM events
ORDER BY created_at DESC
LIMIT 20 OFFSET 100000;Run this on a large table and the second query’s execution time grows roughly linearly with the offset, while the first stays flat. Some teams cap the maximum offset (GitHub’s REST API famously limits deep offsets), which is a reasonable pragmatic guard, but it concedes the point: offset pagination does not scale with depth. The PostgreSQL LIMIT/OFFSET documentation is explicit that the offset rows are computed and discarded, so performance degrades proportionally to the offset value.
Offset pagination remains a legitimate choice for small, mostly-static collections — admin tables, configuration lists — where users actually want to jump to an arbitrary page and the dataset fits comfortably in memory. The mistake is using it as the default for high-volume, append-heavy collections like event logs, activity feeds, or invoice lists.
Cursor pagination: the stable bookmark
Cursor pagination replaces the positional “row 40” with a semantic bookmark: “give me the next 20 items after this specific one.” The server returns an opaque cursor string alongside each page, and the client passes it back to fetch the next page. Because the cursor references a specific row rather than a position, concurrent inserts and deletes do not shift anything — you might not see brand-new items (which is usually fine and often desired), but you will never see duplicates or gaps within one iteration.
In SQL, this is keyset pagination: a WHERE clause on the sort key instead of an offset:
-- Page 1
SELECT id, created_at, payload FROM events
ORDER BY created_at DESC, id DESC
LIMIT 20;
-- Next page: continue strictly after the last row of page 1.
-- (1639,...) are the created_at and id of that row.
SELECT id, created_at, payload FROM events
WHERE (created_at, id) < (CAST(1639 AS timestamptz), 48211)
ORDER BY created_at DESC, id DESC
LIMIT 20;This uses PostgreSQL’s row-value comparison, which the planner can satisfy with a single index scan on (created_at DESC, id DESC) — no matter how deep you page, the cost stays flat. The tiebreaker column is not optional decoration: created_at alone is not unique, and ties on the sort key make iteration ambiguous. Always append a unique column (usually the primary key) as the final sort component. A matching composite index on (created_at, id) turns each page fetch into an index seek.
API design questions follow from the SQL reality. The cursor the server hands out should be opaque to the client: typically a base64 encoding of the keyset tuple. Opacity matters because clients that parse and mutate cursors couple themselves to your internal sort order; when you change the schema, every hand-rolled client breaks. Validate the cursor on decode — if it references a row that no longer exists or carries an old schema version, either restart the iteration cleanly or return a clear error rather than silently returning wrong data. Both Stripe’s pagination API and the GitHub REST pagination guide use cursor-based Link headers in production, and GitHub documents a 100-record cap for a single page precisely because deep-offset modes do not scale. JSON:API makes the contract explicit with page[after]-style query parameters, and its profile defines the cursor conventions many teams copy.
What you lose is random access. There is no “jump to page 7” with cursors, no reliable total count (more on that below), and no way for a client to show “page 3 of 47.” If your UI genuinely needs numbered pages over a large dataset, offset pagination plus a hard depth cap is an honest compromise — just know what you are trading.
Relay-style connections: the enterprise contract
GraphQL introduced its own canonical pagination shape, and it is worth understanding even if you never ship GraphQL, because it cleanly formalizes the concepts every cursor API needs. The Relay connection specification wraps results in a Connection containing edges (each edge carries the node plus a cursor) and a pageInfo object with hasNextPage, hasPreviousPage, and the first/last cursors of the page.
The design decisions are instructive:
- Cursors live on edges, not nodes. If your API later needs per-item metadata (position, relation type, permissions), the edge is the natural home without breaking the node shape.
pageInfo.hasNextPageanswers “should I keep asking?” without an extra count query. This is the entire anti-pattern cure for “SELECT COUNT(*) on every list request,” which is its own performance disaster on large tables.first/afterandlast/beforeare symmetric. Bidirectional iteration is a first-class concept, so chat histories and feeds can page backward without a second API.
The same page shape translates directly to REST — you can return pageInfo-style metadata in a list envelope and get most of the benefit. The GraphQL pagination tutorial walks through the motivation if you want the full reasoning.
The details that decide whether your API works
The strategy choice is maybe 20% of the work. The rest is the edge cases:
Total counts are a lie under concurrency. Clients love displaying “1,204 results.” But any count you compute is a snapshot of a moving target; displaying it next to iteration results that were fetched seconds apart guarantees visible inconsistencies. If you must show a count, cache it (approximately is fine), compute it asynchronously, or move to “load more” UX that never implies a total. Never run a COUNT(*) on the hot path of a list request over a large table.
Filtering changes the keyset. The moment clients can filter by category or status, your cursor must incorporate the filter context, and your index must match the filter-plus-sort combination. A cursor encoding (created_at, id) is only valid under the exact same WHERE clause it was issued with — encode the filter signature into the cursor or reject mismatched requests rather than returning pages computed under different predicates.
Sort-key mutation breaks iteration. If users can re-sort or items can change their sort key (say, a “last activity” timestamp that updates when someone replies), keyset pagination can skip or repeat items whose keys moved past the cursor. There is no perfect fix; either iterate on an immutable key (id), or accept and document that re-sorting restarts the iteration.
Enforce sane page sizes server-side. Always clamp limit to a maximum. An unbounded limit=1000000 is a denial-of-service vector against your database and your own response serializer, and it will be exercised — accidentally by an eager script if not maliciously. A silent clamp plus a response header stating the effective page size beats a 400 error for most public APIs, but whichever you pick, pick it deliberately.
Microsoft Graph documents the limits openly. For a mature example of edge-case handling at scale, the Graph paging documentation spells out server-driven paging, page-size caps, and how clients should resume when the service truncates a response — a good model for what your own API docs should eventually say.
A practical reference implementation
Here is a compact Go handler implementing cursor pagination with the conventions above — opaque base64 cursor, explicit tiebreaker, clamped page size, and hasNextPage computed by fetching one extra row:
package main
import (
"context"
"database/sql"
"encoding/base64"
"encoding/json"
"errors"
"net/http"
"strconv"
"time"
)
const (
maxPageSize = 100
defPageSize = 20
)
type Event struct {
ID int64 `json:"id"`
CreatedAt time.Time `json:"created_at"`
Payload string `json:"payload"`
}
type Page struct {
Data []Event `json:"data"`
NextCursor string `json:"next_cursor,omitempty"`
HasNext bool `json:"has_next_page"`
}
type cursor struct {
CreatedAt time.Time `json:"created_at"`
ID int64 `json:"id"`
}
func encodeCursor(c cursor) string {
raw, _ := json.Marshal(c)
return base64.URLEncoding.EncodeToString(raw)
}
func decodeCursor(s string) (cursor, error) {
var c cursor
raw, err := base64.URLEncoding.DecodeString(s)
if err != nil {
return c, errors.New("invalid cursor")
}
if err := json.Unmarshal(raw, &c); err != nil {
return c, errors.New("invalid cursor")
}
return c, nil
}
func listEvents(db *sql.DB) http.HandlerFunc {
return func(w http.ResponseWriter, r *http.Request) {
q := r.URL.Query()
// Clamp the page size; never trust client input.
limit := defPageSize
if v, err := strconv.Atoi(q.Get("limit")); err == nil && v > 0 {
limit = min(v, maxPageSize)
}
// Fetch one extra row purely to compute HasNext.
query := `SELECT id, created_at, payload FROM events`
args := []any{}
if raw := q.Get("after"); raw != "" {
c, err := decodeCursor(raw)
if err != nil {
http.Error(w, err.Error(), http.StatusBadRequest)
return
}
query += ` WHERE (created_at, id) < ($1, $2)`
args = append(args, c.CreatedAt, c.ID)
}
query += ` ORDER BY created_at DESC, id DESC LIMIT $` +
strconv.Itoa(len(args)+1)
args = append(args, limit+1)
ctx, cancel := context.WithTimeout(r.Context(), 5*time.Second)
defer cancel()
rows, err := db.QueryContext(ctx, query, args...)
if err != nil {
http.Error(w, "query failed", http.StatusInternalServerError)
return
}
defer rows.Close()
page := Page{Data: make([]Event, 0, limit)}
for rows.Next() {
var e Event
if err := rows.Scan(&e.ID, &e.CreatedAt, &e.Payload); err != nil {
http.Error(w, "scan failed", http.StatusInternalServerError)
return
}
page.Data = append(page.Data, e)
}
if err := rows.Err(); err != nil {
http.Error(w, "iteration failed", http.StatusInternalServerError)
return
}
if len(page.Data) > limit {
page.HasNext = true
page.Data = page.Data[:limit]
last := page.Data[len(page.Data)-1]
page.NextCursor = encodeCursor(cursor{CreatedAt: last.CreatedAt, ID: last.ID})
}
w.Header().Set("Content-Type", "application/json")
_ = json.NewEncoder(w).Encode(page)
}
}
func min(a, b int) int {
if a < b {
return a
}
return b
}
func main() {
db, err := sql.Open("postgres", "postgres://app:PASSWORD@localhost:5432/appdb?sslmode=disable")
if err != nil {
panic(err)
}
http.HandleFunc("/v1/events", listEvents(db))
if err := http.ListenAndServe(":8080", nil); err != nil {
panic(err)
}
}