Database reasoning · 2 / 4
Paginate a changing dataset
Your challenge
An activity feed contains millions of rows. Offset pagination slows down and sometimes duplicates entries when new activity arrives. Design a stable query.
Try it first. Write down your assumptions and explain your reasoning.
SELECT id, created_at, body
FROM activity
WHERE account_id = $1
AND (created_at, id) < ($2, $3)
ORDER BY created_at DESC, id DESC
LIMIT 21;
CREATE INDEX activity_feed_idx
ON activity(account_id, created_at DESC, id DESC);1.Choose a deterministic order
Sort by created_at descending and a unique id descending. Time alone is not unique, so two rows sharing a timestamp need a tie-breaker. The cursor must contain both values and describe the applied filters.
2.Use keyset pagination
Fetch rows strictly older than the cursor tuple. Add an index beginning with the account or tenant filter, followed by the sort columns. Request one extra row to determine whether another page exists. Explain the index with the actual query plan rather than assuming every index is helpful.
3.State the consistency contract
New rows naturally appear on a refreshed first page, not halfway through an older cursor sequence. Updating the sort key can still move rows between pages. For an exact snapshot, you need a snapshot/version boundary; for most feeds, documented refresh behavior is sufficient. Validate cursor shape and keep account scoping on every query.
Take it one step further
- 1.How do you handle the first page?
- 2.What happens if created_at can be edited?
Self-review
Can you explain each point without looking at the solution?
- Stable tie-breaker
- Matching filter and sort index
- Account scope and explicit consistency behavior