A list endpoint returning 2 million records times out on deep pages and returns 500 for validation failures. Fix both properly.

The pagination problem

OFFSET 100000 requires the database to scan and discard 100,000 rows before returning the page, so each page is slower than the last. Offset pagination also shifts when rows are inserted or deleted between requests, so consecutive pages skip or repeat records.

Replace with cursor (keyset) pagination. Encode the sort key of the last item — "the next 50 after this" — which becomes an indexed range scan of constant cost regardless of depth, unaffected by insertions earlier in the set.

Requirements: the sort must be stable and unique, so tie-break a timestamp with an ID. The cursor should be opaque (encoded, not a raw column value) so its internals can change without breaking clients.

The trade-off to state: you cannot jump to page 47. Fine for APIs and infinite scroll, unacceptable for numbered page navigation — in which case constrain the maximum offset and pair it with filtering.

Also cap page size server-side, and never return an unbounded result set by default.

The error problem

Returning 500 for a validation failure is actively harmful: 500 signals a server fault, which tells retry middleware to retry something that will fail identically forever, and it pollutes your error rate SLI with client mistakes.

Use the right code. 400 for malformed syntax, 422 for semantically invalid content, 404 for a missing resource, 409 for a state conflict, 429 for rate limiting.

Give clients something to act on

Problem Details (RFC 9457): type (a URI identifying the error class — the stable thing clients branch on), title, status, detail, instance, plus extensions.

Two extensions worth always including: a correlation ID, so a support conversation can find the request; and field-level validation errors as a structured list rather than a concatenated string, so a client can highlight the offending field.

Clients must never branch on message text — it is for humans and may be reworded or localised.

Make retryability explicit

Retryable: 408, 429, 502, 503, 504, connection failures, timeouts. Not retryable: 400, 401, 403, 404, 422. Ambiguous: 409, and 500 which may be transient or deterministic.

Consider an explicit retryable flag or error type in the body, plus Retry-After where a wait is appropriate — and remember the one absolute exception: any error on a non-idempotent operation without an idempotency key is unsafe to retry, regardless of status code.