The Hidden Cost Of OFFSET
The classic pagination pattern is ORDER BY a column then LIMIT a page size with an OFFSET. It looks cheap, but OFFSET does not let the engine jump ahead. It must produce and throw away every row before the offset.
Why It Degrades
- Page one with OFFSET zero is fast.
- Page one thousand with a large OFFSET must scan all the earlier rows first.
- The cost grows linearly with how deep you page, so late pages get steadily slower.
Other Pitfalls
- Without a stable, unique ORDER BY, rows can shift between pages or appear twice as data changes.
- A new insert near the front shifts every later page by one, causing rows to be skipped or repeated.
Mitigations
- Always order by a unique key to make the order deterministic.
- For shallow pages OFFSET is fine, but deep pagination needs a different approach such as keyset pagination, which the next lesson covers.
Key idea
OFFSET pagination must scan and discard every skipped row, so cost grows with page depth and shifting data can skip or duplicate rows.