Design Stable API and Database Pagination
Page through a large, changing dataset without duplicates, gaps, or unstable query plans.
Problem statement
Design pagination for an API backed by a large mutable database table. Clients select approved filters and one stable sort mode, receive a bounded page plus an opaque continuation, and may continue after concurrent inserts and updates.
The continuation must bind the filter, sort, page-size ceiling, and expiry, but it may carry signed position state or refer to server- or database-owned state. Choose and defend keyset, pinned-snapshot offset, a stateful/native continuation, or another bounded total-order traversal.
The API must reject continuation under changed query semantics and state whether it offers a current-view or snapshot-consistent walk. Cover indexes, deletes, inserts, backwards navigation if offered, abuse limits, and query observability. General full-text search is outside scope.
What to build
- Start a bounded page walk for an approved filter and stable total order.
- Continue with an opaque cursor that cannot be replayed under changed query semantics.
- Expose whether results follow current-view or pinned-snapshot semantics and handle mutation accordingly.
- Keep query work bounded with supported indexes, limits, validation, and observable rejection.
At scale
- For this exercise, assume one billion rows, 20,000 page requests per second, and a maximum page size of 200 records.
- For this exercise, supported list queries must complete within 300 milliseconds at p95 on a healthy indexed path.
- For this exercise, continuation cursors expire after 24 hours and may contain signed metadata but no sensitive raw filter values.
- For this exercise, each supported sort adds a reviewed composite index; arbitrary client-provided sort expressions are rejected.