Pagination at scale
Published
Why this, first. “How would you paginate a huge, constantly-changing collection?” separates engineers who’ve only used offset pages from those who’ve felt them break in production. Cursor pagination is the senior answer — and knowing exactly why offset fails, and what you give up to escape it, is what the question is really probing. This lesson makes both something you can draw.
Collections get huge
and are written to constantly
so offset goes slow + wrong
so you anchor to a cursor
?page=3&per_page=50 is trivial to ship and lets a human jump to any page. A cursor
remembers where you stopped on a stable, indexed key and range-scans forward.
Offset is not wrong — it’s narrow
For a small, mostly-static table behind a human-driven UI, page numbers are the right call. Don’t over-engineer a settings list. They earn their place on exactly the things a cursor can’t do:
Jump to any page Positional, so “Page 3 of 412” and skipping straight to page 200 just work — a cursor can only go next/prev.
Pairs with a total Naturally renders “412 pages,” which product and humans expect from a paginated table.
Trivial to implement LIMIT/OFFSET is one line of SQL; no key design, no
opaque token to encode.
Fine when small On a few thousand rows that barely change, neither failure below ever bites.
But it breaks on two axes a senior is expected to name without prompting — and both get worse exactly as the data gets bigger and busier.
Failure 1 · Cost grows with depth
The database can’t seek to row 1,000,000 — it must scan and discard every row up to the offset before returning your page. Cost is O(offset), so deep pages crawl while page 1 stays fast: a trap that hides in dev and surfaces under real traffic.
OFFSET 1000000 reads a million rows to hand you fifty — the work is in the rows you
skip, not the rows you keep.
Failure 2 · The window drifts under writes
Offsets are positional, not anchored to data. Insert one row at the head between two page requests and every offset slides by one: a row you already saw gets pushed forward and duplicated, or a row slips past your offset and is skipped. On a feed being written to constantly, every user hits this.
✓ Truth: under concurrent writes it’s also wrong — it silently duplicates and skips rows, independent of speed.
The senior answer: cursor / keyset
Instead of “skip N rows,” remember where you stopped using a stable, unique, ordered
key — typically a composite like (created_at, id) so ties break deterministically.
The next page is a range scan, not a skip:
WHERE (created_at, id) > (last_seen) ORDER BY created_at, id LIMIT n — the engine seeks to the anchor and reads forward,
then hands back an opaque next_cursor.
Two properties fall out, and they’re the exact inverse of offset’s two failures:
Stable under writes You anchor to a value, not a position. Inserts and deletes elsewhere can’t shift rows you’ve passed — no duplicates, no skips.
Cost independent of depth With an index on the sort key, the engine seeks to the anchor and reads forward. Page 20,000 costs the same as page 1.
The price · no jump-to-page You can only go next/prev from where you are. Volunteer this — it’s the trade-off the interviewer is listening for.
The price · stable sort required The key must be unique and the sort can’t change mid-walk; a cursor is only valid against the ordering it was minted under.
Hand the client an opaque cursor (e.g. base64 of the key) rather than raw column values, so you can change the encoding, add a tiebreaker, or migrate keys later without breaking clients who’ve stored one.
Offset is for pages a human clicks; cursors are for data a machine streams. The shape of the consumer decides the shape of the pagination — offset when someone scans a table of contents, cursor when something drains a firehose.
The count nobody warns you about
An exact total is expensive on a large table — there’s no shortcut; the engine scans (or counts an index) over the whole filtered set, and that cost grows without bound. The senior move is to not promise one on a billion-row feed.
Omit it Drop the total entirely. Most streaming consumers never render “page 3 of N” anyway.
Approximate it Return a statistics-based estimate — “~2.4M” — instead of a precise, costly scan.
Compute it lazily Move the exact count off the hot path: cache it, or calculate it asynchronously.
Use has_more
Fetch n+1 rows; if the extra one exists, there’s a next page — no counting at
all.
How the exemplars actually do it
GitHub defaults to page numbers and returns a Link header carrying
rel=“next”/“prev”/“last” URLs — clients follow links rather than building offsets
by hand — and offers cursor pagination (before/after)
on newer, high-volume endpoints where offset wouldn’t hold up.
1
Stripe is cursor-only: you page with
starting_after/ending_before (passing an object ID as the cursor) and
read a has_more flag to decide whether to keep going — no page numbers, no totals.
2
Link header — Link: <…&after=X>; rel=“next”
body stays clean, pure data; client follows URLs blind.
Envelope — { data:[…], next_cursor, has_more }
everything in the payload; clients often find it easier.
Either is defensible. Whichever you pick, keep sort and filter params stable across pages: a cursor is only meaningful against the exact ordering it was minted under, so changing sort or filter mid-walk invalidates the cursor. Treat sort + filter as part of the cursor’s contract.
♻️ Memory rule: Offset = skip-and-discard on a moving position → slow deep
- duplicates/skips under writes. Cursor = seek-and-read-forward on a stable key → flat
cost + consistent, at the price of no jump-to-page. Drop the total: omit, approximate, or use
has_more.
Retrieval practice
Close the lesson in your mind first, then answer from memory. Effortful recall is what converts “I read it” into “I own it.” Immediate feedback below.
Q1. Why does a deep OFFSET query get slow on a large table?
Q2. Under concurrent inserts, offset pagination can cause…
Q3. Cursor pagination's cost stays flat with depth because it…
Q4. The cursor returned to a client should be opaque so that…
Rehearse the answer out loud
Interviewer: "Design pagination for an activity feed with hundreds of millions of rows that's being written to constantly." ~60 seconds, from memory (no scrolling up) — then reveal and compare. Don't read the model first.
“I’d use cursor / keyset pagination on a stable composite key —
(created_at, id) — with a matching index. Each page is a range scan:
WHERE (created_at, id) < last ORDER BY created_at DESC, id DESC LIMIT n, and
I return an opaque cursor plus a has_more flag (and a next
link). I’d explicitly reject offset here: deep offsets scan-and-discard, so
cost grows with depth, and the window shifts under constant writes, which duplicates or
skips rows across pages.
I’d omit or approximate the total count — an exact count over hundreds of millions of rows is too expensive per request. I’d keep the sort and filter fixed for a cursor’s lifetime, since the cursor is only valid against the ordering it was minted under, and I’d not offer jump-to-page — for a streaming feed, next/prev is what consumers actually need.”
Why this scores: it picks cursor for the right reasons — consistency under writes plus depth-independent cost — and consciously drops the total count and jump-to-page rather than pretending they’re free. Naming the trade-off you’re accepting is the senior signal.
I’m your teacher — ask me anything. Want to work through the exact SQL for descending order with a composite tiebreaker, see how to encode an opaque cursor, or compare Link headers to an envelope in practice? Want me to grade your rehearsal answer or fire harder follow-ups? Just say so in the chat.
Primary source (read this next)
📖
Stripe API — Pagination: the cleanest production reference for cursor pagination done right —
starting_after/ending_before and has_more, no totals.
Then compare
GitHub’s Link-header approach
to see page-number and cursor styles side by side.
Interview — pick your bar
Answer out loud in ~60s, then reveal. Core = recall · Senior = trade-offs & failure modes · Staff = synthesis under ambiguity · System Design = open design round (a different axis, not a harder level).
What's the single biggest performance problem with deep offsets?
The engine can't seek to row 1,000,000 — it must scan and discard every row before the offset. OFFSET 1000000 LIMIT 50 reads a million rows to hand you fifty. Cost is O(offset), so deep pages crawl while page 1 stays fast.
What is cursor (keyset) pagination, in one sentence?
Instead of "skip N rows," you remember where you stopped using a stable, unique, ordered key, and the next page is a range scan from that anchor rather than a skip.
Why does cursor pagination's cost stay flat with depth?
With an index on the sort key, the engine seeks straight to your anchor and reads forward n rows. There's nothing to scan-and-discard, so page 20,000 costs the same as page 1.
Why hand the client an opaque cursor instead of raw column values?
So you can change the internals without breaking clients. If a cursor is just ?after_id=839, that ID becomes a public contract. Wrap the key in an opaque token (e.g. base64) and you can change the encoding, add a tiebreaker, switch sort keys, or migrate storage later — clients only ever echo the blob back.
Explain the consistency problem with offset pagination under concurrent writes.
Offsets are positional, not anchored to data. If a row is inserted before your window between two page requests, everything shifts down by one: a row you already saw is pushed into the next page and duplicated. If a row is deleted, rows slide up and one gets skipped entirely. On a feed being written to constantly, every user hits this.
Write the core keyset query and explain each piece.
WHERE (created_at, id) > (last_created_at, last_id) ORDER BY created_at, id LIMIT n. The WHERE seeks past the last row you returned; the composite (created_at, id) uses a tuple comparison so ties on created_at are broken deterministically by id; ORDER BY must match the index and the cursor's order exactly; LIMIT n bounds the page.
The product wants a total count on a huge feed. What are your options?
Three senior moves: omit it (most streaming clients don't need it); return an approximate count from table statistics ("~2.4M results"); or compute it lazily / off the hot path (cached, async, or a separate endpoint). The mistake is silently promising an exact count and eating an unbounded query on every request.
What does cursor pagination cost you compared to offset?
Two things you must volunteer: no jump-to-arbitrary-page (only next/prev from where you are — there's no "page 7" because there's no positional counting), and it requires a total, stable sort order that can't change mid-walk. Exact totals also become expensive. You trade addressability for consistency and flat cost.
The tuple comparison (a,b) > (x,y) — does every database optimize it as a single index range seek?
No — that's a real portability trap. Postgres treats row-value comparison as a clean range seek on the composite index. MySQL historically optimized the tuple form poorly, so people expand it by hand: WHERE created_at > ? OR (created_at = ? AND id > ?), which is logically identical but must be written carefully to stay sargable. Always EXPLAIN it on your engine; "it's keyset, so it's fast" is an assumption, not a guarantee.
How do filtering and sorting interact with cursor pagination?
The cursor is valid only for the filter+sort it was minted under, so each distinct sort order needs its own composite key and a supporting index — "sort by price" and "sort by date" are different walks with different cursors. Arbitrary user-chosen sort + filter combinations cause an index explosion; you either constrain sortable fields to an indexed allow-list, or accept that rarely-used sorts fall back to slower paths. Filters that narrow the set are fine as long as they stay constant across the walk and the index still supports the leading columns.
You inherit an offset API in production and need to migrate to cursors without breaking clients. How?
Run them in parallel: add cursor params (and a next_cursor in responses) alongside existing page/per_page — purely additive, no break. Steer new and high-volume clients to cursors, optionally cap offset depth to contain the worst queries immediately. Emit Deprecation/Sunset signals on the offset params, watch real usage, and only remove offset once traffic drains — a standard additive-evolution + deprecation lifecycle.
- Clarify the access pattern — is this an infinite-scroll feed, a jump-to-page admin table, or a full export? The consumer shape dictates the strategy.
- Pick a strategy by depth & write-rate — offset/limit for small, mostly-static, human-clicked data; cursor/keyset (seek) for large, growing, or constantly-written data and machine consumers.
- Define a stable total ordering — choose sort columns and add a unique tiebreaker (e.g.
id) so the order is total and deterministic; compare the tuple(sort_key, id). - Design the cursor — opaque token encoding the sort keys + direction (and ideally the sort/filter it was minted under); sign it if tampering could leak or rescope rows, version it so you can evolve the encoding.
- Answer the total-count question explicitly — exact (cheap only on small sets), approximate from statistics, or omit + use
has_morevia ann+1fetch. - State consistency under concurrent writes — name the drift/duplicate/missing-row failure modes and how the chosen strategy handles them; for true snapshots, use an
as_offilter or a changefeed. - Set limits & guardrails — default and max page size, clamp oversized limits, cap offset depth, and treat uncapped page size as a DoS/cost vector.
- Call out observability — watch deep-page / tail latency and cursor-invalid error rates so the cliff shows up in metrics, not just incidents.
Design pagination for an activity feed with millions of rows and constant inserts.
A strong answer covers: cursor/keyset on a stable composite key (created_at, id) with a matching index, each page a range scan WHERE (created_at, id) < last ORDER BY created_at DESC, id DESC LIMIT n returning an opaque cursor + has_more (fetch n+1). Explicitly reject offset: deep offsets scan-and-discard so cost grows with depth, and the window shifts under constant writes, duplicating or skipping rows. Omit or approximate the total — an exact count over millions of rows is too expensive per request. Keep sort and filter fixed for the cursor's life since it's only valid against the ordering it was minted under, don't offer jump-to-page (next/prev is what a streaming consumer needs), and cap page size. Name the trade-off you're accepting — that conscious drop of total + jump-to-page is the senior signal.
Design a paginated, filterable, sortable list endpoint that must also support jump-to-page in an admin UI.
A strong answer covers: start from the access pattern — a human-driven admin table that genuinely needs "Page X of Y" and arbitrary jump is offset-shaped, so offset/limit is defensible here even though it's the weaker default. Mitigate its two failure modes: cap depth (no page 10,000) and steer deep navigation through filters; use a deferred join (page the indexed key, then join back) to shrink the scanned width; and accept the under-writes drift as tolerable for a low-churn admin set, or add a stable sort tiebreaker to bound it. For the filter/sort surface, constrain sortable fields to an indexed allow-list to avoid index explosion, and keep each sort backed by a supporting index. Provide an approximate or cached total for the page count rather than an exact scan per request, and bound/clamp per_page. If the same data also feeds a machine/export consumer, expose a parallel cursor path for that surface rather than forcing one model on both.
📄 Keep handy:
Caching, pagination & versioning reference
◂ Prev ·
Next ▸ Lesson 7These notes reflect my current understanding and are updated as I learn, build, and discover better explanations.