Keyset pagination

Pages by value, not by position

OFFSET identifies a page by position: “rows 99,961 to 99,980”. Keyset identifies it by value: “the 20 rows that come right after the last one you saw”. The client keeps the last row’s sort values and sends them back as a cursor:

-- first page
SELECT id, created_at, payload FROM events
WHERE org_id = 7
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- next page: $2, $3 are the created_at and id of the last row of the previous page
SELECT id, created_at, payload FROM events
WHERE org_id = 7
  AND (created_at, id) < ($2, $3)
ORDER BY created_at DESC, id DESC
LIMIT 20;

(created_at, id) < ($2, $3) is the row comparison from The expression toolkit: earlier created_at, or the same created_at with a smaller id. That is exactly “comes after the cursor, in this order”.

Three rules make it work:

1. The order must be unique. created_at alone can tie; add the primary key as a tie-breaker, in both the ORDER BY and the cursor.
2. The index must match. Equality columns first, then the sort columns: (org_id, created_at, id). PostgreSQL can read it backward for DESC, DESC.
3. The cursor is the last row’s values, not a page number.

What it costs

The same one-million-row table and the same depths as the previous page, with each cursor taken from the last row of the page before:

Keyset does the same tiny amount of work at every depth. The plan shows why:

 Limit (actual rows=20.00 loops=1)
   Buffers: shared hit=6
   ->  Index Scan Backward using events_org_created_id on events (actual rows=20.00 loops=1)
         Index Cond: ((org_id = 7) AND (ROW(created_at, id) < ROW('2024-12-31 18:36:47+00'::timestamp with time zone, 407)))

The cursor became part of the index condition. PostgreSQL descends the B-tree straight to the cursor, reads 20 entries backward, and stops.

Pages that stay put

What keyset cannot do

• Jump to page 731. There is no cursor for a page you have not reached. Most feeds and APIs only need next (and previous, by flipping the comparison and the sort); “jump to page” interfaces are where OFFSET still fits, ideally with a cap on depth.
• Sort by arbitrary columns without an index for each sort order a user can choose.

Check yourself

1. Why must the keyset order include a unique column such as id?

   Show answer

   If the sort column can tie, rows with the same value have no defined order and the cursor cannot say which of them were already shown. Adding a unique tie-breaker makes the order, and the cursor, exact.

   I could explain this without looking

2. What index serves WHERE org_id = $1 AND (created_at, id) < ($2, $3) ORDER BY created_at DESC, id DESC?

   Show answer

   (org_id, created_at, id): the equality column first, then the sort columns. PostgreSQL scans it backward for the descending order.

   I could explain this without looking

3. Why does keyset not show duplicates when new rows arrive at the top?

   Show answer

   It asks for rows after a specific value, not after a number of positions. New rows at the top change positions but not which rows come after the cursor.

   I could explain this without looking