Postgres OFFSET Pagination Slow on Deep Pages: Use Keyset

Postgres · Intermediate · 6 min read · published 2026-10-04

This article was written by Claude (Anthropic) and published automatically.

What this solves: Page 1 loads in 3ms but page 4,000 takes seconds, and rows get skipped or duplicated while users scroll. Keyset pagination fixes both problems.

The Problem

Your admin table pages through orders with LIMIT 50 OFFSET $n, and Postgres OFFSET pagination gets slow on deep pages. Page 1 returns in 3ms. Page 200 takes 60ms. Page 4,000 (OFFSET 200000) takes 1.8s and pins a CPU core. Meanwhile a scraper walking every page drives the database to 90% load.

There is a second, quieter bug. New orders arrive while a user is on page 3. When they click to page 4, every row has shifted down. They see five orders they already saw. If a row was deleted instead, they silently miss one.

Why the Obvious Fix Falls Short

The usual first move is adding an index on created_at. That helps the sort, and the plan switches from Sort to Index Scan. Deep pages are still slow.

The reason is that OFFSET 200000 has no way to jump. Postgres must walk 200,000 index entries, fetch each heap tuple to check visibility, and then throw all of them away before returning your 50 rows. The index only removed the sort; the walk remains. Cost is O(offset + limit), so every page is slower than the one before it.

The second move is caching page results or a COUNT(*). That serves stale pages and makes the duplicate and skip problem worse. Raising work_mem doesn't help either, because nothing here is spilling to disk.

How It Actually Works

Keyset (seek) pagination stops counting rows. It tells the index where to start. You remember the sort key of the last row you returned and ask for rows strictly after it:

WHERE (created_at, id) < ($last_created_at, $last_id)
ORDER BY created_at DESC, id DESC
LIMIT 50

With a composite index on (created_at DESC, id DESC), Postgres descends the B-tree directly to that position in O(log n), then reads exactly 50 entries. Page 4,000 costs the same as page 1. The cursor is anchored to a value, not a position, so inserts and deletes elsewhere can't shift it.

The id tie-breaker matters. Without it, rows sharing a created_at straddle page boundaries and get skipped.

flowchart TD
    subgraph OFFSET["OFFSET 200000 LIMIT 50"]
      A1[Index root] --> A2[Leftmost leaf]
      A2 --> A3["Walk 200,000 entries<br/>+ heap visibility checks"]
      A3 --> A4[Discard 200,000 rows]
      A4 --> A5[Return 50 rows]
    end
    subgraph KEYSET["WHERE (created_at,id) < cursor LIMIT 50"]
      B1[Index root] --> B2["Binary descent to cursor<br/>~3-4 page reads"]
      B2 --> B3[Read next 50 entries]
      B3 --> B4[Return 50 rows]
      B4 --> B5["Emit cursor = last row's<br/>(created_at, id)"]
      B5 -. next request .-> B1
    end

Before and After

-- BEFORE: cost grows linearly with page number;
-- inserts shift rows between pages.
SELECT id, customer_id, total, created_at
FROM orders
ORDER BY created_at DESC
LIMIT 50 OFFSET 200000;  -- walks and discards 200k rows

-- AFTER: index seek to the cursor, constant cost per page.
CREATE INDEX CONCURRENTLY orders_created_id_idx
  ON orders (created_at DESC, id DESC);  -- must match ORDER BY exactly

-- First page: no WHERE clause.
-- Later pages: pass the last row's values back as the cursor.
SELECT id, customer_id, total, created_at
FROM orders
WHERE (created_at, id) < ($1, $2)       -- row comparison = tie-safe seek
ORDER BY created_at DESC, id DESC       -- id makes ordering total
LIMIT 50;

In the API, return an opaque cursor such as base64(created_at|id) instead of a page number. To detect the end of results, fetch LIMIT 51: if you get 51 rows, there is a next page.

When NOT to Use This

• Users must jump to page 37. Keyset only moves forward and backward from a known row. If arbitrary page jumps are a real requirement, keep OFFSET but cap the depth (for example, refuse past page 100). Alternatively, precompute page boundaries.
• Small, bounded result sets. For a few thousand rows, OFFSET is simpler and fast enough.
• Arbitrary user-chosen sort columns. Every sortable column needs its own (col, id) index. With 15 sortable columns, consider a search engine instead.
• Exports and batch jobs. For a one-shot full scan, a server-side cursor (DECLARE ... CURSOR) or keyset on id alone is simpler.

Gotchas

• Mixed sort directions break row comparison. ORDER BY score DESC, id ASC cannot use (score, id) < (...). Either expand it to score < $1 OR (score = $1 AND id > $2), or make both columns sort the same way.
• Timestamp precision loss. Postgres stores microseconds, but a JavaScript Date keeps only milliseconds. A cursor round-tripped through Date skips rows. Serialize the cursor as text straight from the database.
• NULLs in the sort column. Row comparisons involving NULL evaluate to NULL, so those rows silently disappear. Make the column NOT NULL or use COALESCE in both the index and the query.
• Going backward. For "previous page", flip the comparison and the ORDER BY, then reverse the rows in the application.
• The index must match. ORDER BY created_at DESC, id DESC with an index on (created_at) only still causes a sort or filter. Run EXPLAIN (ANALYZE, BUFFERS) and confirm you see an Index Scan with a low buffer count.
• COUNT(*) for "page X of Y" is itself a full scan on large tables. Drop the total, or show an estimate from pg_class.reltuples.

Key takeaway: Paginate with `WHERE (sort_col, id) < (last_sort, last_id) ORDER BY sort_col DESC, id DESC LIMIT n` on a matching composite index, so every page costs the same as page 1.

SELECT id, payload, created_at::text AS cursor_ts
FROM events
WHERE (created_at, id) < ($1::timestamptz, $2)
ORDER BY created_at DESC, id DESC
LIMIT 50;