Pagination & Error Modeling

Two boring details where APIs fall over at scale

Two boring-looking API details, pagination and error shape, are where APIs quietly fall over at scale. Both have a correct answer.

Pagination: offset is the trap

The naive approach is offset/limit: ?offset=100000&limit=20. Two problems. It is O(n) deep: to return page 5,000 the database must scan and discard the first 100,000 rows, so deep pages get linearly slower and hammer the DB. And it is unstable under inserts: if a new row is added at the top between fetching page 1 and page 2, every item shifts down by one, so the user sees a duplicate or skips a row. On a live feed this is constant.

The fix is cursor (keyset) pagination. Instead of "skip N rows," you say "give me rows after this position." With an indexed ordering column:

WHERE (created_at, id) < (:cursor_ts, :cursor_id)
ORDER BY created_at DESC, id DESC
LIMIT 20

Because the DB seeks directly into the index rather than counting from the start, each page is O(1) regardless of depth, and because the cursor points at a stable row identity, inserts at the top do not shift the window. You return an opaque next_cursor (base64 of the last row's sort key) so the client cannot fabricate positions and you can change the encoding later. Always enforce a server-side max page size, and prefer a has_more boolean over an exact total count, because COUNT(*) on a large table is itself an expensive scan.

Errors: machine-readable and precisely coded

Clients need machine-readable, consistent errors to retry correctly, and humans need enough detail to debug. The standard shape is RFC 9457 Problem Details (the successor to RFC 7807): a JSON body with type (a URI naming the error class), title, status, detail, and instance, plus a correlation id so a support ticket maps to a specific log line.

Status codes must be used precisely, because they drive client retry logic:

• 400 malformed request, 422 well-formed but semantically invalid (validation).
• 401 not authenticated, 403 authenticated but not allowed, 404 not found.
• 409 conflict (for example a version clash or duplicate), 429 rate limited.
• 5xx server error.

The critical distinction is retryable versus not. 5xx and 429 are retryable (with backoff, and honor Retry-After on 429). 4xx other than 429 are the client's fault and must not be blindly retried, because retrying a 400 just wastes calls and can amplify load.

Interview nuance: two things separate strong answers. Saying "keyset pagination is O(1) and stable, offset is O(n) and shifts under inserts" (with the SQL), and saying "structured errors so clients can distinguish retryable 5xx/429 from non-retryable 4xx," plus the warning to never leak stack traces to clients.

Recap: use opaque cursor/keyset pagination with a bounded page size for O(1) stable paging, and return RFC 9457 structured errors with precise status codes so clients retry 5xx/429 but not other 4xx.