Cursor-Based Pagination

What's being tested

Cursor-based pagination tests whether you can return stable slices from an ordered result set without relying on fragile OFFSET semantics. Interviewers probe deterministic ordering, cursor/token design, bidirectional navigation, filtering, duplicate avoidance, and complexity tradeoffs for both in-memory and database-backed queries.

Patterns & templates

• Stable sort key: order by a business key plus unique tie-breaker, e.g. ORDER BY created_at DESC, id DESC, to avoid skipped or duplicated rows.

• Forward cursor predicate: for descending order, fetch after (ts, id) using (created_at, id) < (:ts, :id); invert comparisons for ascending order.

• Backward pagination: reverse the inequality, fetch limit + 1, then reverse results before returning so UI order stays consistent.

• Opaque cursor token: encode {created_at, id, filters, direction} with base64 or signed JSON; never expose mutable array indexes as durable cursors.

• Limit-plus-one: request limit + 1 rows to compute has_next_page or has_previous_page without an extra count query.

• Filter composition: apply conjunctive filters before pagination; index should match WHERE filters then ORDER BY, e.g. (user_id, status, created_at, id).

• Complexity target: database seek pagination should be O(limit) after index seek; in-memory versions are often O(n log n) sort plus O(limit) slice.

Common pitfalls

Pitfall: Using OFFSET and LIMIT as the primary solution; inserts or deletes between requests can shift rows and cause duplicates or missing items.

Pitfall: Sorting only by created_at; equal timestamps make ordering nondeterministic unless you add a unique tie-breaker like id.

Pitfall: Treating backward pagination as “same query with previous cursor”; you must flip comparison direction, fetch extra, and restore display order.

Practice these

The practice cards below cover the canonical variants — solve all of them and time yourself.