OFFSET vs keyset pagination

SQL, indexing and PostgreSQL Mid-level Python 3.13+ PostgreSQL 17+ Reviewed 31 Oct 2026 By Best Latest Apps

Predict the output

The client saw rows up to (ts=3, id=7). What does the next page return?

SQL · PostgreSQL 17

CREATE TABLE posts (id int PRIMARY KEY, ts int NOT NULL);
INSERT INTO posts VALUES (9, 4), (8, 3), (7, 3), (6, 3), (5, 2);
SELECT id FROM posts
WHERE (ts, id) < (3, 7)
ORDER BY ts DESC, id DESC
LIMIT 2;

Options6 then 55 only8 then 67 then 6

Hint 1 of 1

Compare tuples left to right.

Choose an answer, then open Show answer below to check it.

Show answer

Answer: A 6 then 5

Correct. (3, 6) and (2, 5) are the next keys below (3, 7) in descending order.

A row comparison like (ts, id) < (3, 7) compares ts first, then id when ts ties. That matches the ORDER BY, so the next page starts right after the last row the client saw.

Why not the others?

B 5 only. Comparing on ts alone would skip id 6, which shares ts = 3.
C 8 then 6. (3, 8) is greater than (3, 7), so it was on an earlier page.
D 7 then 6. The cursor row itself is excluded by the strict <.

Result on PostgreSQL 17 (psql)

id
6
5

(2 rows)

30-second answer

OFFSET makes the database produce and throw away every skipped row, so page 5,000 costs as much as reading 100,000 rows, and rows shift between pages when data changes. Keyset (cursor) pagination remembers the last row's sort key and asks for rows after it: WHERE (created_at, id) < ($1, $2) ORDER BY created_at DESC, id DESC LIMIT 20. With a matching index each page costs the same.

Practise saying it · 30 s. No recording, just a timer

Explain keyset pagination.

Say your answer out loud, then check which of these points you covered:

OFFSET reads and discards
Pages drift on inserts
WHERE (key, id) < cursor
Opaque cursor token
Matching index

Deep dive

Include a unique tie-breaker (id) in the sort, or rows with equal timestamps get skipped or repeated.

Row-value comparison (a, b) < (x, y) can use a composite index on (a, b).

Expose the cursor to clients as an opaque token (for example base64 of the last key), not raw SQL values.

Keyset can't jump to page 37; most feeds and APIs don't need that.

Common mistakes

Sorting by a non-unique column only.
Building cursors clients can tamper with to read other tenants' rows.
Using OFFSET for infinite scroll on large tables.

Senior take & follow-ups

Name both problems with OFFSET (cost and drift), show the row-value WHERE clause, and require a unique tie-breaker.

Follow-up questions

Think of your answer first, then open the hint.

How do you paginate backwards?
Show hint
Flip the comparison and the sort, then reverse the page.
How do you show total pages?
Show hint
Usually don't; an approximate count or 'more' flag is cheaper.

Suggest a fix if something here is wrong or out of date.