Cursor-Based Pagination in Node.js: Why Offset Pagination Breaks at Scale

Most developers reach for offset pagination first. It is intuitive — page=3&limit=20 maps directly to OFFSET 60 LIMIT 20 in SQL. It works fine in development. It starts showing problems at 100,000 rows and is genuinely broken at 10 million.

This guide explains exactly why offset pagination fails, how cursor-based pagination fixes it, and gives you a full implementation you can drop into any Node.js API.

Why Offset Pagination Breaks

The SQL for offset pagination looks innocent:

SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 10000;

What Postgres actually does: scans and discards the first 10,000 rows, then returns the next 20. The work grows linearly with the offset value. Page 1 is fast. Page 500 is slow. Page 5,000 times out.

Run this and watch the difference:

-- Page 1: fast regardless of table size
EXPLAIN ANALYZE
SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 0;
-- actual time: ~0.5ms

-- Page 500: painful on large tables
EXPLAIN ANALYZE
SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 10000;
-- actual time: ~180ms on 1M rows, worse as table grows

The second problem is consistency. If a new order is inserted while a user pages through results, every subsequent page is shifted by one row — users see duplicates or skip records entirely. On a busy table, this is not an edge case.

How Cursor Pagination Works

Instead of saying "skip N rows," cursor pagination says "give me rows that come after this specific record." The cursor encodes the position of the last item the client saw — typically its ID and sort field value.

-- Instead of OFFSET, use a WHERE clause with the last seen values
SELECT * FROM orders
WHERE (created_at, id) < ($last_created_at, $last_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;

This query uses an index regardless of how far into the dataset you are. Page 1 and page 5,000 have identical performance. And because you are filtering by a fixed point in the data, new inserts do not shift your position.

The tradeoffs: you cannot jump to an arbitrary page number, and the sort order must be deterministic (which is why we include id as a tiebreaker — created_at alone can have duplicates).

Full Implementation

The Cursor

The cursor is a base64-encoded JSON object containing the values needed to resume from a specific position. Never expose raw database IDs or timestamps directly — encoding lets you change the internal structure without breaking clients.

// src/lib/pagination.ts

interface CursorData {
  id:        string;
  sortValue: string;   // ISO string for dates, string for other sort fields
}

export function encodeCursor(data: CursorData): string {
  return Buffer.from(JSON.stringify(data)).toString('base64url');
}

export function decodeCursor(cursor: string): CursorData | null {
  try {
    const decoded = Buffer.from(cursor, 'base64url').toString('utf-8');
    return JSON.parse(decoded);
  } catch {
    return null;
  }
}

export interface PaginationResult<T> {
  data:       T[];
  pagination: {
    hasNextPage:  boolean;
    hasPrevPage:  boolean;
    nextCursor:   string | null;
    prevCursor:   string | null;
    totalCount?:  number;     // Optional — expensive to compute, include only when needed
  };
}

Generic Cursor Pagination Helper

// src/lib/pagination.ts (continued)
import { Pool } from 'pg';

interface CursorPaginateOptions {
  db:          Pool;
  table:       string;
  sortField:   string;          // e.g. 'created_at'
  sortOrder:   'ASC' | 'DESC';
  limit:       number;
  cursor?:     string;
  where?:      string;          // Additional WHERE conditions
  params?:     any[];           // Params for the where clause
  select?:     string;          // Columns to select (default: *)
}

export async function cursorPaginate<T>({
  db,
  table,
  sortField,
  sortOrder,
  limit,
  cursor,
  where    = '',
  params   = [],
  select   = '*',
}: CursorPaginateOptions): Promise<PaginationResult<T>> {

  // Fetch one extra to know if there is a next page
  const fetchLimit = limit + 1;
  const operator   = sortOrder === 'DESC' ? '<' : '>';
  const conditions: string[] = [];
  const queryParams: any[]   = [...params];

  if (where) conditions.push(where);

  if (cursor) {
    const decoded = decodeCursor(cursor);
    if (!decoded) throw new Error('Invalid cursor');

    // Compound comparison: (sortField, id) < (value, id)
    // This handles ties in sortField correctly
    queryParams.push(decoded.sortValue, decoded.id);
    const p1 = queryParams.length - 1;
    const p2 = queryParams.length;

    conditions.push(
      `(${sortField}, id) ${operator} ($${p1}, $${p2})`
    );
  }

  const whereClause = conditions.length > 0
    ? `WHERE ${conditions.join(' AND ')}`
    : '';

  queryParams.push(fetchLimit);
  const limitParam = queryParams.length;

  const query = `
    SELECT ${select}
    FROM ${table}
    ${whereClause}
    ORDER BY ${sortField} ${sortOrder}, id ${sortOrder}
    LIMIT $${limitParam}
  `;

  const result = await db.query<T>(query, queryParams);
  const rows   = result.rows;

  const hasNextPage = rows.length > limit;
  if (hasNextPage) rows.pop();  // Remove the extra row

  const nextCursor = hasNextPage && rows.length > 0
    ? encodeCursor({
        id:        (rows[rows.length - 1] as any).id,
        sortValue: String((rows[rows.length - 1] as any)[sortField]),
      })
    : null;

  const prevCursor = cursor && rows.length > 0
    ? encodeCursor({
        id:        (rows[0] as any).id,
        sortValue: String((rows[0] as any)[sortField]),
      })
    : null;

  return {
    data: rows,
    pagination: {
      hasNextPage,
      hasPrevPage: !!cursor,
      nextCursor,
      prevCursor,
    },
  };
}

Route Implementation

// src/routes/orders.ts
import { z } from 'zod';
import { cursorPaginate } from '../lib/pagination';

const ListOrdersSchema = z.object({
  limit:  z.coerce.number().int().min(1).max(100).default(20),
  cursor: z.string().optional(),
  status: z.enum(['pending', 'paid', 'shipped', 'cancelled']).optional(),
  sort:   z.enum(['newest', 'oldest']).default('newest'),
});

router.get('/orders', authenticate, async (req, res) => {
  const query = ListOrdersSchema.safeParse(req.query);
  if (!query.success) {
    return res.status(400).json({ error: 'Invalid query parameters' });
  }

  const { limit, cursor, status, sort } = query.data;
  const sortOrder = sort === 'newest' ? 'DESC' : 'ASC';

  // Build tenant-scoped where clause
  const whereConditions = ['tenant_id = $1'];
  const params: any[]   = [req.tenant.id];

  if (status) {
    params.push(status);
    whereConditions.push(`status = $${params.length}`);
  }

  const result = await cursorPaginate({
    db:        pool,
    table:     'orders',
    sortField: 'created_at',
    sortOrder,
    limit,
    cursor,
    where:     whereConditions.join(' AND '),
    params,
    select:    'id, status, total, created_at, user_id',
  });

  res.json(result);
});

Response shape:

{
  "data": [
    { "id": "uuid-1", "status": "paid", "total": "149.99", "created_at": "..." },
    { "id": "uuid-2", "status": "pending", "total": "89.00", "created_at": "..." }
  ],
  "pagination": {
    "hasNextPage": true,
    "hasPrevPage": false,
    "nextCursor": "eyJpZCI6InV1aWQtMiIsInNvcnRWYWx1ZSI6Ii4uLiJ9",
    "prevCursor": null
  }
}

How the Client Uses It

// Client-side — fetch next page
async function fetchNextPage(currentCursor: string | null) {
  const params = new URLSearchParams({ limit: '20' });
  if (currentCursor) params.set('cursor', currentCursor);

  const res  = await fetch(`/api/orders?${params}`);
  const data = await res.json();

  return {
    orders:     data.data,
    nextCursor: data.pagination.nextCursor,
    hasMore:    data.pagination.hasNextPage,
  };
}

// Infinite scroll implementation
let cursor: string | null = null;
let hasMore = true;

async function loadMore() {
  if (!hasMore) return;
  const { orders, nextCursor, hasMore: more } = await fetchNextPage(cursor);
  appendToList(orders);
  cursor  = nextCursor;
  hasMore = more;
}

When You Still Need Total Count

Cursor pagination deliberately avoids COUNT(*) — it is expensive on large tables. But some UIs genuinely need "showing 1–20 of 4,832 results."

If you need it, run the count query separately and cache it:

// Cache the count for 60 seconds — it doesn't need to be exact
async function getOrderCount(tenantId: string, status?: string): Promise<number> {
  const cacheKey = `count:orders:${tenantId}:${status || 'all'}`;
  const cached   = await redis.get(cacheKey);
  if (cached) return parseInt(cached);

  const whereClause = status
    ? 'WHERE tenant_id = $1 AND status = $2'
    : 'WHERE tenant_id = $1';
  const params = status ? [tenantId, status] : [tenantId];

  const result = await db.query(
    `SELECT COUNT(*) FROM orders ${whereClause}`,
    params
  );

  const count = parseInt(result.rows[0].count);
  await redis.setex(cacheKey, 60, count.toString());
  return count;
}

Only fetch the count on the first page request — subsequent page fetches do not need it.

The Index That Makes This Fast

The cursor query only performs well if Postgres can use an index for the compound sort. Create a covering index that matches your sort:

-- For ORDER BY created_at DESC, id DESC
CREATE INDEX CONCURRENTLY idx_orders_cursor_desc
  ON orders(created_at DESC, id DESC)
  WHERE status != 'cancelled';   -- Partial index if you filter by status often

-- For multi-tenant setups, include tenant_id first
CREATE INDEX CONCURRENTLY idx_orders_tenant_cursor
  ON orders(tenant_id, created_at DESC, id DESC);

Verify the index is being used:

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE tenant_id = 'uuid'
  AND (created_at, id) < ('2026-01-01', 'uuid-last')
ORDER BY created_at DESC, id DESC
LIMIT 21;
-- Should show: Index Scan using idx_orders_tenant_cursor
-- Should NOT show: Seq Scan

Offset vs Cursor: When to Use Each

Use offset when:                    Use cursor when:
────────────────────────────────    ─────────────────────────────────
Table has < 50,000 rows             Table has or will have > 100k rows
Users need to jump to page N        Infinite scroll / load more UI
Admin reporting (exact page nav)    Real-time data (new rows inserted often)
Simple implementation needed        Performance at scale is required

If you are building a user-facing feed, activity stream, notification list, or any list that grows continuously — cursor pagination is the right choice from day one. Retrofitting it later means updating both your API and your clients simultaneously.