Why My Pagination Got Slower Every Page: Field Notes on OFFSET, Keyset Cursors, and Stable Sort Keys

Headline: OFFSET pagination tells PostgreSQL to read every row it skips and then throw those rows away, so deep pages get steadily slower and can silently skip or duplicate results while the table is being written to. Keyset pagination replaces OFFSET with a WHERE filter on the last row's sort key, backed by a composite index that matches the ORDER BY, and keeps every page roughly the same cost.

An internal dashboard feed I maintain started timing out, but only for the people who scrolled a long way. Page 1 came back in milliseconds. Page 40 took seconds. The SQL had not changed, the index was still there, and the ORM was innocent. The table had simply grown, and OFFSET is an instruction to read rows and discard them. These are my notes from replacing it.

Key takeaways

• OFFSET is linear in the offset. LIMIT 20 OFFSET 10000 makes PostgreSQL produce 10,020 rows in sort order and discard 10,000 of them, so the cost of a page grows with how deep the page is.
• Keyset pagination is a range seek, not a skip. 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.
• The sort key must be unique. Append the primary key to every ORDER BY, because two rows sharing a created_at value will be skipped or repeated at the page boundary.
• The index must match the ORDER BY, including direction. PostgreSQL can scan an index backwards when all columns flip together, but created_at DESC, id ASC needs its own index.
• Keyset pagination gives up page numbers. It supports "load more" and infinite scroll, not "jump to page 37", which is the trade you are actually making.

Why does OFFSET pagination get slower on later pages?

OFFSET n 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. The work is proportional to OFFSET + LIMIT, which is why page 1 and page 400 of the same query have wildly different costs even with a perfect index.

EXPLAIN ANALYZE states this directly: a Limit node reports rows=20 while its child index scan reports actual rows=10020. Nothing is misconfigured — the plan is doing exactly what the query asked for.

The second problem is correctness. OFFSET counts positions in a result set that is recomputed on every request. If rows are inserted above the window between page 3 and page 4, every row shifts down by one and the user sees the same row twice; if rows are deleted, a row is skipped entirely and nobody notices.

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

Keyset pagination — also called seek pagination or cursor pagination — remembers the sort-key values of the last row on the previous page and asks for rows strictly after those values, instead of counting positions. The cursor is data, not an index into a list.

-- OFFSET: produces 10,020 rows, returns 20
SELECT id, title, created_at
FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 10000;

-- Keyset: seeks into the index, reads ~21 rows, returns 20
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, and PostgreSQL compares it lexicographically: true when created_at is smaller, or when created_at is equal and id is smaller. PostgreSQL turns that form into an index range scan. The hand-expanded equivalent, created_at < $1 OR (created_at = $1 AND id < $2), returns the same rows but is an OR the planner often handles worse, so I keep the row-value form and check EXPLAIN when an ORM insists on expanding it.

I also select LIMIT 21 when the page size is 20: if the 21st row exists, there is a next page, and that extra row's key becomes the next cursor. No separate existence query needed.

Which index does a keyset query actually need?

The index has to cover the exact ORDER BY tuple, in order, with a compatible direction pattern.

CREATE INDEX posts_feed_idx ON posts (created_at DESC, id DESC);

For ORDER BY created_at DESC, id DESC that index 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 above, because no single backward scan produces that ordering. The same applies per sort option: a feed offering "newest", "most viewed", and "alphabetical" needs three composite indexes, each ending in the tie-breaker column.

Filters change the requirement too. If the feed is always scoped to a tenant, lead with the filter column — (tenant_id, created_at DESC, id DESC) — so the seek happens inside the tenant's slice.

How do I keep pages stable when rows are inserted or deleted mid-scroll?

Keyset pagination survives concurrent writes because the cursor names values rather than positions. Deleting the row a cursor points at does not break the next request: (created_at, id) < ($1, $2) is evaluated against the values, and those values do not need to still exist in the table.

The sort key still has to be unique as a whole. Sorting by a non-unique column alone — view_count, score, a truncated date — means several rows share the cursor value, and a strict < drops all of their siblings while a non-strict <= repeats the boundary row.

The bug that cost me an afternoon was precision, not uniqueness. PostgreSQL timestamptz stores microseconds; a JavaScript Date holds milliseconds. Round-tripping a cursor through new Date(value).toISOString() truncates the last three digits, and the truncated value sorts after the real row, so the next page quietly skipped every row that shared that millisecond. The fix: treat the timestamp as an opaque string, put the driver's raw value in the cursor, and send it back to PostgreSQL as text to be cast. Never parse it into a Date on the way through.

How should I encode the cursor in a public API?

Encode the cursor as base64url JSON so clients treat it as opaque, and validate it on the way back in as untrusted input. Opaque is not the same as secret: base64 is an encoding, not encryption.

type Cursor = { createdAt: string; id: string };

const encode = (c: Cursor) =>
  Buffer.from(JSON.stringify(c)).toString('base64url');

const decode = (raw: string | null): Cursor | null => {
  if (!raw) return null;
  try {
    const p = JSON.parse(Buffer.from(raw, 'base64url').toString());
    if (typeof p?.createdAt !== 'string' || typeof p?.id !== 'string') return null;
    return p;
  } catch {
    return null;
  }
};

Two rules I apply to every cursor endpoint. First, a malformed cursor returns HTTP 400, not page 1 — silently falling back to the first page turns a client bug into an infinite scroll loop. Second, the cursor must not carry authorization: re-apply tenant and permission filters on the server from the session, so a user who edits a decoded cursor gets an empty page rather than somebody else's rows.

How does keyset pagination 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 off the last page's response.

const feed = useInfiniteQuery({
  queryKey: ['posts', sort],
  initialPageParam: null as string | null,
  queryFn: ({ pageParam, signal }) =>
    fetch(`/api/posts?sort=${sort}&cursor=${pageParam ?? ''}`, { signal })
      .then((r) => r.json()),
  getNextPageParam: (last) => last.nextCursor ?? undefined,
});

Returning undefined from getNextPageParam is what sets hasNextPage to false; returning null does not, and that mismatch is the usual reason a "Load more" button never disappears. Putting sort in the queryKey matters as much: changing the sort order invalidates every cursor.

If the list can grow to thousands of rows, the maxPages option caps how many pages stay in the cache, but it 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?

An admin table over a few thousand rows with page numbers is a perfectly good use of OFFSET. A public feed, an activity log, an export endpoint, or anything an integration will walk end to end should be keyset from the start, because those are exactly the access patterns that reach page 400.

For the middle ground I use a hybrid: keyset for the "load more" path that real users take, plus a capped OFFSET path for the admin UI that stops offering page numbers past a fixed depth. Capping is honest — nobody browses to page 800 of a list; they are either scripting it or they needed search instead.

FAQ

Q: Can I 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".

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

A: Yes, as long as each sort option has a composite index ending in the primary key and the cursor records which sort it was created under. 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. Always confirm with EXPLAIN that the engine produced an index range scan rather than a filter.

Q: Is base64 enough to stop clients from tampering with cursors?

A: No. Validate the decoded shape, re-apply authorization filters server-side, and sign the cursor with an HMAC if you need to detect tampering rather than merely survive it.

Originally published on devya.dev. Also on eng-ahmed.com. Built by Devya Solutions.