Why OFFSET Pagination Gets Slower Every Page: Keyset Cursors, Stable Sort Keys, and the Index That Makes Them Work

Key takeaways

• OFFSET pagination is linear in the offset: LIMIT 20 OFFSET 10000 makes PostgreSQL produce 10,020 rows in sort order and discard 10,000 of them.

• Keyset pagination replaces the skip with a range seek: WHERE (created_at, id) < ($1, $2) starts the index scan at the previous page's last row, so page 500 costs about what page 1 costs.

• Every ORDER BY used for paging must end in a unique tie-breaker, normally the primary key, or rows sharing a sort value are skipped or repeated at the page boundary.

• The composite index must match the ORDER BY including direction; a mixed order such as created_at DESC, id ASC needs its own index.

• Keyset pagination trades numbered pages for flat cost, which is the right trade for feeds, activity logs, and export endpoints.

Why does OFFSET pagination get slower on later pages?

OFFSET does not let PostgreSQL start reading at row n. The executor generates rows in sort order from the beginning and discards the first n before returning anything, so the work is proportional to OFFSET plus LIMIT. EXPLAIN ANALYZE states it plainly: the Limit node reports 20 rows while its child index scan reports 10,020 actual rows.

OFFSET is also unstable under concurrent writes. It counts positions in a result set recomputed on every request, so a row inserted above the window shifts everything down and the user sees a duplicate, while a deletion silently skips a row nobody can tell was missing.

What is keyset pagination, and how is the SQL different?

Keyset pagination, also called seek pagination, remembers the sort-key values of the last row on the previous page and asks for rows strictly after those values. The cursor is data, not a position. The query becomes: SELECT id, title, created_at FROM posts WHERE (created_at, id) < ($1, $2) ORDER BY created_at DESC, id DESC LIMIT 20.

(created_at, id) < ($1, $2) is a row-value comparison that PostgreSQL evaluates lexicographically and turns into an index range scan. The hand-expanded form created_at < $1 OR (created_at = $1 AND id < $2) returns the same rows but is an OR the planner often handles worse, so we keep the row-value form and verify it with EXPLAIN when an ORM rewrites it.

We request one row more than the page size. If the extra row exists there is a next page, and its key becomes the next cursor, which removes the need for a separate existence query.

Which index does the keyset query actually need?

The index must cover the exact ORDER BY tuple in order, with a compatible direction pattern. For ORDER BY created_at DESC, id DESC, an index on (created_at DESC, id DESC) works, and so does (created_at ASC, id ASC), because PostgreSQL can scan a B-tree backwards when every column reverses together.

Mixed directions are the trap: ORDER BY created_at DESC, id ASC cannot be served by either index, because no single backward scan produces that ordering. Each sort option a product offers therefore needs its own composite index ending in the tie-breaker column.

When the feed is always scoped to a tenant, the index should lead with the filter column — (tenant_id, created_at DESC, id DESC) — so the seek happens inside the tenant's slice rather than across the whole table.

How do cursors stay correct while rows are inserted and deleted?

Keyset cursors survive concurrent writes because they name values rather than positions. Deleting the row a cursor points at does not break the next request, since the comparison is evaluated against the stored values and those values need not still exist in the table.

The sort key must be unique as a whole. Sorting by a non-unique column alone means several rows share the cursor value, and a strict comparison drops their siblings while a non-strict one repeats the boundary row. Appending the primary key removes the choice.

The subtlest failure we hit was precision. PostgreSQL timestamptz stores microseconds while a JavaScript Date holds milliseconds, so round-tripping a cursor through new Date(value).toISOString() truncates three digits and the truncated value sorts after the real row — quietly skipping every row in that millisecond. We now treat the timestamp as an opaque string, carry the driver's raw value in the cursor, and send it back as text for PostgreSQL to cast.

How should the cursor be encoded in a public API?

Encode the cursor as base64url JSON so clients treat it as opaque, then validate it on the way back in as untrusted input. Opaque is not secret: base64 is an encoding, not encryption, and any client can decode and edit a cursor.

A malformed cursor should return HTTP 400 rather than falling back to page 1, because a silent fallback turns a client bug into an infinite scroll loop. The cursor must never carry authorization either — tenant and permission filters are re-applied server-side from the session, so an edited cursor yields an empty page instead of another customer's rows.

How does it fit a TanStack Query infinite list?

TanStack Query v5's useInfiniteQuery is built for cursors: initialPageParam seeds the first request and getNextPageParam reads the next cursor from the last page's response. Returning undefined from getNextPageParam is what sets hasNextPage to false — returning null does not, which is the usual reason a Load more button never disappears.

The sort order belongs in the queryKey. Changing the sort invalidates every cursor, and a shared key would append rows fetched under the old ordering to a list built under the new one. The maxPages option caps cached pages but requires both getNextPageParam and getPreviousPageParam, since dropping pages from one end means being able to re-fetch them from the other.

When is OFFSET still the right choice?

• Cost at page 500: OFFSET grows with depth; keyset stays flat.

• Jumping to an arbitrary page: supported by OFFSET, not by keyset cursors.

• Total page count: available from COUNT with OFFSET; keyset needs a separate or estimated count.

• Correctness under concurrent inserts: OFFSET shifts rows, keyset compares values and does not.

• Index requirement: OFFSET needs one index matching the ORDER BY; keyset needs one per sort option, each ending in a unique tie-breaker.

An admin table over a few thousand rows with page numbers is a perfectly good use of OFFSET. Public feeds, activity logs, and export endpoints that integrations walk end to end should be keyset from the start, because those are the access patterns that reach page 400. For the middle ground we ship a hybrid: keyset for the load-more path real users take, plus a capped OFFSET path for admin screens that stops offering page numbers past a fixed depth.

FAQ

Q: Can we still show Page 4 of 120 with keyset pagination?

A: Not directly, because a cursor knows where it is but not how many pages precede it. Show an estimated total from PostgreSQL's reltuples statistics or a cached COUNT, and present the list as load-more rather than numbered pages.

Q: Does keyset pagination work when the user picks the sort column?

A: Yes, provided each sort option has a composite index ending in the primary key and the cursor records which sort created it. Changing the sort must reset the cursor.

Q: Can Prisma do keyset pagination?

A: Prisma's cursor option with take and skip: 1 implements keyset pagination over a unique field, which covers ordering by id. Ordering by a non-unique column with a tie-breaker needs a raw query or a builder like Drizzle that can express the row-value comparison.

Q: Does this apply outside PostgreSQL?

A: The technique is general. MySQL 8 supports the same row-value comparison syntax and Elasticsearch's search_after is the same idea with a sort-value array. Confirm with EXPLAIN that the engine produced an index range scan rather than a filter.

Q: Is base64 enough to stop cursor tampering?

A: No. Validate the decoded shape, re-apply authorization filters server-side, and sign the cursor with an HMAC when tampering must be detected rather than merely survived.