DataTypeScript

Keyset (cursor) pagination for Postgres

Paginate large tables with stable, fast queries that don't slow down on deep pages and don't skip or repeat rows when new data arrives, unlike OFFSET.

#postgres#pagination#sql#api

lib/list-orders.ts

import { Pool } from "pg";

const pool = new Pool({ connectionString: process.env.DATABASE_URL });

// Supporting index: CREATE INDEX orders_created_id_idx ON orders (created_at DESC, id DESC);

type Order = { id: string; customer_id: string; total_cents: number; created_at: Date };
type Row = Order & { cursor_ts: string };
type Cursor = { ts: string; id: string };

const encodeCursor = (c: Cursor) => Buffer.from(JSON.stringify(c)).toString("base64url");

function decodeCursor(value: string): Cursor | null {
  try {
    const c = JSON.parse(Buffer.from(value, "base64url").toString("utf8"));
    return typeof c?.ts === "string" && typeof c?.id === "string" ? c : null;
  } catch {
    return null;
  }
}

export async function listOrders({ limit = 50, cursor }: { limit?: number; cursor?: string } = {}) {
  const pageSize = Math.min(Math.max(limit, 1), 200);
  const after = cursor ? decodeCursor(cursor) : null;
  if (cursor && !after) throw new Error("Invalid cursor");

  const params: unknown[] = [pageSize + 1]; // one extra row tells us if there's a next page
  let where = "";
  if (after) {
    params.push(after.ts, after.id);
    // Row comparison: strictly "after" the last row of the previous page
    where = "WHERE (created_at, id) < ($2::timestamptz, $3::uuid)";
  }

  const { rows } = await pool.query<Row>(
    `SELECT id, customer_id, total_cents, created_at,
            created_at::text AS cursor_ts -- full microsecond precision
       FROM orders
       ${where}
      ORDER BY created_at DESC, id DESC
      LIMIT $1`,
    params,
  );

  const hasMore = rows.length > pageSize;
  const page = rows.slice(0, pageSize);
  const last = page.at(-1);

  return {
    items: page.map(({ cursor_ts, ...order }) => order),
    nextCursor: hasMore && last ? encodeCursor({ ts: last.cursor_ts, id: last.id }) : null,
  };
}

Usage

TypeScript

// app/api/orders/route.ts
import { listOrders } from "@/lib/list-orders";

export async function GET(req: Request) {
  const { searchParams } = new URL(req.url);
  const result = await listOrders({
    limit: Number(searchParams.get("limit") ?? 50),
    cursor: searchParams.get("cursor") ?? undefined,
  });
  return Response.json(result); // { items, nextCursor }
}

How it works & gotchas

01OFFSET 100000 still reads and discards 100,000 rows. A keyset query jumps straight to the right place in the index, so every page costs the same.
02The tiebreaker column (id) is required. Without it, rows sharing a created_at value can be skipped or repeated across pages.
03The cursor stores created_at::text, not a JS Date. JavaScript dates have millisecond precision and Postgres has microseconds, so a Date round trip can skip rows.
04The sort order, the row comparison and the index must all match. Changing the sort means a new cursor format and a new index.
05Keyset pagination can't jump to page 37. If users need numbered pages, combine it with an approximate count or use search instead.

Need this wired into a real system?

We build and harden production software end to end.

Talk to an engineer

More code helpers

SQL

Bulk upsert in Postgres with ON CONFLICT

Insert or update many rows in one round trip, skip writes when nothing changed, and find out which rows were new. Useful for syncing data from CRMs, APIs or CSV imports.

TypeScript

Stream LLM responses from a Next.js route handler

Send tokens to the browser as the model produces them instead of making users wait for the full completion. Uses a plain ReadableStream, so it works with any client that can read a fetch body.

TypeScript

Retry LLM calls with exponential backoff and jitter

LLM APIs return 429s and 5xx errors under load. This wrapper retries only the failures worth retrying, honours Retry-After and spreads retries out so many workers don't hit the API in lockstep.