{"schemaVersion":"1.0","type":"Article","types":["Article"],"slug":"cursor-based-pagination-in-node-js-why-offset-pagination-breaks-at-scale-4d6o7","url":"https://zyvop.com/cursor-based-pagination-in-node-js-why-offset-pagination-breaks-at-scale-4d6o7","title":"Cursor-Based Pagination in Node.js: Why Offset Pagination Breaks at Scale","subtitle":"Why offset pagination breaks at scale — and a drop-in cursor implementation that stays fast at 10 million rows","tldr":"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 developmen...","keywords":["offset vs cursor pagination","cursor-based pagination PostgreSQL","cursor pagination Node.js","pagination API 2026","keyset pagination Node.js","infinite scroll API Node.js","compound cursor pagination"],"entities":["ZyVOP","offset vs cursor pagination","cursor-based pagination PostgreSQL","cursor pagination Node.js","pagination API 2026","keyset pagination Node.js","infinite scroll API Node.js","compound cursor pagination"],"keyTakeaways":["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."],"headings":["Why Offset Pagination Breaks","How Cursor Pagination Works","Full Implementation","The Cursor","Generic Cursor Pagination Helper","Route Implementation","How the Client Uses It","When You Still Need Total Count","The Index That Makes This Fast","Offset vs Cursor: When to Use Each"],"outboundLinks":[],"contentText":"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 growsThe 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 ScanOffset 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 requiredIf 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.","contentHash":"sha256:bc9b67454583496e3cb878a65679ecb423cf7c7319aa8c2ca6aa63121664aebe","authorName":"ZyVOP","authorUrl":"https://zyvop.com/author/zyvop","authorSameAs":["https://zyvop.com","https://github.com/zyvop","https://x.com/zyvop1"],"category":null,"tags":["offset vs cursor pagination","cursor-based pagination PostgreSQL","cursor pagination Node.js","pagination API 2026","keyset pagination Node.js","infinite scroll API Node.js","compound cursor pagination"],"audience":"Software engineers and developers building applications with offset vs cursor pagination","tone":"Practical and evidence-based engineering guidance","readingTimeMinutes":6,"wordCount":1345,"faqs":null,"primaryTopic":"offset vs cursor pagination","publishedAt":"2026-05-26T10:59:28.753Z","updatedAt":"2026-08-18T03:55:00.180Z","canonicalUrl":"https://zyvop.com/cursor-based-pagination-in-node-js-why-offset-pagination-breaks-at-scale-4d6o7"}