Swe Api Pagination Cursor Offset Keyset

Why Pagination Design Is a Real Engineering Decision

Pagination looks like a solved problem until an API serves a table with 10 million+ rows and a client requests page 50,000. At that scale, the pagination strategy you chose early on determines whether that request takes 8ms or 8 seconds. This makes pagination design a recurring API-design interview question in 2026 — not because it’s conceptually hard, but because most candidates only know one approach (LIMIT/OFFSET) and can’t explain why it breaks down.

Offset Pagination: The Default That Doesn’t Scale

SELECT * FROM orders ORDER BY created_at LIMIT 20 OFFSET 100000 looks innocent but forces the database to scan and discard 100,000 rows before returning the 20 you want, in most relational database query planners (Postgres, MySQL both do this — OFFSET is not an index seek, it’s a count-and-skip). At offset 100, this costs nothing. At offset 100,000, on a 10M-row table, this can take 200ms-2s depending on index coverage, and that cost scales linearly with the offset value, meaning deep pagination gets slower page by page, not just table by table.

Offset pagination has a second, subtler bug: it isn’t stable under concurrent writes. If a new row is inserted while a user is paginating (sorted by created_at DESC), everything shifts by one position, and the user either sees a duplicate row across two pages or skips one entirely. This is a correctness bug, not just a performance one, and it’s the detail that separates a surface-level answer from a strong one in interviews.

Keyset (Seek) Pagination: The Production Fix

Keyset pagination replaces OFFSET with a WHERE clause on the last-seen sort key: SELECT * FROM orders WHERE created_at < '2026-07-17T10:00:00Z' ORDER BY created_at DESC LIMIT 20. This is an index seek, not a scan-and-discard — with a B-tree index on created_at, this query costs roughly the same at row 20 as it does at row 10,000,000, because the database jumps directly to the position via the index rather than counting through prior rows.

Keyset pagination requires a tiebreaker column when the sort key isn’t unique (multiple orders can share a created_at timestamp at millisecond precision under load) — the standard fix is a compound key: WHERE (created_at, id) < (?, ?), using the primary key as a deterministic tiebreaker. Skipping the tiebreaker is the most common bug in keyset pagination implementations and causes duplicate or dropped rows under concurrent writes, exactly the problem keyset pagination is supposed to solve.

Cursor Pagination: Keyset Wrapped in an Opaque Token

Cursor pagination is not a fundamentally different mechanism from keyset — it’s keyset pagination with the seek values encoded into an opaque, typically base64-encoded token (eyJjcmVhdGVkX2F0IjoiMjAyNi0wNy0xNyIsImlkIjo0NTAxMn0=) returned to the client as next_cursor, rather than exposing raw column values in the URL. This is the pattern used by Stripe, GitHub, and virtually every modern public API in 2026.

The opacity serves two purposes beyond aesthetics: it lets you change the underlying sort/filter implementation without breaking API consumers (since clients never parse the cursor themselves), and it prevents clients from constructing arbitrary offsets or guessing internal IDs. Cursors are frequently HMAC-signed or encrypted specifically to prevent tampering — a client modifying a decoded cursor to skip permission checks (e.g., changing an embedded tenant_id) is a real security bug pattern that’s shown up in multiple public API vulnerability disclosures.

Comparison Table

The tradeoff is explicit: offset pagination is the only approach that supports random-access “jump to page 50” UX (common in admin dashboards, search result pages with numbered links), while keyset/cursor pagination only supports “next/previous” sequential navigation but scales to arbitrary depth. Public APIs overwhelmingly choose cursor pagination because API consumers rarely need random page access and the correctness+performance wins dominate; internal admin UIs sometimes keep offset pagination specifically because numbered pages matter to the human using them, at a scale where the table is small enough (under ~100K rows) that the performance cost never materializes.

Hybrid Approach: Offset for Shallow Pages, Keyset Beyond a Threshold

Some production systems (notably large e-commerce search backends) use a hybrid: offset pagination for the first N pages (say, 1-20, matching typical user behavior where 95%+ of users never go past page 5-10), and force a “load more” or cursor-based continuation beyond that. This sidesteps building full keyset support for a UX pattern (jump to page 47) that almost nobody uses, while avoiding the O(offset) blowup for the pathological case (bots, scrapers, or power users paging deep). This kind of pragmatic tradeoff reasoning — not picking “the theoretically correct answer” but the one matched to actual usage data — is exactly what distinguishes a senior engineer’s answer in an API design interview from a junior one that recites keyset pagination as universally superior without qualification.

This exact tradeoff table and the follow-up questions (“what if the sort key isn’t unique,” “how do you handle cursor tampering”) are worked through with sample schemas in The 0-to-1 SWE Interview Playbook — available on Amazon.

FAQ

Q: Why does OFFSET get slower the deeper you paginate? Because most relational databases implement OFFSET as “scan and discard N rows before returning results,” not as an index seek to position N. The cost is proportional to the offset value regardless of how many rows you ultimately return.

Q: Does keyset pagination support “jump to page 50”? No. Keyset/cursor pagination is inherently sequential — you can only page from a known position (the last row you saw) forward or backward. If random page access is a hard product requirement, you need offset pagination or a hybrid (e.g., pre-computed page boundaries cached separately).

Q: What happens if the sort column has duplicate values in keyset pagination? Rows can be skipped or duplicated across pages unless you add a unique tiebreaker column (typically the primary key) to the WHERE clause and ORDER BY, forming a compound seek condition like WHERE (sort_col, id) < (?, ?).