Offset vs Keyset Pagination Plans #

Page 1 of an activity feed returns in 0.4 ms. Page 800 returns in 320 ms, and the API’s own clients — a mobile app syncing history — routinely reach page 3,000, where each request costs more than a second. The query, the index and the data are unchanged; only the OFFSET grows.

The general shapes are compared in ORM pagination and batch writes. This page is the conversion, including the details that make keyset conditions correct.

The Planner Condition #

LIMIT n OFFSET m asks the executor to produce m + n rows and discard the first m. There is no way to skip them: ordering is a property of the result set, and an index provides order, not addressable positions. Every page therefore costs proportionally to its depth, and the sort bound becomes m + n, which past a point exceeds work_mem and turns a bounded top-N sort into a full sort.

Keyset pagination replaces the position with a value: the sort key of the last row of the previous page. The condition is a row comparison:

WHERE (created_at, id) < (:last_created_at, :last_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;

PostgreSQL compares row constructors lexicographically, and — crucially — a B-tree index on (created_at DESC, id DESC) can use the whole comparison as an index condition, so the scan starts exactly at the next row.

Three requirements:

  1. The ordering must be unique. Append a unique column (usually the primary key) to both the ORDER BY and the comparison, or rows with equal sort keys are repeated or skipped.
  2. Directions must match the index. With all columns descending, one row comparison works. Mixed directions — created_at DESC, priority ASC — cannot be expressed as a single row comparison and need either an index with matching mixed directions plus expanded conditions, or a reversal trick (negating a numeric column, or storing an inverted key).
  3. Nulls must be handled. NULLS LAST and NULLS FIRST change the comparison semantics; a nullable sort column needs explicit handling or, better, a non-null sort key.
What the executor does for page 800 Left, offset pagination: the index scan produces 16,000 rows, the Limit discards 15,980 of them, and buffers grow with page depth. Right, keyset pagination: the row comparison becomes part of the index condition, the scan starts at the next row and reads only 20, with constant buffers. LIMIT 20 OFFSET 15980 index scan produces 16,000 rows Limit discards 15,980 buffers grow with depth unstable across inserts WHERE (created_at, id) < (…) row comparison in Index Cond scan starts at the next row constant buffers per page stable across inserts both need an index matching the ORDER BY

Annotated EXPLAIN Evidence #

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at, kind FROM activity
WHERE account_id = 4821
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 15980;
Limit  (actual time=318.4..318.5 rows=20 loops=1)
  ->  Index Scan Backward using activity_account_created_id_idx on activity
        (actual time=0.04..316.2 rows=16000 loops=1)
        Index Cond: (account_id = 4821)
        Buffers: shared hit=18204
Execution Time: 318.6 ms
-- 16,000 rows produced, 15,980 discarded

Keyset:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at, kind FROM activity
WHERE account_id = 4821
  AND (created_at, id) < ('2026-08-14 09:12:04+00', 9041204)
ORDER BY created_at DESC, id DESC
LIMIT 20;
Limit  (actual time=0.03..0.04 rows=20 loops=1)
  ->  Index Scan Backward using activity_account_created_id_idx on activity
        (actual rows=20 loops=1)
        Index Cond: ((account_id = 4821) AND (ROW(created_at, id) < ROW('2026-08-14 09:12:04+00'::timestamptz, 9041204)))
        Buffers: shared hit=6
Execution Time: 0.05 ms
-- the whole comparison is an index condition

The wrong way to write it, which does not use the index as one condition:

WHERE created_at < '2026-08-14 09:12:04+00'
   OR (created_at = '2026-08-14 09:12:04+00' AND id < 9041204)

Bitmap Heap Scan on activity  (actual rows=18204 loops=1)
  Recheck Cond: …
-- logically equivalent, but planned as an OR: no single bounded scan
Row comparison or OR chain? Three items are annotated. An Index Cond containing a ROW comparison shows the whole keyset condition bounding the scan. An OR-based rewrite produces a bitmap scan instead. An offset plan shows rows produced far above rows returned. Index Cond: (… AND ROW(created_at, id) < ROW(…)) one bounded scan OR (created_at = … AND id < …) → Bitmap scan equivalent but slower Index Scan rows=16000 → Limit rows=20 offset discards the rest write the row comparison, not the expanded OR form

Step-by-Step Resolution #

  1. Ensure the index matches the ordering, including directions and a unique final column:

    CREATE INDEX CONCURRENTLY activity_account_created_id_idx
  ON activity (account_id, created_at DESC, id DESC);

  2. Return the cursor with each page. Encode the last row’s sort key values — not the page number — and treat it as opaque to clients:

    { "items": [...], "next_cursor": "eyJjIjoiMjAyNi0wOC0xNFQwOToxMjowNFoiLCJpIjo5MDQxMjA0fQ" }

  3. Write the condition as a row comparison, in the same order and direction as the ORDER BY.

  4. Handle the first page by omitting the condition rather than using a sentinel value.

  5. Keep the sort key non-null. If it can be null, either exclude nulls, or sort on coalesce() with a matching expression index.

  6. Deal with mixed directions by reversing one column’s stored value where practical, or by accepting a slightly larger scan with an index on the leading columns and a filter on the rest.

  7. Decide the count question separately: infinite scroll, approximate totals, or a cached count, as discussed in the parent guide.

Cost per page by depth Offset pagination costs 0.4 milliseconds at page 1, 41 milliseconds at page 100, 318 at page 800 and 1,204 at page 3,000. Keyset pagination costs 0.05 milliseconds at every depth. offset page 1 0.4 ms offset page 100 41 ms offset page 800 318 ms keyset, any page 0.05 ms mobile clients syncing history reach page 3,000 routinely

Before and After #

-- BEFORE: LIMIT 20 OFFSET 15980
Index Scan Backward (rows=16000) → Limit (rows=20)   Buffers: 18204     Execution Time: 318.6 ms

-- AFTER: WHERE (created_at, id) < (…) LIMIT 20
Index Cond: (… AND ROW(created_at, id) < ROW(…))     Buffers: 6         Execution Time: 0.05 ms

What keyset pagination gives up #

It cannot jump to an arbitrary page number, because there is no “page 500” without counting. Interfaces that show numbered pages need either offset pagination — acceptable when depth is bounded, as in an admin screen where nobody goes past page ten — or a different design: filters that narrow the set, or jump-to-date controls that translate directly into a cursor.

Backwards navigation works by reversing both the comparison and the ordering, then reversing the result in the application. Implementations usually carry a direction flag with the cursor.

Finally, the cursor encodes data. Making it opaque — base64 of a small JSON object, or a signed token — prevents clients from constructing arbitrary values, which matters when the sort key is sensitive or when a crafted cursor could be used to probe data. Signing also lets the server reject cursors from an older schema after the sort key changes, rather than returning confusing results.

Common Pitfalls #

Non-unique sort keys. Rows repeat or vanish between pages. Diagnostic signal: duplicate items in client lists. Fix: append the primary key to both the ordering and the comparison.

OR-expanded conditions. Logically right, planned worse. Diagnostic signal: a bitmap scan instead of a bounded index scan. Fix: use the row comparison form.

Mismatched index directions. The scan cannot be bounded. Diagnostic signal: a Sort node above the index scan. Fix: declare directions in the index.

Page-number APIs. Clients iterate deeply. Diagnostic signal: high offsets in logged statements. Fix: cursors in the API contract, not just internally.

Frequently Asked Questions #

Why is OFFSET slow in PostgreSQL? #

The server must produce and discard every row before the offset, so cost grows with page depth. There is no way to seek directly to the n-th row of an ordered result.

How do I write a keyset pagination condition? #

Use a row comparison on the sort columns plus a unique tiebreaker, in the same order and direction as the ORDER BY: WHERE (created_at, id) < (:last_created_at, :last_id). A matching composite index turns the whole comparison into an index condition.

Can keyset pagination jump to a specific page? #

No. It moves forward and backward from a known position. Interfaces that need numbered pages either keep offset pagination with bounded depth or replace page numbers with filters and cursors.

Up: ORM Pagination and Batch Writes