Keyset Pagination — Why OFFSET Breaks at Scale

The problem: OFFSET is “read and discard”

You’ve built pagination a hundred times: SELECT ... ORDER BY created_at DESC LIMIT 20 OFFSET N. Page 1 is fast. Page 100 is fine. Then a user digs to page 5000 — and your dashboard hangs. The query hasn’t changed. The index is there. What changed is how many rows PostgreSQL had to read and throw away before giving you the 20 you wanted.

To satisfy OFFSET 100000, PostgreSQL must traverse the index starting from the most recent row, count past 100,000 rows, discard all of them, and only then return the next 20. It reads 100,020 rows from the index and hands you 20. The other 100,000 are thrown away. This work is pure waste — the database did real I/O to fetch rows you will never show the user.

The deeper the page, the more rows discarded. This is why OFFSET pagination has O(N) cost per page where N is the offset value. Page 1 is cheap. Page 5000 is 100× more expensive. Page 10000 is 200× more expensive than page 1.

The mental model: a sorted list, not a random access array

Think of your events table as a list sorted by created_at — which it is, because you have a B-tree index on that column (Day 1). The index is a sorted structure. To get “page 1,” PostgreSQL walks to the end of the list (most recent) and grabs 20 items. Fast.

To get “page 5000” with OFFSET, PostgreSQL walks to the end of the list and counts forward 100,000 items before grabbing the next 20. It can’t jump — OFFSET has no “seek to position 100,000” operation. It’s a count-and-discard.

The fix is obvious once you see it this way: don’t count from the beginning. Tell PostgreSQL where to start. If you remember the last item you showed the user, you can say “give me 20 items older than this one.” PostgreSQL seeks directly to that position in the index — no counting, no discarding.

The fix: keyset pagination in three lines

Instead of telling PostgreSQL “skip N rows,” tell it “start from this specific row.” You do this by remembering the last value you showed on the previous page and using it as a filter:

-- Page 1: latest 20 events (no cursor yet)
SELECT id, user_id, created_at, payload
FROM events
ORDER BY created_at DESC
LIMIT 21;   -- fetch 21, show 20, use the 21st as the next cursor

-- Page 2 onward: "older than the last item I showed"
SELECT id, user_id, created_at, payload
FROM events
WHERE created_at < $last_seen_created_at
ORDER BY created_at DESC
LIMIT 21;

Why LIMIT 21, not 20? Fetch one extra row (21 instead of 20). If you get 21 rows back, there’s a next page — use the 20th row’s created_at as the cursor. If you get 20 or fewer, you’re on the last page. This avoids a separate COUNT(*) query to check if more pages exist — a query that would itself scan the whole table.

The WHERE created_at < $cursor condition is the entire trick. It transforms the query from “scan and discard N rows” into “seek to this position and read 20.” The B-tree index on created_at makes the seek O(log N) — effectively instant for any realistic table size.

Real benchmark: OFFSET vs keyset at depth

I ran a benchmark on real PostgreSQL 16 (Podman container, postgres:16-alpine). One million rows in an events table, indexed on created_at. 30 iterations per query, median reported. Page size: 20 rows.

The cost grows with depth — OFFSET, not keyset

Page depth	OFFSET median	Keyset median	Speedup
Page 0 (first page)	1.41ms	1.31ms	1.1×
Page 100	3.24ms	1.56ms	2.1×
Page 500	7.35ms	1.53ms	4.8×
Page 1,000	12.80ms	1.44ms	8.9×
Page 5,000	52.35ms	1.38ms	37.8×
Page 10,000	113.57ms	1.31ms	86.6×

Look at the shape of that data. OFFSET latency grows linearly with page depth — each page deeper costs more. Keyset latency is flat — page 10,000 is as fast as page 1. At page 10,000, keyset is 86.6× faster than OFFSET.

And this is on a 1-million-row table. On 10 million rows, the gap widens further — OFFSET page 10000 would scan over 2 million rows. The keyset query wouldn’t change at all.

What EXPLAIN reveals: buffer pages tell the real story

The median latency is one signal. The deeper story is in EXPLAIN (ANALYZE, BUFFERS) — Day 3’s tool. Here’s what PostgreSQL actually did at page 10,000.

OFFSET 200000 — the slow path

Limit  (cost=11863.56..11864.74 rows=20 width=32)
                           (actual time=171.149..171.159 rows=20 loops=1)
    Buffers: shared hit=201114
    ->  Index Scan Backward using idx_events_created_at on events
                           (actual time=0.073..164.999 rows=200020 loops=1)
          Buffers: shared hit=201114
Planning Time: 0.123 ms
Execution Time: 171.269 ms

200,020 rows read from the index (OFFSET 200000 + LIMIT 20). 201,114 buffer pages touched — roughly 1.5 GB of data read from shared buffers, just to return 20 rows. The index is being used (good), but OFFSET forces it to traverse 200K entries before it can start returning.

Keyset — the fast path

Limit  (cost=0.42..1.83 rows=20 width=32)
                           (actual time=0.082..0.104 rows=20 loops=1)
    Buffers: shared hit=23
    ->  Index Scan Backward using idx_events_created_at on events
          Index Cond: (created_at < '2026-10-19 23:31:01+00'::timestamptz)
                           (actual time=0.081..0.101 rows=20 loops=1)
          Buffers: shared hit=23
Planning Time: 0.128 ms
Execution Time: 0.139 ms

20 rows read. 23 buffer pages touched — about 184 KB. The Index Cond line is the key: PostgreSQL used the B-tree to seek directly to the cursor position, then read 20 rows forward. Execution time: 0.139ms vs 171.269ms — 1,232× faster at the database level.

Method (page 10,000)	Buffer pages	Data read
OFFSET 200000	201,114	~1.5 GB
Keyset	23	~184 KB

OFFSET reads 8,744× more data to return the same 20 rows.

The gotcha: you need a stable sort column

Keyset pagination has one requirement that OFFSET doesn’t: your sort column must have unique, stable values. If two rows share the same created_at, the cursor WHERE created_at < $cursor might skip rows or show duplicates.

The fix: use a tiebreaker column. Sort by (created_at, id) and use a compound cursor:

-- Stable keyset pagination with tiebreaker
SELECT id, user_id, created_at, payload
FROM events
WHERE (created_at, id) < ($last_seen_created_at, $last_seen_id)
ORDER BY created_at DESC, id DESC
LIMIT 21;

This is a row-value comparison — PostgreSQL compares the tuples lexicographically. If created_at ties, it falls back to id. Since id is a unique primary key, the sort is fully deterministic. No row is ever skipped or duplicated.

For the tiebreaker query to seek efficiently, you need a composite index on (created_at, id) — Day 6’s leftmost prefix rule.

When to use which: OFFSET isn’t always wrong

OFFSET isn’t evil — it’s misused.

• Use OFFSET when: small bounded result sets, admin pages (few pages deep), user needs “jump to page N,” result set is small (under 10K rows). OFFSET enables random access (page 5 → page 50 → page 3).
• Use keyset when: infinite scroll / “load more,” feeds/activity logs/timelines, large tables (100K+ rows), users only go forward. Keyset is sequential access (page N → page N+1 only).

The key constraint: keyset pagination only goes forward. You can’t jump to page 50 without first visiting pages 1–49, because you need the cursor from page 49. This is why it’s perfect for infinite scroll (“load more”) but not for an admin table where users want to jump to page 50 directly.

The connection: pagination ties the database curriculum together

Keyset pagination isn’t an isolated trick — it’s the payoff of everything you’ve learned about indexes:

• Day 1 (Indexes): Keyset pagination only works because a B-tree index is a sorted structure. The WHERE created_at < $cursor condition becomes an index range seek.
• Day 3 (EXPLAIN): EXPLAIN (ANALYZE, BUFFERS) is how you prove the seek happened. The Buffers: shared hit=23 vs shared hit=201114 comparison is the smoking gun.
• Day 6 (Composite Indexes): The tiebreaker pattern (created_at, id) is a composite index. The leftmost prefix covers the sort; the second column breaks ties.
• Day 8 (Connection Pooling): A slow OFFSET query (113ms at page 10,000) holds a database connection for 113ms instead of 1.3ms. Under load, deep pagination with OFFSET can exhaust the connection pool.
• Day 13 (Normalization): Normalized schemas have more rows. More rows means deeper pagination before users find what they want — which makes the OFFSET cost worse.
• Day 15 (JOINs): Paginating JOIN results has the same problem. SELECT ... JOIN ... ORDER BY ... LIMIT 20 OFFSET 100000 still scans and discards.

Indexes make the seek possible. EXPLAIN proves the seek happened. OFFSET ignores the index. Keyset uses it.

Quiz