How it works

The explanation, with examples you can run.

OFFSET, measured

On 500,000 orders with an index on (created_at, id):

1EXPLAIN ANALYZE2SELECT id, created_at FROM orders3ORDER BY created_at, id4LIMIT 20 OFFSET 400000;

1Limit  (cost=12160.42..12161.03 rows=20 width=16) (actual time=20.032..20.033 rows=20 loops=1)2  ->  Index Only Scan using orders_created_id_idx on orders  (cost=0.42..15200.42 rows=500000 width=16) (actual time=0.034..14.033 rows=400020 loops=1)3        Heap Fetches: 04Execution Time: 20.039 ms

The index avoids a sort, but it still reads 400,020 entries to return 20. Without the index it was worse: a full sort of the table that spilled to disk (61 ms here). Page 20,000 of a busy table is reached by crawlers and scripts more often than by people, and each such request does this much work.

Keyset: continue after the last row

Remember the sort key of the last row on the page and ask for rows after it. A row comparison does this neatly, and matches the index column by column:

1EXPLAIN ANALYZE2SELECT id, created_at FROM orders3WHERE (created_at, id) > ('2025-10-10 18:40:00+01', 400000)4ORDER BY created_at, id5LIMIT 20;

1Limit  (cost=0.42..1.08 rows=20 width=16) (actual time=0.014..0.015 rows=20 loops=1)2  ->  Index Only Scan using orders_created_id_idx on orders  (cost=0.42..3049.08 rows=92609 width=16) (actual time=0.014..0.014 rows=20 loops=1)3        Index Cond: (ROW(created_at, id) > ROW('2025-10-10 18:40:00+01'::timestamp with time zone, 400000))4        Heap Fetches: 05Execution Time: 0.026 ms

The Index Cond lets the scan start at the boundary and read exactly 20 entries, however deep the page. Three rules make it correct:

The ORDER BY must be unique; add the primary key as a last column.
The index must match the ORDER BY (same columns, same order; for newest first, compare with < and order DESC on both).
The cursor you give the client is the last row's sort values, usually encoded as an opaque string.

Seeing both behaviours

This plain-JS simulation pages through 100 rows sorted by (createdAt, id), inserts a row at the front between pages, and counts the rows each method has to examine.

Your code

// 100 orders sorted by (createdAt, id); several share a timestamp
let rows = Array.from({ length: 100 }, (_, i) => ({ id: i + 1, createdAt: Math.floor(i / 3) }))
const byKey = (a, b) => a.createdAt - b.createdAt || a.id - b.id

// OFFSET: skip n rows, every time counting from the start
function offsetPage(page, size) {
  const sorted = [...rows].sort(byKey)
  return { rows: sorted.slice(page * size, page * size + size), examined: page * size + size }
}

// Keyset: continue after the last (createdAt, id) seen
function keysetPage(after, size) {
  const sorted = [...rows].sort(byKey)
  const start = after ? sorted.findIndex((r) => byKey(r, after) > 0) : 0
  return { rows: sorted.slice(start, start + size), examined: size }  // with an index, the database seeks straight to start
}

const p1 = offsetPage(0, 10)
const k1 = keysetPage(null, 10)
console.log("page 1 ids:", p1.rows.map((r) => r.id).join(","))

// A new row is inserted at the very start while the user reads page 1
rows.push({ id: 101, createdAt: -1 })

const p2 = offsetPage(1, 10)
const k2 = keysetPage(k1.rows.at(-1), 10)
console.log("offset page 2:", p2.rows.map((r) => r.id).join(","), "(id 10 shown twice)")
console.log("keyset page 2:", k2.rows.map((r) => r.id).join(","))
console.log("rows examined for page 9 -> offset:", offsetPage(8, 10).examined, " keyset:", keysetPage({ createdAt: 26, id: 80 }, 10).examined)

⌘/Ctrl + Enter

OutputNot run yet

Press Run to see what your code prints, in order.

Runs in this browser, in an isolated frame with no access to this page. console.log, info, warn and error are shown; code runs as a classic script, so use an async function for await.

The cost of keyset pagination is that you cannot jump to an arbitrary page number. For infinite scroll, "load more" and API cursors that is no loss; for a numbered admin table with a few pages, OFFSET is fine.

Check yourself

3 questions. Get them all right to finish this topic.

1.Why does SELECT ... ORDER BY created_at, id LIMIT 20 OFFSET 400000 get slow even with an index on (created_at, id)?
2.A user is on page 1 of a newest-first list when a new item is published. They click "next" (OFFSET 20). What do they see?
3.Why add id to ORDER BY created_at for keyset pagination?

0 of 3 right so far. Get all three to finish this topic.

Pagination: OFFSET versus keyset

How it works

OFFSET, measured

Keyset: continue after the last row

Seeing both behaviours

Check yourself

Go deeper