Pagination, Filtering, Sorting & Partial Responses
List endpoints make or break API UX. Interviewers ask offset vs cursor, why page 50 drifts under inserts, how to sort stably, where filters live, and whether fields= helps mobile. Prefer cursors for large append-heavy feeds; always define a total order.
- 1Gist
- 2Maps
- 3Q&A
- 4Sandbox
Voice readout needs Web Speech Synthesis in this browser.
How you page a hot collection
Prefer
Keyset cursor, stable sort, clamped limit, Link next
Encode (created_at, id) in an opaque token. ORDER BY those columns. Clients follow rel=next. Mobile may ask ?fields=id,status.
- Inserts do not duplicate or skip the way OFFSET 10000 does.
- Tokens expire or bind to ACL so they are not IDOR vectors.
- Normalize query param order if you CDN-cache GETs.
Alternative
Offset=50 with sort=createdAt only, unlimited limit
Page 50 drifts under concurrent inserts. Ties reshuffle. limit=100000 blows memory and turns into a 429 retry storm.
- SQL OFFSET is expensive on deep pages.
- Personalized filters cached at the CDN leak across users.
- Forgeable cursors that encode raw ids skip authz.
First page then continue
Same story as the sequence diagram — vertical for phones.
- 1
Clamp limit
Default 50, max 100–200. Document clamp vs 400. - 2
Stable sort
Newest first still needs an id tie-breaker. - 3
Keyset query
WHERE (created_at, id) is less than the cursor tuple. - 4
Opaque next
Link rel=next and/or next_page_token. Sign or expiry if the token is capability-like.
Overview
List endpoints make or break API UX. Interviewers ask offset vs cursor, why page 50 drifts under inserts, how to sort stably, where filters live, and whether ?fields= helps mobile. Wrong pagination couples to DB pain and broken caches.
Rate-limit retries stay in the 429/idempotency cluster — this page names the interaction only.
Offset vs cursor
- Offset (
?offset=100&limit=50) — simple; bad for deep pages and concurrent inserts/deletes (duplicates/skips). - Cursor / continuation token (
?pageToken=or?cursor=) — opaque; stable under inserts if keyed on sort columns; harder to jump to arbitrary page N. - Keyset pagination (
WHERE (created_at, id) < (?, ?) ORDER BY ...) — efficient; cousin of cursors. - Prefer cursors for infinite scroll and large datasets; offset OK for admin UIs with small N.
| Style | Pros | Cons |
|---|---|---|
| Offset | Jump to page 10 | Drift, expensive OFFSET in SQL |
| Cursor / keyset | Stable, cheap | No cheap random access; tokens are opaque |
| GraphQL connections | Same cursor ideas | Different surface (hub note only) |
Client-driven high limits mean fewer RTTs and more abuse — pair with 429.
Stable sort
- Always define a total order: e.g.
sort=createdAt,idso ties do not reshuffle. - Reject unknown sort fields; document defaults.
- Descending "newest first" still needs a unique tie-breaker id.
Filtering, sorting, fields
- Filters as query params:
?status=active&owner=usr_1(naming — not path). - Sort:
?sort=-createdAt,idor?sort=createdAt&order=desc— pick one grammar. - Sparse fieldsets / partial responses:
?fields=id,name,statusor?select=— cut payload for mobile. - Max page size: enforce server-side (e.g. 100–200); clamp silently or 400 — document it.
Caching and Link headers
- Query strings are part of the cache key — unstable param order can fragment caches; normalize if you CDN-cache GETs.
Link: <...>; rel="next"(RFC 8288) and/or bodynextLink/next_page_token.- Prefer opaque tokens that expire or bind to ACL so tokens are not forgeable IDOR vectors.
- Personalized filtered GETs at a CDN need
Vary/ auth awareness.
Sequence
- 1
Client
1 First page
- 2
Client → List API
GET limit=50 stable sort
- 3
List API → Store
Keyset query limit 50
- 4
Store → List API
Rows plus cursor material
- 5
List API → Client
200 items Link rel=next
- 6
Client
2 Continue
- 7
Client → List API
GET cursor token
- 8
List API → Store
Decode plus keyset
- 9
Store → List API
Next rows
- 10
List API → Client
200 items optional next
Lesson map
Pagination, Filtering, Sorting & Partial Responses
List endpoints make or break API UX. Interviewers ask offset vs cursor, why page 50 drifts under inserts, how to sort stably, where filters live, and whether fields= helps mobile. Prefer cursors for large append-heavy feeds; always define a total order.
Architecture. Architecture
Select a node to see why it exists, or an edge to see the protocol, direction, effect, and consequence.
Mermaid export
flowchart TB c["Client"] api["List API"] db["Store"] c -->|GET limit=50| api api -->|Keyset query| db db -->|Rows plus cursor| api api -->|200 items Link| c c -->|GET cursor token| api api -->|Decode plus| db
Sandbox: cursor encode and clamp (Python)
Rows assumed sorted by created_at desc, id desc. Didactic Base64 — production signs or encrypts and binds ACL.
Press Run. Snippets must be self-contained — no network, files, or native modules.
Link next builder (TypeScript)
Press Run. Snippets must be self-contained — no network, files, or native modules.
Deep dive · Why OFFSET drifts
You read offset 0–49. A new row inserts at the top. Offset 50 now starts one row later than the client expects — skip. A delete does the opposite — duplicate. Keyset "everything less than the last (created_at, id) I saw" is stable for that sort.
Pitfalls
Client A holds page 1 (newest 50). An order inserts. Client B asks offset=50. Draw which row is skipped. Now repeat with a (created_at, id) cursor. Who still sees the new order, and on which page?
Interview Q&A
Offset vs cursor?
Answer
Offset for small/admin UIs that jump to page N. Cursor/keyset for large, append-heavy feeds and infinite scroll.
Why stable sort?
Answer
Without a total order, pages reshuffle under concurrency. createdAt ties need id.
How to expose the next page?
Answer
Opaque cursor + Link: rel=next (RFC 8288) and/or nextLink in the body. Do not make clients invent offsets.
Sparse fieldsets worth it?
Answer
Yes for mobile/bandwidth. Document allowed fields. Do not break required clients that omitted fields.
Interaction with rate limits?
Answer
Smaller pages mean more requests. Pair with backoff — 429 / Retry-After and retry storms. List GETs are safe; mutating retries are idempotent methods.
Can the client jump to page 10 with a cursor?
Answer
Not cheaply. That is the offset trade. Some APIs expose page only under a small max N.
What goes in the token?
Answer
Sort keys (and direction), not a raw SQL offset. Sign or bind to the caller's ACL. Expiry if it is a capability.
Cache fragmentation?
Answer
?limit=50&status=a vs ?status=a&limit=50 are different cache keys unless you normalize. CDN-cached private filters need Vary and auth.
GraphQL connections?
Answer
Same cursor ideas on a different surface. This cluster stays on HTTP list GETs — see the hub note.
Where do filters live?
Answer
Query string. Path is identity. Depth: URI design.