APIs
Pagination: multiple-choice review
Crux Multiple-choice synthesis across the pagination unit: offset-vs-keyset cost, page drift under writes, stable ordering, opaque cursors, and the hidden count tax.
Your altitude — climbing toward senior
ZeroJuniorMiddleSenior
You are at senior altitude — in orbitSix questions that span the whole unit. None is a definition to recite — each is a call you make designing a real list endpoint that has to survive a 50-million-row table and constant writes.
Goal
Confirm you can connect offset cost, page drift, ordering stability, the opaque-cursor contract, and the count tax — the synthesis the lesson built toward, not the individual facts.
Quiz
Completed
An endpoint serves LIMIT 20 OFFSET 200000 against a 50M-row table. It returns 20 rows but takes ~8 seconds. Why does cost grow with the offset and not the page size?
Heads-up The payload is always 20 rows; response size is constant. The cost is the 200000 rows the engine reads and throws away to reach them — invisible in the response.
Heads-up The index can still order the rows; the engine just has to walk past every offset row to count them off. It is scan-and-discard work, not a missing index.
Heads-up OFFSET is a plain read and takes no such lock. The latency is the linear read-and-discard work proportional to the offset value.
Quiz
Completed
On a fast-moving feed, users report seeing the same post twice and others swear posts 'disappeared.' The frontend is correct and the table is small. What is the cause?
Heads-up No cache is in play, and the symptom appears the moment two requests straddle a single write. The drift is intrinsic to positional pagination, not a cache.
Heads-up Page size does not cause drift; any size has the same positional boundaries. The boundary moving under inserts/deletes is what duplicates and skips rows.
Heads-up Duplicates and skips are a real correctness bug from positional pagination. Keyset anchoring on a value eliminates them.
Quiz
Completed
A keyset feed orders by created_at DESC only and carries the last created_at forward as the cursor. Occasionally a post is skipped or shown twice at a page seam. Why, and what fixes it?
Heads-up Timestamps compare correctly as values. The defect is non-uniqueness: ties at the page seam break the seek unless you add a unique tiebreaker like the primary key.
Heads-up Ordering by id alone abandons the newest-first order the product wants. Keep created_at and append id as the unique tiebreaker so both ordering and stability hold.
Heads-up A bigger page does not make the key unique; ties can still straddle any boundary. The fix is a unique composite sort key, not a larger page.
Quiz
Completed
A product needs an admin table with 'jump to page 4,000 of 80,000' AND a public infinite-scroll feed over the same data. What is the right call?
Heads-up Keyset cannot jump to an arbitrary page N; it only walks next/prev. The admin 'jump to page 4000' requirement is exactly offset's one real advantage.
Heads-up Offset on the public feed reintroduces deep-page slowness and drift under writes — the failures the unit is about. Match the strategy to each surface's access pattern.
Heads-up Counting cursors to fake page numbers still needs total position, which is the offset scan you were avoiding. Arbitrary jumps are offset's domain by design.
Quiz
Completed
A teammate proposes returning the raw (created_at, id) tuple as the cursor 'since the client just echoes it back anyway.' Why prefer an opaque encoded cursor?
Heads-up Decoding cost is negligible either way. The reason is contract evolution and preventing hand-crafted cursors — not decode speed.
Heads-up A tuple serializes fine over JSON. The point is that exposing internal keys couples the wire contract to your schema and lets clients craft arbitrary seeks.
Heads-up Opacity has nothing to do with arbitrary jumps; keyset still only walks next/prev. Opacity protects the contract and constrains client-crafted cursors.
Quiz
Completed
The endpoint is now fast via keyset, but p99 is still high because of the 'Showing 1–20 of 4,812,339' footer. Why is the exact count expensive in Postgres, and what is the senior move?
Heads-up COUNT(*) does not lock; it is a read. The cost is the MVCC-driven row walk to determine visibility, a scan whose cost grows with the table.
Heads-up Postgres cannot read an exact visible count from metadata precisely because MVCC gives each transaction a different visible set. reltuples is an estimate, not exact metadata.
Heads-up An index does not give an exact visible count under MVCC; the engine still walks rows for visibility. The fix is to estimate or use LIMIT n+1, not to index harder.
Recap
The unit’s through-line is one decision: address pages by position (offset) and you pay twice — deep pages scan-and-discard linearly, and positional boundaries drift under writes into duplicates and skips. Address them by value (keyset) over a unique, stable, indexed key and latency stays flat with no drift, at the cost of losing arbitrary page jumps — which is why offset still earns its place on bounded admin tables. Hand clients an opaque cursor so the contract can evolve, and treat the exact total as its own scan: estimate or probe with LIMIT n+1 instead of paying for a count nobody needs.