Pagination at scale — interview questions

You translate a page request — ?page=3&per_page=50 — into LIMIT 50 OFFSET 100. The database orders the rows, skips the first OFFSET of them, and returns the next LIMIT. Trivial to implement, and it pairs naturally with a total count so you can render "Page 3 of 412."

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.

Small or mostly-static data behind a human-driven UI where jump-to-arbitrary-page and a "Page X of Y" total are genuinely useful — a settings list, an admin table, search results a person scans. Don't over-engineer a cursor where someone is clicking a table of contents.

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.

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.

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.

They're the two directions of a walk. after fetches the page following a cursor (forward / next); before fetches the page preceding it (backward / prev). Together they give bidirectional paging without offsets — GitHub exposes both, Stripe uses starting_after / ending_before.

There's no shortcut: to count a filtered set the engine must scan (or count an index over) the whole matching set, and that cost grows without bound as the table grows. On a billion-row feed, computing "of N total" on every page is often more expensive than the page itself.

A has_more boolean. Fetch n+1 rows; if the extra one comes back, there's a next page, so you return n rows and has_more: true (and drop the spare). One cheap extra row replaces a full count.

Ask who consumes it. If it's machine traffic or an infinite-scroll feed over large/growing data, push for cursor from day one — retrofitting it later is painful once clients depend on page numbers. If it's a small human-scanned table that needs jump-to-page, offset is fine. Decide by access pattern, not habit.

Because it cleanly separates engineers who've only used offset pages from those who've felt them break in production. Reaching for cursors and naming exactly why offset fails — O(offset) cost and the shifting-window inconsistency — plus what you give up (jump-to-page, cheap totals), is the senior signal.

Because it hides in development. You test page 1 and page 2 — both fast. The cost only shows up at depth and at scale, exactly the conditions you don't reproduce locally. It surfaces as a tail-latency cliff under real traffic, often via crawlers or scripts walking to deep pages, long after the design is locked in.

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.

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.

You anchor to a value, not a position. Inserts and deletes elsewhere in the table can't shift the rows you've already passed — your WHERE still says "give me rows after this key." So across the pages you walk forward, no duplicates and no skips.

Because it isn't unique — many rows can share a timestamp. If you page on created_at alone, rows at a boundary timestamp get split unpredictably across pages and can be skipped or repeated. You need a total stable order, so you add a unique tiebreaker (id) and compare the tuple (created_at, id).

A composite index on the exact sort tuple in the exact order/direction — e.g. (created_at, id) (or both DESC for newest-first). Without it the "seek" degrades to a sort or scan and you lose the whole benefit. The index, the ORDER BY, and the cursor key must agree.

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.

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 values needed to reconstruct the WHERE on the next request: the sort key plus the unique tiebreaker of the last row returned — e.g. {created_at, id} — base64-encoded. Many APIs also fold in the sort and filter the cursor was minted under (or a hash of them) so a mismatched reuse can be rejected rather than silently returning garbage.

A cursor is only meaningful against the exact ordering it was minted under — it says "the row after this position in this sequence." Change the sort or filter mid-walk and that position points at nothing meaningful: you get skips, dups, or nonsense. Treat sort + filter as part of the cursor's contract; if they change, the client must start a fresh page-walk.

Fail loudly and predictably — return a 400 with a clear "cursor invalid, restart pagination" error rather than silently coercing it into a wrong window. Because cursors are opaque, clients are built to handle "start over," so a clean error is far safer than quietly returning duplicates or gaps the client can't detect.

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.

Link headers (GitHub: rel="next"/"prev"/"last") keep the body pure data and let the client just follow URLs — clean separation, HTTP-native. An envelope — { data: [...], next_cursor, has_more } — puts pagination state in the payload, which clients often find easier to consume and works regardless of header handling. Either is defensible; pick one and be consistent.

GitHub defaults to page numbers and returns a Link header with next/prev/last URLs, and offers before/after cursors on newer, high-volume endpoints. Stripe is cursor-only: starting_after / ending_before take an object ID as the cursor, and you read a has_more flag — no page numbers, no totals.

The server must cap and default it — e.g. default 50, max 100 — and clamp anything larger rather than honoring per_page=1000000. Page size is a resource-consumption and latency lever a client shouldn't control unbounded; an uncapped limit is a trivial DoS and a memory/serialization blowup.

Cursor/keyset 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, 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. Keep sort and filter fixed for the cursor's life, and don't offer jump-to-page — next/prev is what a streaming consumer needs.

For "prev," reverse the comparison and the order: instead of (k) < last ... ORDER BY DESC you run (k) > first ... ORDER BY ASC LIMIT n, then reverse the result set back to display order before returning. You typically emit two cursors per page — one for each edge (GitHub's before/after, Relay's startCursor/endCursor) — so the client can walk either way.

Plain keyset gives "no dups/skips among rows you've passed," but rows can still appear or vanish ahead of your cursor. For a true point-in-time snapshot, options ladder up in cost: page by an immutable key with an as_of timestamp filter (WHERE created_at <= snapshot_ts) so later writes are invisible; use a repeatable-read/snapshot transaction for short exports (doesn't survive a long client-paced walk); or for very large jobs, do a bulk export / changefeed instead of live pagination. Match the mechanism to how long the walk lasts.

They're the two general-purpose ones, but keyset is really a family. Time-window paging (since/until on a timestamp) is keyset specialized to a time axis — great for logs and changefeeds. Seek-by-value ("items after id=X") is keyset with the client naming the anchor. The common thread is anchoring to a value in a stable order; "offset vs cursor" is the headline, but the right framing is positional vs value-anchored.

Partly, with hybrids. You can cap depth (most UIs never need page 10,000) and force deep navigation through filters instead. You can use a deferred join — page the indexed key column only, then join back for the full rows — to shrink the scanned width. And for "page N" specifically you can sometimes seek by a known boundary value rather than a count. But none of these recover both jump-to-page and flat cost; that's the trade you accept by choosing offset.

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.

It depends on threat model. Base64 alone is just obfuscation — a curious client can decode it. Sign it (HMAC) if a tampered cursor could leak rows or skip authorization scoping; encrypt it if the embedded key itself is sensitive. Expiry is reasonable when cursors imply a snapshot you can't hold open forever (you return a clear error so clients restart). The default is opaque + signed; reserve encryption and short TTLs for when the cursor carries real risk.

It's a deliberate trade. The object ID is already public (you're listing those objects), so it leaks nothing new, and it makes cursors human-debuggable and intuitive — "page after this charge." The cost is that the ID's role in ordering becomes part of the contract. It works for Stripe because their objects have a stable creation order keyed by ID; a general API with mutable sort keys is usually better served by a true opaque cursor that can hide a composite key.

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.

Cursors are inherently relative, not addressable, so there's no canonical "page 5." Options: link to a cursor instead of a page number (a permalink that means "from this anchor onward" — stable as long as that row exists); or, if the product truly needs human page numbers, link to a filter that re-anchors (e.g. "items after this date"). If genuine arbitrary jump-to-page is a hard requirement, that's a signal the access pattern is offset-shaped — and you accept offset's costs for that surface, possibly with a depth cap.

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.

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.

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.