Pagination That Survives Writes
Why OFFSET gets slow and silently skips rows, and how keyset pagination over a stable composite ordering fixes both.
GET /orders?limit=50&cursor=<opaque> -> { "data": [...], "has_more": true, "next_cursor": "..." }LIMIT 20 OFFSET 40000 asks the database to produce 40,020 rows in order and discard 40,000 of them. Cost grows with the page number, so the last page of an export is the slowest request your API serves.
Why it is also wrong
Offset is a position in a result set, not a position in the data. If three rows are inserted ahead of the reader between page 3 and page 4, everything shifts and the reader sees three rows twice. Deletions skip rows instead. Nobody notices until a nightly export is missing records and nobody can reproduce it.
Keyset pagination
Order by something stable and unique — a timestamp plus the primary key as tiebreaker — and ask for "rows after this exact point" rather than "rows after this many rows". The index seeks straight there, so page 2,000 costs what page 1 costs. Two constraints: the ordering columns must match the index you want used, and the tiebreaker is not optional. Ordering by created_at alone, with two rows sharing a timestamp across a page boundary, loses or repeats them.
Opaque cursors
Return the cursor as one base64url string the client never parses. The moment a client can read created_at out of it you can no longer change the ordering, add a tiebreaker, or move to a different store. Sign it if a forged cursor could reach another tenant's rows. At the end of the list return an empty array with a null cursor, or has_more: false as Stripe's list endpoints do — never a 404.
Example
import crypto from 'node:crypto';
const SECRET = process.env.CURSOR_SECRET;
// Opaque and signed: clients treat it as a token, we stay free to change it.
function encodeCursor(row) {
const payload = Buffer.from(JSON.stringify({
t: row.created_at.toISOString(), i: row.id,
})).toString('base64url');
const mac = crypto.createHmac('sha256', SECRET).update(payload).digest('base64url');
return `${payload}.${mac}`;
}
function decodeCursor(cursor) {
const [payload = '', mac = ''] = cursor.split('.');
const want = crypto.createHmac('sha256', SECRET).update(payload).digest('base64url');
// timingSafeEqual throws on length mismatch, so check length first.
if (mac.length !== want.length ||
!crypto.timingSafeEqual(Buffer.from(mac), Buffer.from(want))) {
throw new HttpError(400, 'invalid_cursor');
}
return JSON.parse(Buffer.from(payload, 'base64url').toString());
}
export async function listOrders({ accountId, limit = 50, cursor }) {
const size = Math.min(Number(limit) || 50, 100); // cap it: the client does not decide your page cost
const after = cursor ? decodeCursor(cursor) : null;
// Index: (account_id, created_at DESC, id DESC)
// Fetch size + 1 rows; the extra row is how we know more exist.
const { rows } = await db.query(
`SELECT id, created_at, total_cents
FROM orders
WHERE account_id = $1
AND ($2::timestamptz IS NULL OR (created_at, id) < ($2, $3))
ORDER BY created_at DESC, id DESC
LIMIT $4`,
[accountId, after?.t ?? null, after?.i ?? null, size + 1]);
const has_more = rows.length > size;
const data = rows.slice(0, size);
return { data, has_more, next_cursor: has_more ? encodeCursor(data.at(-1)) : null };
}When to use it
- A list endpoint that times out for the one customer with 2 million rows
- A nightly sync that misses records whenever writes land during the run
- Adding cursors to an existing offset-paged endpoint without breaking current clients
- Deciding what a list endpoint returns once the client has reached the end
More examples
The same predicate, shorthand and longhand
These are the same thing: PostgreSQL's row constructor is sugar for the OR form. Write the shorthand by default, and fall back to the longhand when EXPLAIN shows the planner refusing to turn the row comparison into an index range — MySQL's optimiser is noticeably pickier about row constructors than PostgreSQL's.
-- Shorthand: a row-value comparison, compared left to right.
SELECT id, created_at FROM orders
WHERE account_id = 7
AND (created_at, id) < ('2026-03-01 10:00:00+00', 918273)
ORDER BY created_at DESC, id DESC LIMIT 50;
-- Longhand: identical result set, written out.
SELECT id, created_at FROM orders
WHERE account_id = 7
AND (created_at < '2026-03-01 10:00:00+00'
OR (created_at = '2026-03-01 10:00:00+00' AND id < 918273))
ORDER BY created_at DESC, id DESC LIMIT 50;Offset against keyset at depth
Run both with EXPLAIN (ANALYZE, BUFFERS) on a table with a few million rows and the difference is in the buffer counts, not just the timing. Reach for this when a list endpoint's p99 is fine but its slowest requests all come from the same batch job walking to the end.
-- Deep offset: the engine orders 40,020 rows and throws 40,000 away.
-- Cost rises with the page number, and concurrent inserts shift every page.
SELECT * FROM orders WHERE account_id = 7
ORDER BY created_at DESC, id DESC LIMIT 20 OFFSET 40000;
-- Keyset: one index seek plus 20 rows, whatever the depth.
-- EXPLAIN (ANALYZE) shows Index Scan ... Rows Removed by Filter: 0
SELECT * FROM orders WHERE account_id = 7
AND (created_at, id) < ('2026-02-14 08:11:02+00', 771920)
ORDER BY created_at DESC, id DESC LIMIT 20;Client-side auto-pagination
An async generator is the shape most clients want: the caller writes `for await (const order of allOrders(t))` and never sees a page. The important detail is that the loop ends on a null cursor from the server rather than on a short page, because a short page is legal mid-list when rows are filtered after the index scan.
export async function* allOrders(token) {
let cursor = null;
do {
const url = new URL('https://api.example.com/orders');
url.searchParams.set('limit', '100');
if (cursor) url.searchParams.set('cursor', cursor);
const res = await fetch(url, { headers: { Authorization: `Bearer ${token}` } });
if (!res.ok) throw new Error(`list failed: ${res.status}`);
// Destructuring is the shorthand; the long form is
// const body = await res.json(); const data = body.data; ...
const { data, next_cursor } = await res.json();
yield* data;
cursor = next_cursor; // null ends the walk; never guess the next page
} while (cursor);
}
Discussion