Stop Using OFFSET for Pagination: Keyset Pagination Is 10,000x Faster on Deep Pages

Almost every API paginates like this:

SELECT * FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 40;   -- page 3

It works great on page 1. On deep pages it gets thousands of times slower, and it can show users the same item twice. Here's the proof, and the fix.

Video version:

Measured: 1,000,000 rows in PostgreSQL 17

I filled a table with a million posts (id, title, a 200-character body, created_at), added an index on (created_at, id), and timed the same 20-row page query at different offsets. These are medians of 7 runs:

OFFSET 0          0.022 ms
OFFSET 1000       0.041 ms
OFFSET 10000      0.319 ms
OFFSET 100000     6.217 ms
OFFSET 500000    32.102 ms
OFFSET 999980    63.890 ms   🐌  (the last page)
keyset (last)     0.006 ms   ⚡

The cost grows linearly with the page number. The last page is about 2,900× slower than the first, while the keyset query for the same last page is basically free.

Why OFFSET is slow

EXPLAIN ANALYZE shows the problem:

EXPLAIN ANALYZE SELECT * FROM posts ORDER BY id LIMIT 20 OFFSET 900000;

Limit (actual rows=20)
  ->  Index Scan using posts_pkey on posts (actual rows=900020)   ❌

The database can't jump to row 900,000. It reads 900,020 rows and throws away 900,000 of them. An index helps it read them in order, but it still has to walk through every skipped row.

The fix: keyset (cursor) pagination

Instead of a page number, remember the last row the user saw and ask for the rows after it:

-- first page
SELECT * FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- next page: pass in the last row's created_at and id
SELECT * FROM posts
WHERE (created_at, id) < ($last_at, $last_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;

The (created_at, id) < (…) row comparison means "older than the last post, and if the timestamps tie, a smaller id". The id is a tiebreaker: without it, two posts with the same timestamp could be skipped or repeated at a page boundary.

Add an index that matches the sort exactly:

CREATE INDEX idx_posts_feed ON posts (created_at, id);

Now the plan is:

Limit (actual rows=20)
  ->  Index Scan Backward using idx_posts_feed on posts (actual rows=20)   ✅
        Index Cond: (ROW(created_at, id) < ROW(...))

It reads exactly 20 rows, whether you're on page 1 or page 50,000.

In an API, you'd usually return the cursor with the results, for example next_cursor: "2026-11-10T12:00:00Z_9812" (often base64-encoded), and the client sends it back to get the next page.

Bonus bug: OFFSET shows duplicates (and skips rows)

OFFSET has a correctness problem too. With 10 posts and 3 per page:

page 1 (OFFSET 0)          → 10, 9, 8
-- someone publishes post 11
page 2 (OFFSET 3)          → 8, 7, 6    ❌ post 8 again!
page 2 (keyset: id < 8)    → 7, 6, 5    ✅

Everything shifted down by one, so the user sees post 8 twice. If a post is deleted between page loads, the opposite happens: in my test, deleting post 9 made page 2 return 6, 5, 4, and post 7 was never shown. On a busy feed with infinite scroll, this happens all the time.

Keyset pagination asks for "posts older than 8", which stays correct no matter what gets inserted or deleted.

When to use which

✅ Keyset pagination

• Infinite scroll, feeds, timelines
• Public APIs (cursor-based pagination)
• Exporting or syncing big tables in batches

✅ OFFSET is fine

• Small tables
• Admin pages where users really need "jump to page 37"

⚠️ The trade-offs of keyset

• No jumping to an arbitrary page number, only next and previous
• The sort must be unique (add id as a tiebreaker)
• You need an index on the sort columns

I tested the row-comparison syntax in PostgreSQL 17 and SQLite 3.51, and both handle it.

Does your API use OFFSET or cursors? 👇

---

I post one tested coding tip every day on YouTube at Refactorly. The 30-second version of this one is here.