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¹, node-postgres 8.21.0², and Postgres 18.4³, 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.⁴ 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.⁵

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:

-- 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.⁴ 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 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³. 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

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:

{
  "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"
  }
}

{
  "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:

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:⁷

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:

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.⁸ It's the concise, index-friendly equivalent of the clumsy expanded form:

-- 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:

-- 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.⁸

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:

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 = <R>(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 <R>(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.² And bigint (id) and numeric (price) also arrive as strings, because a JS number can't safely hold every value of either type;² 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:

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<string, unknown>).createdAt !== 'string' ||
    typeof (parsed as Record<string, unknown>).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.⁹ 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:

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<T> {
  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<Page<Product>> {
  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<Product>(
      `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<Product>(
      `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:

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:

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,⁸ 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.⁸ 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:

# First page
curl "http://localhost:3000/products?limit=2"

{
  "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:

curl "http://localhost:3000/products?limit=2&cursor=eyJjcmVhdGVkQXQiOiIyMDI2LTAyLTA0IDE3OjE5OjAwKzAwIiwiaWQiOiI0OTk5OSJ9"

A tampered or truncated cursor returns a clean 400 instead of a stack trace:

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.⁷

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.²

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.⁴ 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.⁵

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, and for evolving the response shape over time, API versioning strategies and trade-offs. For the broader picture of where pagination fits, the API development guide 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.

Footnotes

1. Fastify releases, npm fastify (5.8.5, dist-tag latest, verified 2026-06-10). https://www.npmjs.com/package/fastify ↩

2. 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." https://node-postgres.com/features/types ↩ ↩² ↩³ ↩⁴

3. PostgreSQL 18.4 release announcement (current minor as of 2026-06-10). https://www.postgresql.org/about/news/postgresql-184-1710-1614-1518-and-1423-released-3297/ ↩ ↩²

4. Citus Data, "Five ways to paginate in Postgres, from the basic to the exotic." https://www.citusdata.com/blog/2016/03/30/five-ways-to-paginate/ ↩ ↩² ↩³

5. 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. https://dev.to/scion01/optimizing-pagination-in-postgresql-offsetlimit-vs-keyset-21dp ↩ ↩²

6. Node.js Releases — Node 24 is Active LTS, maintenance through April 2028. https://nodejs.org/en/about/previous-releases ↩

7. PostgreSQL Documentation, "Multicolumn Indexes." https://www.postgresql.org/docs/current/indexes-multicolumn.html ↩ ↩²

8. PostgreSQL Documentation, §9.25.5 "Row Constructor Comparison" — operators applied per field, left to right; NULL pair yields NULL. https://www.postgresql.org/docs/current/functions-comparisons.html ↩ ↩² ↩³ ↩⁴

9. Node.js Buffer documentation — base64url encoding (URL- and filename-safe alphabet). https://nodejs.org/api/buffer.html#buffers-and-character-encodings ↩