Cursor pagination versus offsets

What it is

Offset pagination asks for "page 5 of 20 items" and the database skips 80 rows; PostgreSQL's documentation notes that skipped rows still have to be computed, so large offsets are inefficient. Cursor (or keyset) pagination instead asks for "the next 20 items after key X", which uses an index and does not skip rows.

Why it matters

Between two offset requests, an inserted row shifts every later page: clients see duplicates or miss items. A cursor anchored to a stable sort key (an id, or a timestamp plus id) returns each item once regardless of concurrent writes.

How to apply

• Sort by a unique, indexed key or a composite that ends in one.
• Return an opaque next_cursor that encodes the last key; sign or encrypt it if clients must not construct their own.
• Give cursors an expiry and a documented way to reconcile after expiry.
• Keep page sizes bounded (a default and a maximum).

Pitfalls

Cursors cannot jump to page N; if a user interface needs numbered pages, offsets with a bounded maximum may still be the right choice. Sorting by a non-unique column without a tiebreaker loses or repeats rows at page boundaries.