Practice · Scaling · Card 4
Why is page 50,000 so much slower than page 1?
The same query, same index, same database. Page 1 returns in 5ms. Page 50,000 takes 4 seconds. The user is on the same table.
The query
Standard OFFSET pagination from Kaminari, page size 20, on a 5M-row table with an index on created_at.
# At page 50,000, Kaminari generates:
Post.order(:created_at).limit(20).offset(1_000_000)
# Which is:
# SELECT "posts".* FROM "posts"
# ORDER BY "posts"."created_at" ASC
# LIMIT 20 OFFSET 1000000
# Page 1: 5ms.
# Page 50,000: 4s.The question
Why is page 50,000 so much slower? The index on created_at is in place.
Take a moment. Pick the best answer. Wrong picks reveal why they're wrong, which is half the point.
✅ Answer breakdown
✗ A. The router takes longer to parse the URL when the page number is large.
Route matching is O(URL length), not O(page number). The URL /posts?page=50000 is the same length as /posts?page=1 in matching terms.
✓ B. Postgres walks the index for 1,000,020 rows, throws away the first million, and returns the last 20.
This is exactly what OFFSET does. The index is used, but the cost scales with the offset value, not the limit. Page 1 reads 20 entries. Page 50,000 reads a million.
✗ C. OFFSET prevents Postgres from using indexes entirely.
It doesn't. OFFSET still uses the index. The walk is just longer. EXPLAIN will still show an Index Scan with both pages.
✗ D. Rails has a default page-size cap and falls back to a full table scan past it.
Rails has no such limit, and even if a paginator gem had one, "fall back to a full table scan" isn't a thing that happens automatically.
💡 The principle
OFFSET is "skip N rows and then return some." There's no shortcut for "skip a million rows." Postgres has to walk them to know they're the ones to skip. The cost grows linearly with the page number.
The fix is keyset pagination (also called "cursor pagination" or "seek pagination"). Instead of "page 50,000," you remember the last value the user saw and query WHERE created_at > ? LIMIT 20. The cost stays at 20 rows read regardless of how deep the user is.
Tradeoff: keyset pagination doesn't give you "jump to page 50,000" — only "next 20." For most user-facing infinite scrolls and APIs, that's a feature, not a limitation.
📚 Theory
For the full walkthrough, read Scaling · Pagination at Scale.