Cursor pagination, and the offset that skips rows

Offset pagination over a list that changes between requests skips and duplicates rows, and it gets slower as the offset grows.

-- page 400: the database reads 8,000 rows and discards 7,980
SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 7980;

-- cursor: constant cost, and stable under insertion
SELECT * FROM orders WHERE id < ? ORDER BY id DESC LIMIT 20;

-- and with a non-unique sort key, the cursor needs both
WHERE (placed_at, id) < (?, ?) ORDER BY placed_at DESC, id DESC

The row-skipping is the correctness argument and is more important than the speed one: a row inserted while somebody pages through shifts everything down, so page two omits a row that was on page one. The composite comparison for a non-unique sort key is the part people implement wrongly — comparing only the timestamp loses rows sharing a timestamp with the cursor. Encoding the cursor opaquely stops clients constructing one and depending on the format.