Keyset Pagination vs. Offset Pagination

Consider an API endpoint that responds in 30 ms on page 1, but degrades to several seconds by page 200—even though both pages return exactly 50 records. The performance bottleneck is rarely the volume of returned data; it is the computational overhead the database incurs before discarding preceding rows.

When executing ORDER BY created_at DESC, id DESC LIMIT 50 OFFSET 10000, PostgreSQL must sort, traverse, and discard the first 10,000 rows before returning the next 50. The query cost scales linearly ($O(N)$) as the offset deepens.

The Inefficiency of Deep Offsets

An index avoids an expensive in-memory sort, but it cannot eliminate the fundamental penalty of OFFSET: even when walking a B-tree in index order, the database engine must still count past and discard every preceding entry until reaching the target position.

In keyset (or cursor-based) pagination, the API takes the last seen values and queries directly from that pivot: WHERE (created_at, id) < (:last_created_at, :last_id) ORDER BY created_at DESC, id DESC LIMIT 50.

Stable Ordering and Keyset Seeking

Including id as a deterministic tie-breaker is essential because multiple rows can share identical timestamps. Without a strict, total ordering, pages can skip records or duplicate items across consecutive fetches even if no underlying data changed.

Backed by a compound B-tree index matching the filter and sort direction—such as (created_at DESC, id DESC) in this example without additional filters—PostgreSQL navigates directly to the relevant range, seeking into the index in $O(\log N)$ time rather than counting thousands of rows already seen.

Eliminating Page Drift Under Concurrency

The difference is equally pronounced under concurrent writes. When new records are inserted at the beginning of a dataset, a positional OFFSET shifts subsequent rows downward, causing users to see duplicate entries across pagination boundaries. Keyset cursors anchor traversal to actual values, preventing drift during navigation.

Keyset pagination does not provide arbitrary jumps to page 500, nor does it guarantee an immutable snapshot across navigations. It requires opaque cursor encoding, strict filter validation, and per-query authorization. In exchange, it replaces unbounded scanning costs with consistent, constant-time database retrieval.