Pagination That Survives a Large Table

SELECT id, created_at, total
  FROM orders
 WHERE tenant_id = $1
 ORDER BY created_at DESC
 LIMIT 20 OFFSET 400000;

-- The engine must produce 400,020 rows in order
-- and discard 400,000 of them.
--
-- Cost grows with the offset. Walking the whole
-- table page by page is quadratic in row count.
--
-- Plus, for a page-number UI:
--   SELECT count(*) FROM orders WHERE tenant_id = $1;
-- which scans the whole filtered set, every page.

-- Cursor carries the last row's ordering tuple.
SELECT id, created_at, total
  FROM orders
 WHERE tenant_id = $1
   AND (created_at, id) < ($2, $3)   -- DESC: strictly before
 ORDER BY created_at DESC, id DESC
 LIMIT 20;

-- Index seek to the cursor position, then read 20
-- entries. Identical cost at any depth.

CREATE INDEX ON orders (tenant_id, created_at DESC, id DESC);
-- The index must match the ORDER BY exactly --
-- columns, order and direction -- or the engine
-- sorts and the advantage disappears.

The two queries express different operations. Offset is "count rows and discard", which is inherently proportional to how deep you are; keyset is "seek to a value", which is a tree descent of the same cost regardless of position. The id tiebreaker is not decoration: without it, orders sharing a created_at have no defined relative order, and rows at page boundaries are skipped or duplicated on every request.

1const MAX_PAGE = 100
2 
3type Cursor = { v: 1; createdAt: string; id: string }
4 
5const encode = (c: Cursor) =>
6  Buffer.from(JSON.stringify(c)).toString('base64url')
7 
8function decode(s: string | undefined): Cursor | null {
9  if (!s) return null
10  try {
11    const c = JSON.parse(Buffer.from(s, 'base64url').toString())
12    // validate: it is untrusted input, not a token we issued
13    if (c?.v !== 1 || typeof c.id !== 'string') return null
14    if (Number.isNaN(Date.parse(c.createdAt))) return null
15    return c
16  } catch { return null }
17}
18 
19export async function listOrders(tenantId: string, q: Query) {
20  // clamp, do not reject: a client asking for 100000 gets 100
21  const limit = Math.min(Math.max(q.limit ?? 20, 1), MAX_PAGE)
22  const cur = decode(q.cursor)
23 
24  // fetch limit + 1: the extra row answers "is there more"
25  // without a count query
26  const rows = await db.query(
27    `SELECT id, created_at, total
28       FROM orders
29      WHERE tenant_id = $1                 -- from the session,
30        AND ($2::timestamptz IS NULL       -- NEVER from the cursor
31             OR (created_at, id) < ($2, $3))
32      ORDER BY created_at DESC, id DESC
33      LIMIT $4`,
34    [tenantId, cur?.createdAt ?? null, cur?.id ?? null, limit + 1],
35  )
36 
37  const hasMore = rows.length > limit
38  const page = hasMore ? rows.slice(0, limit) : rows
39  const last = page.at(-1)
40 
41  return {
42    items: page.map(toDto),
43    nextCursor: hasMore && last
44      ? encode({ v: 1, createdAt: last.created_at.toISOString(), id: last.id })
45      : null,
46  }
47}

Three things carry their weight here. tenantId comes from the session and never from the cursor — a cursor that carried it would be an authorization bypass one base64 edit away (Object-Level Authorization). Fetching limit + 1 answers "is there more" for the cost of one row instead of a count(*). And the version field v is what lets the ordering key change later without every stored cursor becoming a crash.