Offset vs Cursor Pagination: Why Your API Slows Down on Page 500 (With PostgreSQL and Node.js Code)
DEV Community

Offset vs Cursor Pagination: Why Your API Slows Down on Page 500 (With PostgreSQL and Node.js Code)

Most list endpoints start with LIMIT and OFFSET. It's simple, it works on day one, and it gets quietly slower as the table grows.

By the time someone (or a crawler) asks for page 500, the database is reading and throwing away thousands of rows just to return 20. Cursor pagination (also called keyset pagination) fixes this. Here's how both approaches work, where offset breaks down, and how to build a cursor-based endpoint with PostgreSQL and Node.js.

How offset pagination works

SELECT id, title, created_at FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 9980;

PostgreSQL can't jump straight to row 9,981. It has to walk the first 10,000 rows in order and discard 9,980 of them. Even with an index on created_at, the work grows with the offset: page 1 is fast, page 500 reads roughly 500 times as many rows.

The two problems with OFFSET

  • It gets slower the deeper you go. The cost is proportional to offset + limit, not to the page size.
  • Results shift when data changes. If a new post is inserted while a user is on page 1, every row moves down by one, and page 2 repeats the last item they already saw. A delete does the opposite and silently skips an item. In infinite-scroll feeds this shows up as duplicates and missing items.

How cursor pagination works

Instead of saying "skip 9,980 rows", the client says "give me 20 rows after the last one I saw":

SELECT id, title, created_at FROM posts WHERE created_at < $1 ORDER BY created_at DESC LIMIT 20;

With an index on created_at, PostgreSQL seeks directly to that position and reads 20 rows. Page 500 costs the same as page 1, and inserts at the top of the list no longer push items onto the next page.

Make the sort key unique

created_at on its own isn't safe. Two rows can share a timestamp, and a plain < comparison would skip one of them. Add a unique tie-breaker (usually the primary key) and compare both columns as a row value:

SELECT id, title, created_at FROM posts WHERE (created_at, id) < ($1, $2) ORDER BY created_at DESC, id DESC LIMIT 20;

Back it with a composite index whose column order matches the ORDER BY:

CREATE INDEX posts_created_at_id_idx ON posts (created_at DESC, id DESC);

The row comparison (a, b) < (x, y) means "a < x, or a = x and b < y", which matches the sort order exactly, as long as both columns are sorted in the same direction.

The timestamp precision gotcha

PostgreSQL stores timestamptz with microsecond precision. JavaScript's Date only keeps milliseconds. If you read created_at into a Date and build the cursor from it, 12:00:00.123456 becomes 12:00:00.123, and rows inside that missing fraction of a millisecond get repeated or skipped.

The easy fix is to have PostgreSQL hand you the cursor value as text, so it round-trips without losing precision:

SELECT id, title, created_at, created_at::text AS cursor_ts FROM posts ...

Encode the cursor

Don't make clients build cursors themselves. Pack the last row's sort values into an opaque string:

function encodeCursor(row) {
  return Buffer.from(JSON.stringify([row.cursor_ts, row.id])).toString('base64url');
}

function decodeCursor(cursor) {
  const [createdAt, id] = JSON.parse(Buffer.from(cursor, 'base64url').toString('utf8'));
  if (typeof createdAt !== 'string' || id == null) throw new Error('Bad cursor');
  return { createdAt, id };
}

An opaque cursor lets you change which fields it contains later without breaking clients. It isn't a security boundary, though: anyone can decode it, so validate what comes back.

A complete endpoint (Express + pg)

import express from 'express';
import pg from 'pg';

const pool = new pg.Pool();
const app = express();

app.get('/posts', async (req, res) => {
  const limit = Math.min(parseInt(req.query.limit, 10) || 20, 100);
  const params = [limit + 1]; // fetch one extra row to know if there's a next page
  let where = '';
  if (req.query.cursor) {
    let cursor;
    try {
      cursor = decodeCursor(req.query.cursor);
    } catch {
      return res.status(400).json({ error: 'Invalid cursor' });
    }
    params.push(cursor.createdAt, cursor.id);
    where = 'WHERE (created_at, id) < ($2::timestamptz, $3::bigint)';
  }
  const { rows } = await pool.query(
    `SELECT id, title, created_at, created_at::text AS cursor_ts FROM posts ${where} ORDER BY created_at DESC, id DESC LIMIT $1`,
    params
  );
  const hasMore = rows.length > limit;
  const items = hasMore ? rows.slice(0, limit) : rows;
  res.json({
    items: items.map(({ cursor_ts, ...item }) => item),
    nextCursor: hasMore ? encodeCursor(items[items.length - 1]) : null,
  });
});

Two details worth copying:

  • Fetch limit + 1 rows. If the extra row comes back, there's another page. You never need a separate COUNT(*) query, which is itself slow on large tables.
  • Cap the page size. Without the Math.min, a single request for limit=1000000 undoes all the work.

The client just passes nextCursor back until it's null:

GET /posts?limit=20
GET /posts?limit=20&cursor=WyIyMDI2LTExLTE4IDA5OjEyOjMzLjEyMzQ1NiswMCIsIjQyIl0

Going backwards

For a "previous page" button, flip the comparison and the sort, then reverse the result in code:

SELECT id, title, created_at, created_at::text AS cursor_ts FROM posts WHERE (created_at, id) > ($1, $2) ORDER BY created_at ASC, id ASC LIMIT 21;

The same index works, because PostgreSQL can scan a B-tree index in either direction.

When offset is still the right choice

Cursor pagination isn't free. You can't jump to "page 37", and every sort order you offer needs its own index and its own cursor fields. Offset is still fine when:

  • the table is small (a few thousand rows) and won't grow much
  • users genuinely need numbered pages, as in an admin table
  • the data rarely changes while someone is paging through it

For feeds, public APIs, activity logs, sync endpoints and anything a crawler might walk all the way through, use cursors.

Quick checklist

  • [ ] The sort key is unique (timestamp plus primary key)
  • [ ] A composite index matches the ORDER BY exactly
  • [ ] The cursor keeps full timestamp precision
  • [ ] Cursors are opaque to clients and validated on the server
  • [ ] The endpoint fetches limit + 1 rows instead of running COUNT(*)
  • [ ] Page size has a hard maximum
  • [ ] Filters are applied the same way on every page

Prism Infoways is a web and software development company that offers API and backend development and related IT services. This post was drafted with AI assistance.

Read on DEV Community ↗ ← Back to News

Comments

No comments yet. Start the discussion.