BeCuriouzLearn something new every day
Why page 10,000 loads slower than page 1
💡 OFFSET makes the database scan and discard every skipped row, so deep pages get slower and can skip data — keyset pagination seeks by the last-seen key for constant-cost, stable paging.
You've built pagination with LIMIT 20 OFFSET 200000. It works — but users deep in the list wait longer and longer. Markus Winand's "No Offset" essay explains why: OFFSET doesn't teleport the database to row 200,000. The engine still reads and discards every row before the offset, then returns the next 20. Page 1 skips nothing; page 10,000 skips 200,000 rows first. The cost grows with how deep you go.
The fix is keyset pagination (a.k.a. "seek" or cursor pagination). Instead of counting rows, you remember the last value you saw — say the id or timestamp of the final row — and ask for WHERE id > :last_id ORDER BY id LIMIT 20. Backed by an index, the database jumps straight to that spot, so every page costs the same whether it's page 2 or page 20,000.
There's a second, subtler win: stability. With OFFSET, if someone inserts or deletes a row while a user is paging, the window shifts and rows get skipped or shown twice. Keyset pagination anchors on real values, so it stays consistent under a moving dataset. The tradeoff: you lose easy "jump to page 500" links — keyset is built for "next/previous" and infinite scroll, not random page access.
Source: No Offset — Use The Index, Luke!