Preparing your design workspace

Loading the problem and your latest saved attempt…

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

  1. Start a bounded page walk for an approved filter and stable total order.
  2. Continue with an opaque cursor that cannot be replayed under changed query semantics.
  3. Expose whether results follow current-view or pinned-snapshot semantics and handle mutation accordingly.
  4. 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.