Paginating a 200M-Row Activity Feed
A social app exposes GET /users/{id}/feed backed by a Postgres table
feed_items(id BIGSERIAL, user_id, created_at, payload) with ~200M rows.
Today it uses ?page=N&size=50 (offset pagination). Users report that
when they scroll during busy periods they sometimes see the same post
twice, and the p99 latency of deep pages (page 400+) is over 2 s.
- Explain both symptoms mechanically — why duplicates appear and why deep pages are slow.
- Redesign the endpoint using cursor pagination: specify the query parameters, what the cursor encodes, the SQL the server runs, and the index it needs.
- What do you lose by switching, and when would you keep offset pagination anyway?
1. Why offset pagination misbehaves
Duplicates/skips: offset pagination addresses rows by position, not identity. Page 3 = "skip 100 rows, take 50". If two new items are inserted at the top of the feed between the client fetching page 2 and page 3, every existing row shifts down by two positions, so the last two items of page 2 are now at positions 100–101 and appear again on page 3. Deletions cause the mirror problem — rows are skipped.
Deep-page latency: ORDER BY created_at DESC LIMIT 50 OFFSET 20000
cannot jump to row 20,001. The executor walks the index (or sorts a
heap scan) through 20,050 rows and discards 20,000. Cost grows linearly
with the offset, so page 400 reads roughly 400× the rows of page 1.
2. Cursor (keyset) redesign
GET /users/42/feed?limit=50&after=<cursor> where after is an opaque
base64 token encoding the sort key of the last item returned, e.g.
{"created_at":"2026-08-15T10:22:31Z","id":98811233}. Include id as
a tie-breaker because created_at is not unique.
SELECT id, created_at, payload
FROM feed_items
WHERE user_id = :uid
AND (created_at, id) < (:cursor_created_at, :cursor_id)
ORDER BY created_at DESC, id DESC
LIMIT 50;
Index: (user_id, created_at DESC, id DESC) — the query becomes a
single index seek plus 50 sequential reads, independent of how deep the
user has scrolled. Response body:
{"items":[...], "next_cursor":"...", "has_more":true}; next_cursor
is null on the last page. Because the cursor is opaque, you can
change its internal shape later without breaking clients.
3. Trade-offs
You lose random access ("jump to page 37") and an easy total_pages
count. Sorting by a different field requires that field (plus tie-break)
in the cursor and an index to match. Offset is still fine for small,
bounded, admin-style tables where a page picker is a genuine
requirement and the table is a few thousand rows.
Share this question