9.6.2 — Collections at Scale: Pagination, Filtering, Sorting, Partial Responses

GET /orders works beautifully in development, where the table has forty rows. In production it has four million, and returning all of them means the database reads four million rows, your server holds them all in memory to turn them into JSON, and the client waits thirty seconds for a response that will crash its phone.

So every collection endpoint has to answer four questions. How do I return a slice instead of everything? That is pagination. How does the caller ask for only the rows they care about? That is filtering. In what order? Sorting. Can each row be smaller? Partial responses.

The first question is the interesting one, because the obvious answer is wrong in a way that stays hidden until you are big enough for it to hurt. It also raises a question engineers ask out loud and rarely get answered: who actually writes this — me, my framework, or the database? Section 3 answers that directly. ^{⚑Pagination strategies. [EQ-972]}

1. Offset pagination: the intuitive one, and its two diseases

The spelling everyone invents first:

GET /orders?limit=20&offset=40        # "page 3": skip 40, take 20

SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 40;

Two things about this are genuinely good, and you should not lose them by accident. You can jump straight to any page, because page 57 is just offset=1120. And you can show page 3 of 214, because a COUNT(*) gives you the total. Users like both.

Now the two problems. Both are structural, meaning you cannot tune or index your way out of them. ^{⚑Cursor vs Offset pagination. [EQ-973]}

Problem 1: the database does all the work you asked it to skip. This is the part that surprises people. OFFSET 40 does not teleport to row 41. The database reads rows 1 through 40, throws each one away, and then starts collecting your results.

It has no choice. "The first 40 rows in this order" is not a location the engine knows in advance; it is a fact that only exists once rows have been produced in that order and counted off. So the counting has to happen. An index on created_at helps it produce them in order quickly, but it still walks 40 index entries before reaching yours ([7.3] goes into why).

At page 3 you will never notice. At page 5,000, the database reads 100,020 rows to hand back 20, and the work grows in direct proportion to how deep you go — cost of O(offset). This is how a perfectly normal endpoint becomes an attack on yourself: a search crawler, which follows every link it finds, discovers your page links and walks patiently to page 40,000, each request more expensive than the last.

Problem 2: the pages shift under you while you read them. An offset points at a position, not at a row, and positions move.

Walk through it. You fetch page 2 and get orders 21 to 40. While you are reading, one new order arrives and lands at the top, because the sort is newest-first. Everything below it slides down one place. You now ask for page 3, meaning rows 41 to 60 — but what used to be row 40 is row 41 now. So the last order from page 2 appears again as the first item of page 3. If a row had been deleted instead, the reverse happens: everything slides up, and one order is never shown to you at all.

For a person scrolling a product list, seeing one duplicate is a shrug. For a nightly job copying your orders into a partner's warehouse, this is data corruption with no error attached. Nothing failed. No exception was thrown. There is simply a duplicate row in their database, and a missing one, and nobody finds out until the totals disagree at the end of the quarter.

So: offset is a reasonable choice for shallow, admin-style screens with page numbers, over data that does not change much. It is the wrong choice for feeds, for anything a machine walks end to end, and for anything deep.

2. Keyset (cursor) pagination: the scalable one

The fix reframes the question from "skip N rows" to "continue after the last row I saw":

-- page 1:
SELECT * FROM orders ORDER BY created_at DESC, id DESC LIMIT 20;
-- next page: WHERE the sort key is BEYOND the last row seen        --
SELECT * FROM orders
WHERE (created_at, id) < ('2026-07-20T10:14:00Z', 918273)   -- row-value comparison
ORDER BY created_at DESC, id DESC
LIMIT 20;

That WHERE clause is a completely different operation from OFFSET. It is a seek: the index is a sorted tree, and asking "where does 2026-07-20T10:14:00Z sit in it" is a handful of hops down the tree, no matter how many rows exist ([7.3] draws the tree). Cost is O(log n + limit), so page 5,000 costs what page 2 costs. That is not a speed-up, it is a different complexity class — the graph stops sloping upward entirely.

The drift problem disappears too, and for a reason worth stating plainly: you are no longer pointing at a position. You are pointing at a row. New orders arriving at the top do not change where order 918273 sits in the ordering, so "give me what comes after 918273" means the same thing at 10:14 and at midnight. A job walking the whole table sees each row once, with nothing repeated and nothing skipped, even while writes pour in.

Two rules make this actually work.

The sort must end in a column with no duplicates. This is why id is in there next to created_at. Suppose three orders were created in the same second, and your cursor says only "after 10:14:00". Those three rows are all at 10:14:00, not after it, so depending on which comparison you use you either return all three again on the next page or skip all three. Adding id makes every row's sort position unique, so "after this exact pair" always names exactly one boundary. As a bonus, this is the same uniqueness that makes the sort order deterministic, which section 4 needs anyway.

Give the client an opaque cursor, never the raw key values. Take {"k":["2026-07-20T10:14:00Z",918273]}, base64 it, and hand back ?cursor=eyJrIjp.... Three reasons, and the third is the one people miss.

It hides which columns you sort by, so changing the sort keys next year does not break a client that had learned to read them. It stops clients from building cursors themselves, which they will absolutely do if the format is readable, and which then breaks the moment you change anything. And because it is your format, you can put a version number in it and sign it, so a cursor from six months ago can be rejected cleanly instead of silently decoding into a query that means something different now.

The mental model to give clients: a cursor is a bookmark, not an address. You do not read a bookmark; you hand it back to get the next page.

// The response envelope — the working standard:
{
  "data": [ /* 20 orders */ ],
  "pageInfo": {
    "nextCursor": "eyJrIjpbIjIwMjYt...",     // null when exhausted
    "hasMore": true
  }
}

You do give things up, and you should say so rather than pretending keyset is free. There is no page 57 any more, only "next", because a bookmark tells you where you are and not how far along you are. And page 3 of 214 needs a separate COUNT(*) query, which on a large table is expensive — most APIs that still want it run it separately, cache the answer, and accept that it is slightly stale.

That is a real trade, which is why the choice depends on who is calling. In practice the large public APIs went cursor-first, because the traffic that actually matters — an infinite-scrolling feed, a sync job, a mobile app refreshing its cache — never wanted page 57 in the first place.

Figure 1 — Offset vs keyset. Offset re-walks everything it skips and drifts when rows move; keyset continues from a stable frontier via an index seek. The costs differ in complexity class, not constants.

3. Who implements it? (the honest layer map)

DrillCuriosity #37 (verbatim): What is key-set pagination? What is infinite pagination? Do we need to write code by own our own to implement in a system ( backend right?) or there are services in api framework or db level ?

Key-set pagination is section 2: instead of OFFSET n, remember the last row's sort-key values and continue with WHERE (sortKeys) < (lastSeen) — an index seek, flat cost at any depth, no drift. Infinite pagination (infinite scroll) is not a different backend technique — it's a frontend consumption style over cursor pagination: the UI fetches nextCursor pages as you approach the bottom ([6.3]'s IntersectionObserver triggers it) and appends, instead of rendering page buttons. Same API; different UI.

Who implements what — three layers, all involved: the database provides the primitives (LIMIT, ORDER BY, row-value WHERE (a,b) < (x,y) comparisons, and the index that makes the seek fast — you must design that index, [7.3]); it does not know about cursors or HTTP. The backend — your code owns the real work: choosing keyset vs offset per endpoint, building the WHERE clause from the decoded cursor, encoding/signing the opaque cursor, the response envelope, and the tie-breaker discipline.

Frameworks/libraries ship helpers so you don't hand-roll: ORMs have .cursor()/cursorPaginate() (Prisma's cursor:, Laravel's cursorPaginate, Django's CursorPagination class), and GraphQL's Relay connection spec standardizes the envelope — but they wrap the same SQL, and you still choose the strategy and the sort keys. So: yes, it's backend code to write or configure — the DB gives mechanics, the framework gives scaffolding, and the decisions (strategy, keys, cursor opacity) are always yours.

4. Filtering and sorting: a grammar, not an accident

Nobody decides to build a query language on top of their orders endpoint. It happens one ticket at a time. Someone needs to filter by status, so you add ?status=. Someone needs orders above a value, so you add ?minTotal=. Then ?createdAfter=, then ?sortBy= and ?sortDir= as two separate parameters because that was the shape of that afternoon's ticket. A year later the endpoint accepts nineteen parameters, four of them mean nearly the same thing, two are silently ignored, and no document describes any of it.

The fix is to decide the grammar once, before the second parameter exists, so every future filter slots into a shape that already has rules: ^{⚑Filtering. [EQ-974]⚑Sorting. [EQ-975]}

GET /orders?status=shipped&total[gte]=500&createdAt[lt]=2026-07-01&sort=-createdAt,id

Plain parameters mean equality; operators get a bracket. status=shipped means equals. total[gte]=500 means greater-than-or-equal, following one shape — field[op]=value — that every future operator can reuse. You could instead put the operator in the value, like filter=total>500, and that works too, but then you own a set of escaping rules for what happens when a value legitimately contains >. Either grammar is fine. Having two is not.

For multiple values, status=shipped,delivered means "either of these", the SQL IN. Write down two things while you are deciding this: that a comma inside one field means OR, and that separate fields always combine with AND. Both are the intuitive reading, and both will be asked about by the third client that integrates with you.

Write down the list of fields you allow, and reject everything else. This is not paranoia; it is the point where API design and database design turn out to be the same conversation. Allowing a filter on a column means promising it is indexed, and allowing a sort on an unindexed column means every request that uses it sorts the whole table in memory ([7.3]). So the list of permitted fields is really a list of indexes you have committed to maintaining.

When a client sends a field you do not support, answer 422 and include the valid options in the error. Do not silently ignore it. Silent ignoring is worse than it looks: the client's typo of statusss=shipped returns a perfectly good 200 with unfiltered data, so their code appears to work and quietly processes every order in the system. It also traps you, because once clients are sending parameters you ignore, adding a real parameter with that name changes their behaviour without warning.

Sorting gets one parameter, not two. sort=-createdAt,id — a leading minus means descending, and the comma means "then by". Two rules keep it safe. The full sort must always end in something unique, so the ordering is completely determined; if two rows can tie with nothing to break the tie, the database is free to return them in either order on either request, and pagination over an order that changes between calls is meaningless. And if the endpoint is cursor-paginated, every sort you allow must be one your cursor can encode, because the cursor stores that specific sort's key values. Sorting and pagination are not two features that happen to sit near each other. They are one mechanism.

Parse and check every value at the door. Everything in a query string arrives as text, so total[gte]=abc reaches your code as the string "abc", and JavaScript will happily compare it to numbers and produce nonsense rather than an error (3.6.7). Run query parameters through a schema exactly as you would a request body (3.7.7), and return 422 with the offending field named.

5. Partial responses: send less of each item

Collections have a second size axis: item width. A list view needs 5 fields; the resource has 45 (three of them expensive joins). The standard tool is a sparse-fieldset parameter: ^{⚑Partial responses. [EQ-976]}

GET /orders?fields=id,status,total,createdAt      # list view: 4 cheap columns
GET /orders/42                                     # detail view: everything

Four rules keep this from turning into its own mess.

Whitelist the selectable fields, same as with filters. Make the list endpoint cheap by default, so the expensive fields — the ones that need an extra join or a second table — are only fetched when someone explicitly asks. That one default is what stops a list endpoint from quietly doing a join per row for every caller who only wanted the order numbers. Document which fields are expensive, so a client asking for them is making a choice rather than an accident.

And treat fields as a filter on presentation only. It must never change what an operation means, and it must never affect permissions. A field the caller is not allowed to see is missing whether or not they asked for it; a field they are allowed to see is missing only because they did not ask. Once those two cases are distinguishable, you have built an oracle that tells anyone what fields exist and who can see them.

Step back and there are really three different answers to one question — who decides which fields come back?

• The server, per endpoint. That is fields= as shown above. Simple, and the server keeps control of what is cheap.
• The client, per query. That is GraphQL: the caller writes exactly the shape they want and the server assembles it. Enormous flexibility, and you now own the problem of a client asking for something ruinous ([5.8] weighs when this trade is worth it).
• The server, per purpose. No field-selection machinery at all: /orders returns a deliberately thin list shape, /orders/{id} returns everything. This is what most product APIs actually do, and it is the right default until someone can name the specific screen that the two shapes fail.

None of these is more advanced than the others. Pick the one whose control model you want, and pick it on purpose.

6. The expert lens

The pagination question is really an index question. Keyset is fast for exactly one reason: the columns you sort by are the ones the index is sorted by, so the database can jump into the middle of it. Change the sort to a column with no index and keyset's flat cost vanishes — you have the same query shape and none of the benefit.

Follow that thread and every query parameter you expose turns out to be a storage commitment. Each sort option is an index somebody has to maintain and pay write cost for. Each filter-and-sort combination is a query plan you have implicitly promised will stay fast. That is the real reason the grammar is whitelisted rather than open. The API's query surface is a contract with your database, and the conversation about whether to add one more sort option — which sorts do users genuinely need, and what will that index cost us on every insert? — is where API design and database design meet. It is usually the first conversation where a backend engineer visibly stops being junior.

Browsers and walkers are different customers. A person clicking through a screen tolerates a little drift and enjoys page numbers, and they will never go past page 5. A machine — a sync job, an export, a crawler, your own mobile app refreshing its cache — cannot tolerate drift at all, because a duplicate silently becomes a wrong number in someone's report, and it will go to page 8,000 because it is walking everything.

The classic failure is designing entirely for the first customer and then discovering the second one exists. Offset with page numbers ships, works fine for a year, and then a partner's nightly job starts making the database unusable every night at 2am.

When you have both audiences, build cursors as the foundation: machines get correctness, and an infinite-scroll UI consumes cursors natively. If a screen genuinely needs page numbers, layer that on top with a hard depth cap — refuse to go past page 100 and say so in the error. Large search engines do exactly this, which is why you cannot get to result 10,000. And anyone who really needs the whole dataset gets an export endpoint instead, not a walk down the interactive path.

Every knob you ship is permanent. fields, sort, each filter[op] — the moment one exists, someone builds on it, including in ways you never imagined. The example that bites is not a client using your API wrong; it is a partner's nightly job sorting four million rows by a field you added casually and never indexed, because it happened to be exposed.

So ship the smallest grammar your real consumers need, and add operators when someone can name the use case, not in anticipation of one. This is 9.3.6's open-closed discipline applied to query parameters: leave room to extend, but do not pre-build the extension. The cheapest part of your API surface is always the part you did not expose.

What the interviewer will push on

Pagination is a favourite interview topic because it is small enough to discuss in ten minutes and deep enough to separate people who have run a system from people who have read about one.

"How would you paginate this endpoint?" They are checking whether you ask about the consumer before answering. The strong response is a question back: is this a screen a human clicks through, or something a machine walks end to end? Then give the matching answer with its trade named. Answering "cursor, always" without asking is nearly as weak as answering "offset" — you skipped the reasoning, you just happened to land on the more fashionable option.

"Why is OFFSET slow?" They want to know if you understand the database or have memorised a rule. The answer must contain the words reads and discards: the engine has no way to locate row 40,001 without producing the first 40,000 in order. The tell of experience is mentioning that an index does not rescue you, because the walk happens through the index. The wrong answer is "because it scans the whole table", which is a different and usually false claim.

"Your cursor pagination returns duplicates sometimes. Why?" This is a debugging question with one overwhelmingly likely answer: the sort has no unique tie-breaker, so rows sharing a timestamp fall on both sides of the boundary. It is worth naming the second candidate too — a client that built its own cursor, or held one across a change in sort keys, which is precisely why the cursor should be opaque and versioned.

"The frontend wants a total count. What do you tell them?" They are testing whether you can push back with an alternative instead of just refusing. Say the real cost: an exact count over a filtered set of millions of rows means scanning them, on every page request. Then offer the options — an approximate count from table statistics, an exact count computed once and cached, hasMore instead of a total when the UI only needs to know whether to show a "load more" button, or a capped count such as "999+". Very often the design need is "does the next page exist", which is one extra row fetched and nothing else.

"How do you stop someone from writing an expensive query against this endpoint?" The full answer has several layers, and having more than one shows you have been on call: whitelist filterable and sortable fields so unindexed sorts are impossible; require a bounded time range on large tables and reject requests without one; cap the page size, because a client asking for limit=100000 should get a 422 and not a heroic effort; cap the length of IN lists; and send heavy analytical walks to a read replica so a badly-behaved partner cannot slow down checkout.

Volunteer this one, because nobody asks: say that a consumer wanting to mirror your whole dataset does not actually want pagination — it wants a delta feed, an endpoint that returns everything changed since a bookmark. Teams re-walk millions of rows nightly because pagination was the only tool offered to them. Replacing the walk with a delta can turn a six-hour job into under a minute, and it is the single highest-leverage thing you can offer a partner integration.

Next: 9.6.3 — writes that survive retries and races: idempotency keys, ETags and optimistic concurrency, and how to version an API without breaking its users.

Recall

• Offset (LIMIT/OFFSET): random page access + counts, but O(offset) walk-and-discard cost and drift under writes (dups/gaps for walkers). Fit: shallow, jump-to-page, admin UIs.
• Keyset/cursor: continue after the last-seen sort key — WHERE (created_at, id) < (…) = index seek, flat cost, drift-free. Laws: sort ends in a unique tie-breaker; API exposes an opaque cursor (base64 bookmark, never client-mintable). Trade: no page jumps, counts cost extra. Infinite scroll = UI over cursors, not a new backend technique.
• Who implements: DB gives primitives + the index you design; framework gives helpers (Prisma cursor:, Relay connections); your backend owns strategy, cursor encoding, envelope, tie-breakers.
• Filter/sort grammar designed once: field[op]=value, whitelisted fields (each = an index promise; unknown → 422, never ignore), sort=-a,b deterministic with tie-breaker, sort⊗pagination is one design, types validated at the boundary.
• Partial responses: sparse fields= projection (whitelist; cheap default on lists; never touches auth/semantics) — vs GraphQL (client-decided) vs purpose-shaped endpoints (server-per-purpose). Every knob is a forever-promise: ship the minimum.

Self-test: Explain both offset diseases mechanically. Why does keyset need the id tie-breaker, and why must the cursor be opaque? Recite the three-layer who-implements map. What makes every sortable field an index commitment? Name the three answers to "who decides the projection?"

Quiz Bank

FoundationalExplain offset pagination's two failure modes mechanically — the cost one and the correctness one.

Cost: OFFSET n cannot seek — which rows constitute "the first n" is only knowable by producing the ordered sequence, so the engine walks and discards n rows before returning the page ([7.3]: even with an index on the sort key, it traverses n entries). Cost is O(offset + limit): page 3 is cheap, page 5,000 reads 100k rows for 20 — deep pagination becomes a database DoS you built yourself (crawlers and export jobs find it reliably). Correctness (drift): the offset addresses positions, not rows; concurrent inserts/deletes shift every subsequent position between requests, so a walker sees the page-2 tail again on page 3 (insert above) or silently skips a row (delete above). Browsing humans shrug; sync/export consumers get duplicates and gaps with no error signal — data-corruption-shaped, not error-shaped. Both diseases are structural to skip-counting; both vanish under keyset's "continue after row X," which is a stable frontier and an index seek.

FoundationalDesign a correct keyset scheme for GET /orders sorted by newest first. What exactly goes in the cursor, and what are the two laws?

Sort: ORDER BY created_at DESC, id DESC — law 1: the sort must end in a unique, stable tie-breaker (id), because created_at collides: "after 10:14:00" alone would skip or duplicate same-second rows, and non-deterministic order makes any pagination incoherent. Page query: WHERE (created_at, id) < ($1, $2) (row-value comparison matching the DESC direction) ORDER BY … LIMIT 20 — an index seek on a composite (created_at, id) index you must create ([7.3]). The cursor: the last row's sort-key values — {"k": ["2026-07-20T10:14:00Z", 918273]} — base64-encoded (optionally versioned + HMAC-signed): law 2: opaque — clients treat it as a bookmark, never parse or mint it, so you can change sort keys, add fields, or re-shard without breaking anyone (an address invites construction; a bookmark doesn't). Envelope: { data, pageInfo: { nextCursor, hasMore } }, nextCursor: null at the end. What you give up, said out loud: page-57 jumps and cheap exact totals (serve counts from a separate cached/approximate query if the UI insists).

AppliedYour product API needs: a customer-facing order-history feed (mobile, infinite scroll), an admin table with page numbers over ≤10k rows, and a partner nightly export of all orders. Choose the pagination per surface and justify.

Three consumers, three correct answers — the point being that strategy is per-endpoint-purpose, not global. Order-history feed → keyset: mobile infinite scroll is cursor consumption natively (nextCursor on scroll-approach); depth is unbounded (years of orders), so O(offset) is disqualifying; and drift matters (a new order mid-scroll must not duplicate entries).

Admin table → offset, capped: admins genuinely use page jumps and totals; the dataset is small and shallow (≤10k rows ⇒ worst-case walk is trivial); drift between admin page views is acceptable. Cap depth defensively anyway (page ≤ 500) — admin tools get scripted eventually.

Partner export → neither: a dedicated export surface — either keyset-walked with a long-lived cursor designed for resumption (partner stores the cursor, resumes after failures — drift-free by section 2) or, better at real scale, an async export job (POST /exports → 202 → status URL → downloadable file, 9.6.1's 202 contract) — because "walk 4M rows through the request path nightly" is a load pattern the API shouldn't absorb (9.5.4's bounding instinct: move bulk work off the interactive path). The wrong answer the question is probing: one global offset implementation "for consistency" — which melts under the feed's depth, corrupts the export, and helps only the admin table.

InterviewDesign the filtering and sorting grammar for a collection endpoint, including every validation decision.

One grammar, declared once: equality as bare params (status=shipped; comma = IN: status=shipped,delivered), range/comparison operators as bracket suffixes (total[gte]=500, createdAt[lt]=2026-07-01 — the field[op]=value convention; document the operator set: gte/gt/lte/lt/ne, maybe like with escaping rules), cross-field composition is always AND. Sorting: sort=-createdAt,id — leading - descending, comma precedence, and the server appends the unique tie-breaker if absent so order is always deterministic (and cursor-compatible: on paginated endpoints, sort and cursor are one mechanism — the cursor encodes this sort's keys).

Validation decisions: whitelist filterable and sortable fields explicitly — each is an index commitment ([7.3]) and a permanent API promise; unknown field or operator → 422 with a field-level error listing valid options (silently ignoring turns typos into "filter is broken" tickets and makes future param additions breaking changes); type-parse every value at the boundary (query strings are all strings — 3.6.7; total[gte]=abc → 422 INVALID_TYPE, schema-validated per 3.7.7); cap list lengths and value sizes (a 10k-item IN list is a query-plan grenade); and bound combinatorics — if only three sort options are indexed, only three are whitelisted, and the conversation about adding a fourth is deliberately an engineering review, not a one-line PR.

StaffA partner integration walks your offset-paginated /transactions API nightly; as data grew, their job now takes 6 hours, hammers the primary, and last month they filed a data-integrity ticket — their mirror has 0.3% duplicate rows. Explain both symptoms to stakeholders, fix it without breaking the partner mid-quarter, and extract the org policy.

Both symptoms are section 1's diseases at scale, and they're the same root cause. Six hours + primary load: their walk's cost is Σ O(offset) across pages — quadratic total work in dataset size ([7.3]'s discarded-row walks), all on the request path. The 0.3% duplicates: overnight inserts shift positions between their page fetches — offset drift materialized as their corrupted mirror; the integrity ticket is your API design showing up in their data warehouse (worth saying plainly to stakeholders: no one wrote a bug; the pagination contract cannot express a consistent walk under writes).

Fix without breakage: (1) Ship keyset on the same endpoint additively — ?cursor= param and pageInfo.nextCursor in the envelope; offset params keep working (permanence law — 9.6.1's staff answer's playbook in miniature). (2) Give the partner the right surface: an async export (POST /exports job → file) or a change-feed (GET /transactions/changes?since=cursor — often what a nightly mirror actually wants is a delta, not a re-walk; this single reframe can turn 6 hours into 40 seconds). (3) Migrate them with dates: deprecation headers on deep-offset requests, a depth cap announced then enforced (offset ≤ 10_000 → 422 DEPTH_CAPPED with a pointer to cursors/exports), and telemetry per consumer so enforcement lands after their migration, not before. (4) Protect the database meanwhile: route deep walks to a read replica, rate-limit by depth.

Org policy extracted: collection endpoints are cursor-first by default; offset only with a documented depth cap; any consumer walking a full dataset gets an export/delta surface, not the interactive path; and "which sorts/filters are indexed" is part of API review (9.6.4's checklist gains a row). The stakeholder sentence: we sold a bookmarking API to someone who needed a photocopier — the fix is selling photocopies.

Flashcards

FlashOffset's two diseases

O(offset) walk-and-discard (deep pages = self-DoS) + positional drift under writes (dups/gaps for walkers, no error signal).

FlashKeyset recipe

ORDER BY key…, id (unique tie-breaker) + WHERE (keys) < (last seen) = index seek, flat cost, drift-free. Cursor = opaque base64 bookmark.

FlashInfinite scroll

Not a backend technique — a UI consuming cursor pages on scroll-approach (IntersectionObserver). Same API as keyset.

FlashWho implements pagination

DB: LIMIT/ORDER BY/row-value WHERE + your index. Framework: helpers (Prisma cursor, Relay). You: strategy, cursor encoding, envelope, tie-breakers.

FlashGrammar rules

field[op]=value · whitelist fields (each = index promise) · unknown → 422 never ignore · sort deterministic w/ tie-breaker · types parsed at boundary.

FlashProjection: three answers

Sparse fields= (server-per-endpoint) · GraphQL (client-per-query) · purpose-shaped endpoints (server-per-purpose). Choose consciously.

Scenario Drill

DrillDesign the complete collection surface for a B2B analytics product's /events endpoint: 500M rows, customers query by time range + event type + user, sort by time, dashboards page through results, a CSV download exists, and one enterprise customer's SIEM tails new events continuously. Specify pagination, grammar, projections, indexes, and the two abuse cases you must prevent.

Base decisions from consumer shapes. Dashboards page deep into large time ranges → keyset only (500M rows makes offset's O(offset) an outage, not a slowdown): sort ORDER BY occurred_at DESC, id DESC, cursor = opaque (occurred_at, id) bookmark, envelope { data, pageInfo }.

Grammar (whitelisted, each entry an index commitment): occurredAt[gte]/[lt] (time range — mandatory pair, actually: unbounded queries over 500M rows are the first abuse case — reject rangeless requests with 422 RANGE_REQUIRED, max range 90 days, documented), type=page_view,click (IN over a small enum), userId= (point filter). Sort: time only (sort=-occurredAt or occurredAt), tie-breaker auto-appended — offering arbitrary sorts over 500M rows is how partner jobs create full-table sorts; declined by whitelist, revisit on demonstrated need (9.3.6 OCP for query params).

Indexes follow the grammar ([7.3]): (occurred_at DESC, id DESC) for the base walk; (user_id, occurred_at DESC, id DESC) for per-user queries; type folded in per cardinality analysis — and the review rule: grammar changes and index changes are one PR.

Projections: events are wide (payload JSON); default list projection is thin (id, type, occurredAt, userId), fields= whitelist opts into payload — the cheap-default rule preventing accidental 500M-row-wide scans. CSV download → async export job (POST /exports {filters} → 202 → status → file): bulk leaves the interactive path (9.5.4); the export worker itself walks keyset pages against a replica.

SIEM tailing → a delta surface, not deep pagination: GET /events/changes?cursor= where the cursor is a position in the stream (same keyset machinery, framed as "everything after my bookmark") — polled or SSE-pushed ([5.8]); this is the change-feed reframe: a tailing consumer wants the frontier, and giving them pagination would recreate the nightly-walk pathology.

Second abuse case: cursor sharing/hoarding — cursors HMAC-signed and TTL'd (a 6-month-old bookmark into a re-sharded table must fail cleanly with 410 CURSOR_EXPIRED + restart guidance, not decode garbage). Close with the drill's pattern: every consumer got the surface shaped like its access pattern — pages for browsing, jobs for bulk, deltas for tailing — and the grammar/index/projection whitelist is one contract reviewed as a unit.