Why is cursor (keyset) pagination preferred over OFFSET for large, changing datasets?
Options
- It supports jumping directly to any page number of the results
- OFFSET skips rows and slows down; a cursor uses an index (Correct option)
- Cursor pagination does not need any index on the table
- OFFSET is not valid SQL in most of the databases
Answer
OFFSET gets slower as it grows and can skip/duplicate rows when data changes; a cursor seeks with an indexed WHERE id > :last
Why
OFFSET 100000 LIMIT 20 makes the database read and throw away 100,000 rows, so deep pages are slow. If rows are inserted while the user pages, items can be skipped or shown twice.
Cursor pagination remembers the last item seen and asks for WHERE id > 12345 ORDER BY id LIMIT 20, which uses an index and is always fast and stable.
Trade-off: no 'jump to page 37'.
Remember
Large or live lists → cursor pagination.