Skip to content

Pagination, offset vs keyset/cursor, with filtering and sorting

An offset is a position in a list that keeps changing; a keyset cursor is the last row itself, found again by an index.

Predict first

Before you read: predict

A box office's support desk works through open support tickets, oldest first, 20 per page: SELECT id FROM tickets WHERE status = 'open' ORDER BY created_at, id LIMIT 20 OFFSET 20 * (p - 1). An agent opens page 1, resolves two of the tickets on it, then opens page 2. Nothing else changes.

What does page 2 show?

Pick one

The 30-second version

OFFSET n reads and discards n rows, so deep pages cost more, and it counts positions: a row added above the page boundary between requests repeats on the next page, a row removed there skips one. A keyset cursor carries the last row's sort values plus a unique id, and the next page seeks past them through a matching index.

An offset is a position in a list that keeps changing; a keyset cursor is the last row itself, found again by an index.

How it works

Step 1 of 14

LIMIT n OFFSET m returns rows m+1 to m+n of the ordered result. Without a unique ORDER BY, which rows those are is unpredictable.

Analogy: A marathon's results board, read out over the phone

A volunteer reads the live board to runners' families. The board lists finishers by chip time, then by bib number, and keeps changing as timing mats upload.

At the results board In the system
"read me lines 21 to 40" OFFSET (step 3)
a late upload adds faster finishers above line 20: the next call repeats names an insert above the boundary (step 4)
a runner struck off above line 20: one name slips past unread a delete above the boundary (step 5)
"carry on after 3:12:05, bib 4417" a keyset cursor (steps 6, 8)
two runners on 3:12:05: "after 3:12:05" alone skips the higher bib the tiebreaker (step 7)
the board is sorted by time, then bib, so the volunteer finds 3:12:05 at once the matching index (step 10)
a caller asking for "the fifth screen" jumping to a page (step 14)

In real terms: before shipping a list endpoint, write down its sort with a unique last column, and check that an index has exactly those columns.

Breaks down when: you picture the note "after 3:12:05, bib 4417" as a pointer to that runner's line. It is the time and bib themselves, copied off the board.

Contrasting case: a list nobody edits while it is read, such as last year's final results. Offset pages are then stable; only their cost grows with depth (step 2).

Examples, run for real

4.1 ExamHall: the proctor event feed

EXH-1300: the proctor console pages exam events newest first, 50 per page, by OFFSET. New events arrive all the time; here the script inserts 7 between two requests. MR !1500 switches to keyset on (occurred_at, id).

4.1 proctor feed, newest first, 50 per page; 7 events inserted between page 1 and page 2
  OFFSET   page 1 #300–#251 · page 2 #257–#208 · repeated 7 · missed 0
  keyset   page 1 #300–#251 · page 2 #250–#201 · repeated 0 · missed 0
  keyset, next pull from the top: #307–#300, … (the 7 new events come first)

The 7 inserts push page 1's last 7 events onto page 2 (step 4). The keyset page starts after the last event shown (step 9); the new events wait for the next pull from the top.

4.2 Depth: what one page reads

4.2 200,000 events, index (occurred_at, id), 20 per page: rows the scan reads for one page
  page 1     OFFSET 0      Index Only Scan Backward using feed_occ_id · Sort no · scan read 20 · removed by filter 0 · returned 20
  page 1     keyset        Index Only Scan Backward using feed_occ_id · Sort no · scan read 20 · removed by filter 0 · returned 20
  page 100   OFFSET 1980   Index Only Scan Backward using feed_occ_id · Sort no · scan read 2000 · removed by filter 0 · returned 20
  page 100   keyset        Index Only Scan Backward using feed_occ_id · Sort no · scan read 20 · removed by filter 0 · returned 20
  page 2500  OFFSET 49980  Index Only Scan Backward using feed_occ_id · Sort no · scan read 50000 · removed by filter 0 · returned 20
  page 2500  keyset        Index Only Scan Backward using feed_occ_id · Sort no · scan read 20 · removed by filter 0 · returned 20

Same plan shape, same 20 rows returned. OFFSET's scan reads every skipped row (step 2); the keyset seek reads 20 at any depth (step 10). "Removed by filter" counts entries a scan read and then discarded: none here.

4.3 The cursor, the Link header and the allow-list

4.3 GET /events?limit=3
  items #307–#305 · next eyJzIjoibmV3ZXN0IiwidiI6…
  decoded payload: {"s":"newest","v":["2026-10-06T09:10:10.000Z","305"]}
  Link: </events?sort=newest&limit=3&cursor=…>; rel="next"
  follow the Link: 200 · items #304–#302
  edited payload                400 cursor rejected: signature
  newest cursor on sort=oldest  400 cursor rejected: made for sort "newest"
  sort=kind                     400 sort not allowed: kind

The payload is the last row's values and its sort, signed (step 12). Clients follow rel="next" without reading it. An edited cursor, a cursor from another sort and a sort off the list are all refused (step 13).

Picture it

Concept illustration for this lesson.
An illustration to help it stick. The mechanism is in the sections above.

Try it

Answer first, then check. Nothing is saved.

Question 1 of 5

A shop pages products cheapest first, 5 per page: SELECT id, price_cents FROM products WHERE price_cents > $1 ORDER BY price_cents LIMIT 5, where $1 is the last price on the previous page. Customers say some products never appear.

Which fix shows every product exactly once?

Pick one

Question 2 of 5

A payments audit log pages newest first with WHERE created_at < $1 OR (created_at = $1 AND id < $2) ORDER BY created_at DESC, id DESC LIMIT 20, and has an index on (created_at, id). A client is 50,000 rows deep.

What does the next page's scan read?

Pick one

Question 3 of 5

A support inbox lists 25 open chat conversations, #1–#25, longest-waiting first: by the time of each conversation's last message, then by id. It shows 10 per page, with a signed keyset cursor that holds the last row's last-message time and id. An agent opens page 1 (#1–#10). The customer in #3 then sends a new message, and the agent pages on to the end.

What does the agent see?

Pick one

Question 4 of 5

A ticketing site's venue-reviews API returns next = the last review's id. The next page runs:

SELECT id, created_at FROM reviews
WHERE (created_at, id) < (SELECT created_at, id FROM reviews WHERE id = $1)
ORDER BY created_at DESC, id DESC LIMIT 20;

Moderators remove reviews all day. Now and then a page comes back empty although older reviews exist.

Which is the bug?

Pick one

Question 5 of 5

A marketplace lists one seller's 60 items most expensive first and, among equal prices, oldest listing first, 20 per page:

SELECT id, price_cents FROM listings
WHERE seller_id = 7 AND <seek>
ORDER BY price_cents DESC, id ASC LIMIT 20;

The signed cursor carries the last item's price_cents as $1 and its id as $2. Many items share a price.

Which <seek> shows every item exactly once?

Pick one

Go deeper

Pro tips
  1. Give every list endpoint a total sort before it ships. End the sort with the primary key, use the same columns in the seek and in the index, and put the sort's name in the cursor. When: any new list or feed endpoint (steps 7, 10, 12).
  2. Keep OFFSET where it is honest. A numbered, filtered admin table with a few dozen pages can offer "jump to page 37"; cap the page number it accepts, and use keyset for feeds and exports. When: an endpoint needs both page jumps and deep scrolling (steps 2, 14).
Source
  • PostgreSQL 17, LIMIT and OFFSET: https://www.postgresql.org/docs/17/queries-limit.html · fetched 2026-09-28. "it is important to use an ORDER BY clause that constrains the result rows into a unique order. Otherwise you will get an unpredictable subset of the query's rows"; "The rows skipped by an OFFSET clause still have to be computed inside the server; therefore a large OFFSET might be inefficient" (steps 1–2). Valid as of 2026-09-28.
  • PostgreSQL 17, Row Constructor Comparison (Comparison Functions and Operators, §9.25.5): https://www.postgresql.org/docs/17/functions-comparisons.html · fetched 2026-09-28. For <, <=, > and >=, "the row elements are compared left-to-right, stopping as soon as an unequal or null pair of elements is found. If either of this pair of elements is null, the result of the row comparison is unknown (null)". Valid as of 2026-09-28.
  • Link = #link-value, link-value = "<" URI-Reference ">" *( OWS ";" OWS link-param ). The relation next is not defined in RFC 8288; the IANA Link Relations registry (https://www.iana.org/assignments/link-relations/, fetched 2026-09-28) lists it as "Indicates that the link's context is a part of a series, and that the next in the series is the link target", referencing HTML. Valid as of 2026-09-28.
  • Observed, not quoted: step 11 and P2 are plan observations from practice.mjs on this data, not documented guarantees, and a later planner may do better. They show that PostgreSQL 17 starts no ordered index scan at the OR form of the keyset condition, that it can seek each arm through a BitmapOr and then sort, and that it chose the filtered backward scan at this depth.

Related lessons

Checked against the tools and docs as of 2026-09-28.