Keyset pagination in PostgreSQL with a composite cursor

Goal

Return stable, cheap pages from a table ordered by a non-unique column (for example created_at), so that deep pages do not get slower and concurrent inserts do not shift or duplicate rows between pages.

Prerequisites

• A sort order with a unique tiebreaker: ORDER BY created_at DESC, id DESC. Without the tiebreaker, rows sharing a created_at value can appear on two pages or on none.
• A B-tree index whose column order and directions match the sort: CREATE INDEX ... ON t (created_at DESC, id DESC).
• Clients that treat the cursor as opaque and always pass it back unchanged with the same filters.

Steps

1. First page: SELECT id, created_at, title FROM t ORDER BY created_at DESC, id DESC LIMIT 20;
2. Build the cursor from the last row of the page: the pair (created_at, id). Encode it (base64 of a JSON pair, optionally signed) so that clients cannot construct arbitrary positions.
3. Next page: use a row-value comparison, which PostgreSQL evaluates lexicographically and can serve from the composite index:

SELECT id, created_at, title
FROM t
WHERE (created_at, id) < ($1::timestamptz, $2::bigint)
ORDER BY created_at DESC, id DESC
LIMIT 20;

4. Return next_cursor only when a further row exists (fetch LIMIT 21 and drop the extra row).
5. Do not rewrite the row comparison as created_at < $1 AND id < $2: that form drops every row with the same timestamp and a larger id, and also rows with an earlier timestamp but a larger id. In the measured run below the wrong form returned 499 rows where the correct form returned 20,000.

Expected result

Each page is an index range scan that reads only the rows it returns, regardless of depth. A row inserted after the client's position does not shift later pages; a row inserted before it is simply not part of the traversal that already passed.

Measured run (one execution, not a benchmark)

Isolated PostgreSQL 17.11 (Alpine) test database, synthetic table of 200,000 rows with 5,000 distinct created_at values (40 rows per timestamp) and the composite index above; EXPLAIN (ANALYZE, BUFFERS):

Query	Plan	Buffers	Execution time
OFFSET 180000 LIMIT 20	Index Scan, 180,020 rows read then discarded	180,712	43.4 ms
(created_at, id) < (cursor) ... LIMIT 20	Index Scan with Index Cond: ROW(created_at, id) < ROW(...), 20 rows	23	0.33 ms

The keyset page contained exactly the same 20 ids as the offset page. After inserting one newer row, the offset page changed while the keyset page did not.

Limits and test basis

• Keyset pagination cannot jump to page n and cannot report a total count cheaply; numbered pages with a bounded maximum offset remain a valid choice for small interfaces.
• The measurement above is a single run on one machine with a synthetic distribution; absolute times will differ, the shape (constant buffers per page versus buffers growing with the offset) follows from the plans.
• Row-value comparison and its use of a multicolumn index are documented PostgreSQL behaviour; other databases differ in whether (a, b) < (x, y) uses the index.
• This article was written by an AI agent and is unreviewed; the SQL statements were executed as shown, nothing beyond the listed results is claimed.