Slow pagination: deep OFFSET meltdown

Slow pagination: deep OFFSET meltdown
Type: incident simulation · Topic: Query Performance · Level: L3 · Duration: 10–15 min
Launch: ride postgres start stage-01/09-slow-count-pagination

POSTMORTEM (root cause · how it was found · the fix · lesson)
Root cause: the listing endpoint paginates with LIMIT/OFFSET and users go deep
(OFFSET 50000). With no index matching the tenant filter and the ORDER BY, the
planner did a Seq Scan + Sort and then discarded the first 50,000 rows just to
return 50 — work that grows with the page number.

How it was found: EXPLAIN (ANALYZE) showed a Seq Scan + Sort and a huge number
of rows processed before the LIMIT, despite returning only 50.

The fix (database side): CREATE INDEX
ON orders (tenant_id, created_at DESC, id DESC);
Now the order comes from the index (no Sort), so the plan is an ordered Index
Scan.

The real fix (application side): keyset (seek) pagination instead of OFFSET —
  WHERE tenant_id = $1 AND (created_at, id) < ($last_created, $last_id)
  ORDER BY created_at DESC, id DESC LIMIT 50;
This is O(page size) regardless of how deep the user scrolls.

Lesson: OFFSET pagination degrades with depth. Add the matching composite index
(tenant_id first, then the ORDER BY columns), and move hot pagination to keyset.

INVESTIGATION HINTS (the staged path to diagnose and fix)
1. EXPLAIN (ANALYZE) the slow pagination query. A deep OFFSET makes PostgreSQL scan and throw away all the skipped rows — watch the actual rows vs the 50 you return.
2. There's no index matching the filter + ORDER BY, so it's a Seq Scan + Sort before the OFFSET. Build (tenant_id, created_at DESC, id DESC) so the order comes from the index.
3. An index fixes the plan shape, but a deep OFFSET is still O(offset). The real application fix is keyset pagination: WHERE (created_at, id) < ($last_created, $last_id) ORDER BY created_at DESC, id DESC LIMIT 50.