API Pagination Strategies Compared: Offset, Keyset, and Cursor

Pagination is the one API feature where the wrong choice is invisible at 10,000 rows and catastrophic at 10 million. The three canonical strategies — offset, keyset, and cursor — look interchangeable from the client's side (they all return next_page and a list), but they differ in what they can index, how they behave under concurrent writes, and how much they cost the database. This article compares them against the only three things that matter: correctness under writes, index utilization, and total latency at scale.

Offset pagination: the default that breaks

?limit=50&offset=5000 maps directly onto SQL's LIMIT/OFFSET, which is why it's the first thing every framework generates:

SELECT * FROM orders
ORDER BY created_at DESC
LIMIT 50 OFFSET 5000;

Two structural problems. First, OFFSET is a scan, not a seek. Postgres and MySQL both compute it by fetching and discarding the first 5,000 rows — the query reads 5,050 rows to return 50. At offset 500,000 that's a full sequential scan of the ordered window, and the cost grows linearly with page number. An index on created_at does not rescue you; the index produces the order, then the engine still discards rows.

Second, OFFSET is unstable under writes. The offset is a position, not an identity. If a row is inserted at the top between page 1 and page 2, every subsequent page shifts by one: you see a row twice, or skip one entirely. Deletes do the same in the other direction. This is the classic "duplicate item on page 2" bug, and no index fixes it because the semantics are wrong, not the query.

Keyset pagination: the seek

Keyset pagination replaces the position with a predicate — "give me the 50 rows after the last row I saw":

SELECT * FROM orders
WHERE (created_at, id) < ('2026-08-18T10:00:00Z', 48211)   -- after the last seen row
ORDER BY created_at DESC, id DESC
LIMIT 50;

The tuple comparison (created_at, id) is the crucial detail: the id breaks ties between rows sharing a timestamp, and the compound index (created_at DESC, id DESC) serves the whole query as a straight index seek. Cost is O(log n) per page and constant per page regardless of depth — page 10,000 costs the same as page 2. Keyset is also stable under writes: new rows don't shift anything, because you're seeking by value, not position.

The client passes the cursor in the query string, typically the last item's key:

GET /orders?limit=50&cursor=2026-08-18T10%3A00%3A00Z_48211

Tradeoffs: the cursor leaks sort-column values (fine for timestamps, awkward for PII); you cannot jump to page N (no "page 37" links — fine for infinite scroll, wrong for numbered results); and it requires a sortable, unique, immutable column set.

Cursor pagination: opaque tokens

Cursor pagination is keyset with the mechanism hidden. The server encodes the last position into an opaque token — usually base64url of a JSON or binary payload — and the client just echoes it back:

type Cursor struct {
    CreatedAt time.Time `json:"c"`
    ID        int64     `json:"i"`
}
 
func encodeCursor(c Cursor) string {
    raw, _ := json.Marshal(c)
    return base64.RawURLEncoding.EncodeToString(raw)
}
 
func decodeCursor(token string) (Cursor, error) {
    raw, err := base64.RawURLEncoding.DecodeString(token)
    ...
}

GET /orders?limit=50&cursor=eyJjIjoiMjAyNi0wOC0xOFQxMDowMDowMFoiLCJpIjo0ODIxMX0

Cursor pagination gets you everything keyset has — index seeks, stability under writes — plus two hygiene wins: the token format can change without breaking clients (it's opaque), and you can version or sign tokens to invalidate stale ones. The cost is server-side complexity: token schema, expiry, and encoding bugs.

The comparison table

Choosing for your API

• Anything user-facing with writes: keyset or cursor. The duplicate-row bug alone is worth it.
• Numbered pages, admin tools, tiny datasets: offset is honest and simple.
• Public API you expect to evolve: cursor, with signed tokens if you need strict invalidation. GitHub, Stripe, and Slack all moved to cursor-style tokens for list endpoints for the same reason: behavior that doesn't degrade with page depth and doesn't corrupt under concurrent writes.

The server should always return a stable shape — {"items": [...], "next_cursor": "...", "has_more": true} — so clients never parse the cursor and never build URLs. And has_more beats a length check: clients should not guess "we're done" from a short page.

Measuring the difference

The gap is easy to verify on your own data. EXPLAIN ANALYZE the offset query at page 100,000 and the keyset query at the same depth; you'll see the offset plan's row count and execution time grow linearly while keyset stays flat. That single benchmark — depth vs. latency — is the whole article in one number.