Cursor-Based vs. Offset Pagination in Node.js: Why Your API Needs Cursors at Scale

Pagination is a fundamental requirement for almost every modern web API. When returning lists of resources—whether they are users, e-commerce products, or high-frequency log entries—you cannot dump millions of records into a single HTTP response.

For most developers starting out with Node.js and PostgreSQL, the go-to solution is offset-based pagination using LIMIT and OFFSET. It is simple, intuitive, and works like a charm in development. However, as your database grows and your API faces heavier traffic, offset pagination becomes a silent performance killer.

In this guide, we will examine why traditional offset pagination fails at scale, explore how cursor-based (keyset) pagination solves these problems, and walk through a production-ready implementation using Node.js, Express, and PostgreSQL.

The Problem with Offset Pagination

Offset-based pagination relies on skipping a specific number of rows before beginning to return results. A typical SQL query looks like this:

SELECT id, name, created_at 
FROM users 
ORDER BY created_at DESC 
LIMIT 20 OFFSET 10000;

At first glance, this looks harmless. But under the hood, PostgreSQL still has to read, sort, and evaluate all 10,020 rows to discard the first 10,000 before returning the 20 you actually asked for.

Why Offset Fails at Scale:

1. $O(N)$ Complexity: As your OFFSET grows, the database engine does more work. A query with an offset of 100,000 takes significantly longer than an offset of 0.
2. Data Drift (Missed or Duplicate Records): If records are inserted or deleted while a user is paging through results, offset queries suffer from sliding windows. A new row inserted at the top shifts all subsequent rows down, causing the user to see duplicate items or skip items entirely.
3. High CPU and I/O Overhead: Sequential scans or massive index-skips strain memory and CPU, degrading performance for all concurrent database queries.

Enter Cursor-Based (Keyset) Pagination

Cursor-based pagination (often called keyset pagination) avoids the concept of “skipping” entirely. Instead, it uses the value of the last seen record from the previous page as a reference point—the cursor—to fetch the next set of records.

By leveraging indexed columns (like a unique ID or timestamp), PostgreSQL can jump directly to the target location in the B-tree index, achieving true $O(1)$ lookup performance regardless of how deep you are in the pagination set.

The Anatomy of a Cursor

A cursor is typically an opaque string (often base64-encoded) containing:

• The value of the sort column (e.g., created_at timestamp).
• A tie-breaker unique identifier (e.g., id) to handle rows with identical timestamps.

For example, decoding a cursor might yield: timestamp: 2026-03-30T10:00:00.000Z, id: 4589. Our SQL query then becomes:

SELECT id, name, created_at 
FROM users 
WHERE (created_at, id) < ('2026-03-30T10:00:00.000Z', 4589)
ORDER BY created_at DESC, id DESC 
LIMIT 20;

This query utilizes a composite index on (created_at DESC, id DESC), allowing PostgreSQL to seek directly to the row matching the cursor criteria without scanning preceding records.

Setting Up the Database Schema and Index

To make cursor pagination blazing fast, your database indexes must align precisely with your sorting strategy. Let’s create a sample transactions table in PostgreSQL.

CREATE TABLE transactions (
    id BIGSERIAL PRIMARY KEY,
    user_id UUID NOT NULL,
    amount NUMERIC(12, 2) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Crucial: Create a composite index matching our sort order
CREATE INDEX idx_transactions_cursor 
ON transactions (created_at DESC, id DESC);

Implementing Cursor Pagination in Node.js

Let’s build a robust Node.js implementation using Express and the pg client. We will create a service layer that handles encoding/decoding cursors and executing the optimized PostgreSQL query.

1. Utility Functions for Cursor Encoding/Decoding

We will use Node’s built-in Buffer class to safely encode and decode our cursor payload into a URL-safe string.

// utils/cursor.js

function encodeCursor(createdAt, id) {
  const rawData = `${createdAt.toISOString()}_${id}`;
  return Buffer.from(rawData).toString('base64url');
}

function decodeCursor(cursor) {
  try {
    const rawData = Buffer.from(cursor, 'base64url').toString('utf8');
    const [createdAtStr, idStr] = rawData.split('_');
    
    if (!createdAtStr || !idStr) {
      throw new Error('Invalid cursor format');
    }

    return {
      createdAt: new Date(createdAtStr),
      id: parseInt(idStr, 10),
    };
  } catch (err) {
    throw new Error('Malformed pagination cursor');
  }
}

module.exports = { encodeCursor, decodeCursor };

2. The Pagination Query Service

Next, we write the repository function that interacts with PostgreSQL. We’ll support forward pagination (limit and cursor).

// services/transactionService.js
const pool = require('../config/db');
const { encodeCursor, decodeCursor } = require('../utils/cursor');

async function getTransactions({ limit = 20, cursor }) {
  // Ensure limit is within safe bounds
  const parsedLimit = Math.max(1, Math.min(parseInt(limit, 10), 100));
  // Fetch one extra row to determine if there is a next page
  const fetchLimit = parsedLimit + 1;

  let queryParams = [];
  let whereClause = '';

  if (cursor) {
    const { createdAt, id } = decodeCursor(cursor);
    whereClause = `WHERE (created_at, id) < ($1, $2)`;
    queryParams.push(createdAt, id);
  }

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

  const query = `
    SELECT id, user_id, amount, created_at
    FROM transactions
    ${whereClause}
    ORDER BY created_at DESC, id DESC
    LIMIT $${limitParamIndex};
  `;

  const result = await pool.query(query, queryParams);
  const rows = result.rows;

  let hasNextPage = false;
  if (rows.length > parsedLimit) {
    hasNextPage = true;
    rows.pop(); // Remove the extra row used for checking
  }

  let nextCursor = null;
  if (rows.length > 0 && hasNextPage) {
    const lastRow = rows[rows.length - 1];
    nextCursor = encodeCursor(lastRow.created_at, lastRow.id);
  }

  return {
    data: rows,
    pagination: {
      limit: parsedLimit,
      nextCursor,
      hasNextPage,
    },
  };
}

module.exports = { getTransactions };

3. Creating the Express API Endpoint

Now, wire up the service into an Express controller.

// controllers/transactionController.js
const { getTransactions } = require('../services/transactionService');

async function listTransactions(req, res) {
  try {
    const { limit, cursor } = req.query;
    const result = await getTransactions({ limit, cursor });
    
    return res.status(200).json({
      success: true,
      ...result,
    });
  } catch (error) {
    console.error('Pagination error:', error.message);
    
    if (error.message.includes('cursor')) {
      return res.status(400).json({ success: false, error: error.message });
    }
    
    return res.status(500).json({ success: false, error: 'Internal server error' });
  }
}

module.exports = { listTransactions };

Handling Bidirectional Pagination (Optional Advanced Pattern)

Sometimes clients need to page both forward and backward (e.g., infinite scroll up and down). You can easily adapt keyset pagination for backward navigation by flipping your comparison operators and sorting order.

-- For 'previous page' (moving backward from a cursor)
SELECT id, user_id, amount, created_at
FROM transactions
WHERE (created_at, id) > ($1, $2)
ORDER BY created_at ASC, id ASC
LIMIT $3;

Note: When returning results for a backward query, remember to reverse the array in your Node.js application before sending it to the client so the chronological order remains consistent.

Offset vs. Cursor: Quick Comparison Matrix

Conclusion

While offset-based pagination is fine for prototyping or admin dashboards with tiny datasets, it becomes a major bottleneck as your application scales. By switching to cursor-based (keyset) pagination in your Node.js and PostgreSQL stack, you guarantee predictable API response times, reduce database CPU load, and eliminate frustrating data duplication bugs for your users.

Implement composite indexes matching your sort keys, encode your cursors cleanly using base64url, and watch your API performance soar even under heavy multi-million row workloads.