# Cursor Pagination in Postgres with Node.js (2026) Cursor (keyset) pagination fetches the next page by seeking past the last row you returned — `WHERE (created_at, id) < ($1, $2)` — instead of counting and discarding rows with `OFFSET`. On Postgres it stays fast at any depth because the database jumps straight into a matching B-tree index and reads only the rows it returns. ## TL;DR This hands-on tutorial builds cursor (keyset) pagination for a `/products` endpoint on **Fastify 5.8.5**[^fastify], **node-postgres 8.21.0**[^nodepg], and **Postgres 18.4**[^pg18], with Node 24 LTS and TypeScript 6 in strict mode. You'll replace slow `OFFSET` paging with a compound `(created_at, id)` keyset query, wrap the position in an opaque base64url cursor, and return a `nextCursor` clients can page forward with. Every query was run against Postgres and every file type-checked on 10 June 2026. Budget about 30 minutes. ## What you'll learn - Why `OFFSET` pagination slows down on large tables, shown with a real `EXPLAIN` - How keyset pagination seeks with a Postgres row-value comparison instead of skipping rows - How a compound `(created_at, id)` cursor keeps paging stable when the sort column has ties - How to encode an opaque, tamper-checked cursor with `base64url` - How to return `nextCursor` and `hasNextPage` from a Fastify route using the fetch-one-extra trick - How descending order and NULL sort columns change the comparison - When `OFFSET` is still the right call ## Why OFFSET pagination gets slow `LIMIT 20 OFFSET 0` is fine. `LIMIT 20 OFFSET 49980` is not. To honor an offset, Postgres still has to walk and discard every row before it, so the cost grows with how deep you page.[^citus] In one published `EXPLAIN ANALYZE` comparison, an offset query's execution time rises from about 0.13 ms on page one to roughly 5.8 ms at offset 20,000 — its plan reads 20,100 rows to return 100 — while the equivalent keyset query stays near 0.1 ms and reads only the 100 rows it returns.[^bench] You don't have to take that on faith — `EXPLAIN` shows it. With 50,000 rows and an index on `(created_at DESC, id DESC)`, the deep-offset plan looks like this: ```text -- EXPLAIN SELECT ... ORDER BY created_at DESC, id DESC LIMIT 20 OFFSET 49980; Limit (cost=3095.05..3096.29 rows=20 width=35) -> Index Scan using products_created_at_id_idx on products (cost=0.29..3096.29 rows=50000 width=35) ``` The `Limit` node has a **startup cost of 3095.05**: Postgres traverses ~49,980 index entries before it can return the first row. The keyset query you'll build next has a startup cost near zero. (Exact costs depend on your statistics and Postgres version; the plan *shape* is the point.) Offset paging has a second, quieter problem: if rows are inserted or deleted while a user pages, the offsets shift, so they can see a row twice or skip one entirely.[^citus] Keyset cursors are immune to that because they anchor on a value, not a position. ## Prerequisites - **Node.js 24** (Active LTS, supported through April 2028)[^node]. Node 24 runs `.ts` files through `tsx`, so there's no build step in dev. - Any currently supported **Postgres (14–18)**; this demo uses the official **`postgres:18.4`** Docker image[^pg18]. Row-value comparison is standard SQL and works on all of them. - Basic SQL and `async`/`await`. No prior Fastify experience needed. Versions are pinned throughout. Pasting `latest` into a tutorial is how a working example rots three weeks later. ## Step 1: Project setup ```bash mkdir cursor-pagination-demo && cd cursor-pagination-demo npm init -y npm pkg set type=module npm install --save-exact fastify@5.8.5 pg@8.21.0 npm install --save-exact -D typescript@6.0.3 tsx@4.22.4 @types/node@24.13.1 @types/pg@8.20.0 ``` `--save-exact` matters: `npm install pg@8.21.0` without it writes `^8.21.0`, so the next `npm install` can resolve a higher patch. Pin the exact version when a tutorial promises reproducibility. `package.json` and a strict `tsconfig.json`: ```json { "name": "cursor-pagination-demo", "private": true, "type": "module", "scripts": { "dev": "node --import tsx --watch src/index.ts", "start": "node --import tsx src/index.ts", "typecheck": "tsc --noEmit" }, "dependencies": { "fastify": "5.8.5", "pg": "8.21.0" }, "devDependencies": { "@types/node": "24.13.1", "@types/pg": "8.20.0", "tsx": "4.22.4", "typescript": "6.0.3" } } ``` ```json { "compilerOptions": { "target": "es2023", "module": "nodenext", "moduleResolution": "nodenext", "lib": ["es2023"], "strict": true, "noUncheckedIndexedAccess": true, "verbatimModuleSyntax": true, "allowImportingTsExtensions": true, "noEmit": true, "types": ["node"], "skipLibCheck": true }, "include": ["src"] } ``` Start Postgres in Docker: ```bash docker run --name cursor-pg -e POSTGRES_PASSWORD=postgres \ -p 5432:5432 -d postgres:18.4 export DATABASE_URL="postgres://postgres:postgres@localhost:5432/postgres" ``` ## Step 2: Schema, index, and seed data The two columns that matter are the sort key (`created_at`) and a unique tiebreaker (`id`). Create a multicolumn index whose direction matches the query's `ORDER BY` so the seek is an index range scan, not a sort:[^pg-multicol] ```sql CREATE TABLE products ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name text NOT NULL, price numeric(10,2) NOT NULL, created_at timestamptz NOT NULL DEFAULT now() ); -- Direction matches the ORDER BY in every keyset query below. CREATE INDEX products_created_at_id_idx ON products (created_at DESC, id DESC); -- 50,000 rows so the OFFSET problem is measurable. Deterministic timestamps -- (one minute apart, newest row = highest id) keep the examples below reproducible. INSERT INTO products (name, price, created_at) SELECT 'Product ' || g, ((g % 100) + 0.99)::numeric(10, 2), timestamptz '2026-01-01 00:00:00+00' + (g * interval '1 minute') FROM generate_series(1, 50000) AS g; ``` Save that as `schema.sql` and load it with `psql "$DATABASE_URL" -f schema.sql`. ## Step 3: The keyset query Here's the whole idea in one statement. To get the page *after* a known row, ask for rows whose `(created_at, id)` is past that row's, in the same order: ```sql SELECT id, name, price, created_at FROM products WHERE (created_at, id) < ($1::timestamptz, $2::bigint) ORDER BY created_at DESC, id DESC LIMIT $3; ``` That `(created_at, id) < ($1, $2)` is a **row-value comparison**. Postgres compares the fields left to right: a row qualifies if its `created_at` is earlier, or if `created_at` ties and its `id` is smaller.[^pg-rowcompare] It's the concise, index-friendly equivalent of the clumsy expanded form: ```sql -- Equivalent, but harder to read and easier to get wrong: WHERE created_at < $1 OR (created_at = $1 AND id < $2) ``` The compound key earns its keep on ties. If you paginate on `created_at` alone and a hundred rows share a timestamp, a single-column cursor can land mid-tie and silently skip or repeat rows. Adding the unique `id` gives every row a **total order**, so the boundary between pages is always unambiguous. `EXPLAIN` on the same 50,000-row table shows why this is fast: ```text -- EXPLAIN SELECT ... WHERE (created_at, id) < ($1, $2) ... LIMIT 20; Limit (cost=0.29..1.58 rows=20 width=35) -> Index Scan using products_created_at_id_idx on products (cost=0.29..3221.26 rows=49998 width=35) Index Cond: (ROW(created_at, id) < ROW('2026-02-04 17:19:00+00', '49999')) ``` Two things changed versus the offset plan. The `Limit` startup cost dropped from 3095.05 to **0.29**, and the comparison became an `Index Cond` — Postgres pushes the bound into the index and starts reading at the right spot instead of counting from the top.[^pg-rowcompare] ## Step 4: A lossless database seam node-postgres returns a few types as strings on purpose, and one default will bite a cursor if you ignore it. Create `src/db.ts`: ```ts import pg from 'pg'; // node-postgres parses timestamptz (OID 1184) into a millisecond-precision JS // Date by default. Postgres stores microseconds, so a Date round-trip can drop // precision and skip/duplicate a row at a page boundary. Returning the raw text // keeps the cursor lossless. pg.types.setTypeParser(1184, (value) => value); export type QueryFn = (text: string, params?: unknown[]) => Promise<{ rows: R[] }>; export function createPool(connectionString: string): pg.Pool { return new pg.Pool({ connectionString }); } // Adapt the generically-typed driver method to a narrow app-level seam. Writing // the wrapper as a generic function (not an alias) lets the row type flow through. export function makeQuery(pool: pg.Pool): QueryFn { return async (text: string, params: unknown[] = []) => { const result = await pool.query(text, params); return { rows: result.rows as R[] }; }; } ``` Two deliberate choices here. The `setTypeParser(1184, …)` call hands back `created_at` as the exact Postgres text, so the value you put in a cursor and the value you compare against are byte-for-byte identical — no microsecond truncation to misplace a boundary row.[^nodepg] And `bigint` (`id`) and `numeric` (`price`) also arrive as strings, because a JS number can't safely hold every value of either type;[^nodepg] keep them as strings and cast back with `$2::bigint` in SQL. The `QueryFn` seam keeps the query function injectable, which makes the pagination logic testable without a live database. ## Step 5: Opaque cursors with base64url Clients should never see your raw column values — that couples your API to your schema. Encode the position into one opaque token. Create `src/cursor.ts`: ```ts export interface Cursor { createdAt: string; id: string; } export class InvalidCursorError extends Error { constructor() { super('invalid cursor'); this.name = 'InvalidCursorError'; } } // base64url is URL-safe: no +, /, or = to percent-encode in a query string. export function encodeCursor(cursor: Cursor): string { return Buffer.from(JSON.stringify(cursor)).toString('base64url'); } // Encoding is not encryption: never put secrets in a cursor. Always validate the // decoded shape so a tampered or truncated value becomes a 400, not a 500. export function decodeCursor(raw: string): Cursor { let parsed: unknown; try { parsed = JSON.parse(Buffer.from(raw, 'base64url').toString('utf8')); } catch { throw new InvalidCursorError(); } if ( typeof parsed !== 'object' || parsed === null || typeof (parsed as Record).createdAt !== 'string' || typeof (parsed as Record).id !== 'string' ) { throw new InvalidCursorError(); } const { createdAt, id } = parsed as Cursor; return { createdAt, id }; } ``` `base64url` is the right encoding for a query-string value: it uses `-` and `_` instead of `+` and `/` and drops padding, so it survives in a URL untouched.[^base64url] The validation matters as much as the encoding — a cursor arrives from the open internet, so a garbage value has to fail closed into a `400`, never an unhandled exception. ## Step 6: The pagination logic Now assemble the query and the cursor. Create `src/pagination.ts`: ```ts import type { QueryFn } from './db.ts'; import { decodeCursor, encodeCursor } from './cursor.ts'; // bigint (int8) and numeric come back from node-postgres as strings, because a // JS number cannot safely hold every value of either type. Keep them as strings. export interface Product { id: string; name: string; price: string; created_at: string; } export interface Page { data: T[]; pageInfo: { nextCursor: string | null; hasNextPage: boolean }; } const DEFAULT_LIMIT = 20; const MAX_LIMIT = 100; export function clampLimit(raw: unknown): number { const n = typeof raw === 'string' ? Number.parseInt(raw, 10) : typeof raw === 'number' ? raw : Number.NaN; if (!Number.isFinite(n) || n < 1) return DEFAULT_LIMIT; return Math.min(Math.trunc(n), MAX_LIMIT); } export async function listProducts( query: QueryFn, opts: { limit: number; cursor?: string | undefined }, ): Promise> { const { limit } = opts; const fetchCount = limit + 1; // one extra row tells us whether a next page exists let rows: Product[]; if (opts.cursor) { const { createdAt, id } = decodeCursor(opts.cursor); const result = await query( `SELECT id, name, price, created_at FROM products WHERE (created_at, id) < ($1::timestamptz, $2::bigint) ORDER BY created_at DESC, id DESC LIMIT $3`, [createdAt, id, fetchCount], ); rows = result.rows; } else { const result = await query( `SELECT id, name, price, created_at FROM products ORDER BY created_at DESC, id DESC LIMIT $1`, [fetchCount], ); rows = result.rows; } const hasNextPage = rows.length > limit; const data = hasNextPage ? rows.slice(0, limit) : rows; const last = data[data.length - 1]; const nextCursor = hasNextPage && last ? encodeCursor({ createdAt: last.created_at, id: last.id }) : null; return { data, pageInfo: { nextCursor, hasNextPage } }; } ``` The trick worth naming is `fetchCount = limit + 1`. Asking for one extra row tells you whether another page exists without a separate `COUNT(*)` query. If you get `limit + 1` rows back, there's a next page — drop the extra row and build `nextCursor` from the last row you actually keep; otherwise `nextCursor` is `null`. `clampLimit` caps page size so a client can't ask for a million rows. ## Step 7: The Fastify endpoint Wire it into a route. Create `src/server.ts`: ```ts import Fastify, { type FastifyInstance } from 'fastify'; import type { QueryFn } from './db.ts'; import { InvalidCursorError } from './cursor.ts'; import { clampLimit, listProducts } from './pagination.ts'; interface ListQuery { limit?: string; cursor?: string; } export function buildServer(query: QueryFn): FastifyInstance { const app = Fastify({ logger: false }); app.get<{ Querystring: ListQuery }>('/products', async (request, reply) => { const limit = clampLimit(request.query.limit); try { return await listProducts(query, { limit, cursor: request.query.cursor }); } catch (err) { if (err instanceof InvalidCursorError) { return reply.code(400).send({ error: 'invalid cursor' }); } throw err; } }); return app; } ``` And the entry point, `src/index.ts`: ```ts import { createPool, makeQuery } from './db.ts'; import { buildServer } from './server.ts'; const pool = createPool(process.env.DATABASE_URL ?? 'postgres://localhost:5432/postgres'); const app = buildServer(makeQuery(pool)); const port = Number(process.env.PORT ?? 3000); const address = await app.listen({ port, host: '0.0.0.0' }); console.log(`listening on ${address}`); ``` Run it with `npm run dev`. Type-check the whole thing any time with `npm run typecheck`. ## Step 8: Ties, descending order, and NULLs Three correctness details separate a keyset query that works in a demo from one that works in production. **Direction has to be uniform.** A row-value comparison applies the *same* operator to every column,[^pg-rowcompare] so `(created_at, id) < ($1, $2)` only expresses "both columns descending." It pairs with `ORDER BY created_at DESC, id DESC` and nothing else. If you need mixed directions — say `created_at DESC, name ASC` — the single tuple comparison can't represent it, and you must fall back to the expanded `OR`/`AND` form from Step 3. **The sort columns must be NOT NULL.** If a compared field is NULL, the row comparison evaluates to NULL rather than true, so `WHERE` drops the row and it never appears on any page.[^pg-rowcompare] That's why `created_at` is declared `NOT NULL` and the tiebreaker is the primary key. If you must paginate on a nullable column, sort on `COALESCE(col, sentinel)` or add a guaranteed-present surrogate. **Forward only, by design.** This cursor pages in one direction. "Previous page" means running the mirror query (`> ($1, $2)` with the order flipped) and reversing the result in code — straightforward, but a separate code path worth planning for rather than bolting on. ## Verification Page through the API and watch the cursor carry you forward: ```bash # First page curl "http://localhost:3000/products?limit=2" ``` ```json { "data": [ { "id": "50000", "name": "Product 50000", "price": "0.99", "created_at": "2026-02-04 17:20:00+00" }, { "id": "49999", "name": "Product 49999", "price": "99.99", "created_at": "2026-02-04 17:19:00+00" } ], "pageInfo": { "nextCursor": "eyJjcmVhdGVkQXQiOiIyMDI2LTAyLTA0IDE3OjE5OjAwKzAwIiwiaWQiOiI0OTk5OSJ9", "hasNextPage": true } } ``` Pass `nextCursor` straight back to get the next page — no offset, no row count. This one returns `Product 49998` next: ```bash curl "http://localhost:3000/products?limit=2&cursor=eyJjcmVhdGVkQXQiOiIyMDI2LTAyLTA0IDE3OjE5OjAwKzAwIiwiaWQiOiI0OTk5OSJ9" ``` A tampered or truncated cursor returns a clean `400` instead of a stack trace: ```bash curl -i "http://localhost:3000/products?cursor=not-a-real-cursor" # HTTP/1.1 400 Bad Request # {"error":"invalid cursor"} ``` ## Troubleshooting **Pages skip or repeat rows.** You're almost certainly paginating on a non-unique column without a tiebreaker. Confirm the cursor and the `ORDER BY` both include the unique `id`, and that the `<` comparison covers the full tuple. **A row with a duplicate timestamp shows up twice.** Same root cause: a single-column cursor can't break the tie. The compound `(created_at, id)` comparison fixes it because `id` is unique. **The query adds a `Sort` step instead of seeking the index.** Run `EXPLAIN` — you want an `Index Scan` (or `Index Scan Backward`) with no `Sort` node. The fix is a multicolumn index covering *both* sort columns, `(created_at, id)`. A single-column `(created_at)` index isn't enough: Postgres still has to order rows within each timestamp. Direction need not match exactly — Postgres reads a B-tree in either direction, so a `(created_at DESC, id DESC)` index also serves the ascending mirror query via a backward scan. You only need a direction-specific index when the two sort columns run in *opposite* directions, such as `created_at DESC, name ASC`.[^pg-multicol] **A row at a page boundary occasionally vanishes or repeats.** This is the timestamp-precision trap — when two rows fall in the same millisecond, a JS `Date` (millisecond precision) can't distinguish the microsecond `timestamptz` values Postgres stores, so the cursor value is ambiguous. The `setTypeParser(1184, …)` call in `src/db.ts` keeps `created_at` as exact text so the round-trip is lossless.[^nodepg] **`invalid cursor` on a cursor you just issued.** A base64url value needs no extra URL-encoding — its alphabet is already URL-safe — so the usual culprit is the value being altered in transit: double-encoding it, truncating it, or copying it with surrounding quotes. Decode it by hand with `Buffer.from(value, 'base64url').toString()` to see exactly what your handler received. ## When OFFSET still wins Keyset pagination isn't free of trade-offs, and being honest about them is the difference between a pattern and a cargo cult. Cursors can't jump to "page 47" — there's no arbitrary page access — and they don't give you a cheap total count.[^citus] If your UI shows numbered pages or a "showing 1–20 of 4,312" label over a small, rarely-changing table, plain `OFFSET` is simpler and perfectly fine. Reach for keyset when the dataset is large, the access pattern is "next page" / infinite scroll, or rows change while users browse. For deep, sequential paging at scale, keyset is the one that stays fast.[^bench] This pairs naturally with the rest of a production API. If you're hardening write endpoints too, see [idempotency keys for a Node.js API with Postgres](/idempotency-keys-nodejs-postgres-api-tutorial), and for evolving the response shape over time, [API versioning strategies and trade-offs](/mastering-api-versioning-strategies-tradeoffs-and-best-practices). For the broader picture of where pagination fits, the [API development guide](/learn-about-api-development-types-tools-and-best-practices) covers the surrounding design choices. ## Next steps - Add a "previous page" route with the mirror query (`>` and reversed order). - Return a stable `ORDER BY` for filtered lists by appending the same `(created_at, id)` tail after your filter columns. - Move the cursor into a request schema so Fastify validates `limit` and `cursor` before your handler runs. [^fastify]: Fastify releases, npm `fastify` (5.8.5, dist-tag `latest`, verified 2026-06-10). [^nodepg]: node-postgres — Data Types: types with no registered parser come back as strings (so `bigint` and `numeric` are strings), `timestamptz` parses to a millisecond-precision JS `Date`, and per the docs "your microseconds will be truncated when converting to a JavaScript date object … If you need to preserve them, I recommend using a custom type parser." [^pg18]: PostgreSQL 18.4 release announcement (current minor as of 2026-06-10). [^node]: Node.js Releases — Node 24 is Active LTS, maintenance through April 2028. [^citus]: Citus Data, "Five ways to paginate in Postgres, from the basic to the exotic." [^bench]: Nishant, "Optimizing Pagination in PostgreSQL: OFFSET/LIMIT vs. Keyset," DEV Community (Oct 2024) — `EXPLAIN ANALYZE` shows the offset query at ~0.125 ms on page 1 rising to ~5.81 ms at `OFFSET 20000` (its index scan reads 20,100 rows to return 100), versus ~0.1 ms for the keyset query. [^pg-rowcompare]: PostgreSQL Documentation, §9.25.5 "Row Constructor Comparison" — operators applied per field, left to right; NULL pair yields NULL. [^pg-multicol]: PostgreSQL Documentation, "Multicolumn Indexes." [^base64url]: Node.js Buffer documentation — `base64url` encoding (URL- and filename-safe alphabet).

#keyset-pagination

الترقيم باستخدام Cursor في Postgres مع Node.js (2026)