LIMIT 50 OFFSET 45000 reads forty-five thousand rows and discards them, which is fine until somebody paginates to the end.
-- offset: reads and discards
SELECT * FROM orders ORDER BY id LIMIT 50 OFFSET 45000;
-- 2.4s
-- cursor: reads 50
SELECT * FROM orders WHERE id < 45000 ORDER BY id DESC LIMIT 50;
-- 3ms
-- and the compound case, which is where it gets fiddly
SELECT * FROM orders
WHERE (created_at, id) < ('2023-06-20 14:02:11', 8814)
ORDER BY created_at DESC, id DESC LIMIT 50;The row-value comparison is the part that trips people up: sorting by a non-unique column needs a tiebreaker in both the ORDER BY and the cursor, or rows are skipped or repeated at page boundaries. What cursors cost is the ability to jump to page 40, which a user interface may genuinely need — in which case offset for the first few pages and a cursor beyond them is an ugly, working compromise.