SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 100000 responds in a few milliseconds on a test table, and in several seconds on the equivalent production table with ten million rows. This is the classic OFFSET pagination trap: it works perfectly in development, where volumes are small, and degrades silently in production, right when deep pages (infinite scroll, paginated exports, crawlers) start hitting the database.

Why OFFSET gets progressively more expensive

OFFSET isn't a direct positioning operation. The database engine first has to sort (or walk an already-sorted index for) every row preceding the requested offset, count through them, and discard them, before returning the rows actually wanted. With OFFSET 100000 LIMIT 20, PostgreSQL reads and throws away 100,000 rows just to return 20. The cost grows linearly with page depth, even when an index perfectly covers the ORDER BY clause.

EXPLAIN ANALYZE
SELECT id, created_at, status
FROM orders
ORDER BY created_at DESC
LIMIT 20 OFFSET 100000;

On a table indexed on created_at, the plan typically shows an Index Scan that looks efficient on paper, but the actual time on the Limit node reveals the real cost: PostgreSQL had to walk 100,020 rows in the index before it could return 20. The deeper the requested page, the higher this cost climbs, with no built-in ceiling.

The second, often overlooked cost: if the UI shows a total page count, it usually runs a separate SELECT COUNT(*), which on a large table with no selective filter requires a full scan whose cost can rival or exceed the rest of the query.

Diagnose before changing anything

Before rewriting pagination, verify that OFFSET is actually the expensive part, and not something else (a missing index on the sort column, for instance). EXPLAIN (ANALYZE, BUFFERS) on a deep-page query, run against production-scale volume, gives the exact measurement:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at, status
FROM orders
WHERE status = 'shipped'
ORDER BY created_at DESC
LIMIT 20 OFFSET 50000;

If Buffers: shared hit or read shows a high block count for a query that only returns 20 rows, that's the telltale signature of OFFSET's cost: the engine had to touch a volume of data wildly disproportionate to the result returned.

Cursor-based (keyset) pagination

The structural fix replaces "give me page number N" with "give me the rows that follow this exact point I've already seen". Instead of a numeric offset, the client sends back the sort column's value (plus a unique tiebreaker) from the last row it saw, and the query resumes from there using a WHERE clause instead of an OFFSET.

-- First page
SELECT id, created_at, status
FROM orders
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- Next page: resume from the last (created_at, id) seen, no numeric offset
SELECT id, created_at, status
FROM orders
WHERE (created_at, id) < ('2025-07-20 14:32:11', 48213)
ORDER BY created_at DESC, id DESC
LIMIT 20;

The tiebreaker (id alongside created_at) is not optional: without it, two rows sharing the exact same created_at would produce an unstable order, risking duplicates or skipped rows across pages. Comparing on the tuple (created_at, id) guarantees a strict total order.

With a composite index matching this exact sort order, the query becomes a plain Index Scan that jumps straight to the right spot in the index, with no need to walk or count the preceding rows:

CREATE INDEX idx_orders_created_at_id ON orders (created_at DESC, id DESC);

This query's cost no longer depends on page depth: fetching page 5 or page 50,000 takes the same time, since the index lets the engine jump directly to the cursor without scanning anything before it.

Implementing the cursor on the application side

The change isn't limited to SQL: the application layer has to encode and decode the cursor opaquely for the client. With Knex, for instance, the next-page query is built directly from the values the client sent back, base64-encoded so the client can't tamper with them or mistake them for a plain page number:

function encodeCursor(row) {
  return Buffer.from(JSON.stringify({ createdAt: row.created_at, id: row.id })).toString('base64');
}

function decodeCursor(cursor) {
  return JSON.parse(Buffer.from(cursor, 'base64').toString('utf8'));
}

async function getOrdersPage(db, { cursor, limit = 20 }) {
  let query = db('orders').select('id', 'created_at', 'status').orderBy([
    { column: 'created_at', order: 'desc' },
    { column: 'id', order: 'desc' },
  ]).limit(limit);

  if (cursor) {
    const { createdAt, id } = decodeCursor(cursor);
    query = query.where((qb) => {
      qb.where('created_at', '<', createdAt)
        .orWhere((qb2) => qb2.where('created_at', '=', createdAt).andWhere('id', '<', id));
    });
  }

  const rows = await query;
  const nextCursor = rows.length === limit ? encodeCursor(rows[rows.length - 1]) : null;
  return { rows, nextCursor };
}

Translating the tuple comparison (created_at, id) < (x, y) into portable SQL (without relying on PostgreSQL's native tuple comparison syntax) requires this decomposition into an OR, a bit more verbose but compatible with query builders that don't generate tuple comparisons directly. The API contract then exposes an opaque nextCursor instead of a page number, which changes the shape of the response on the client side and needs to be designed into the contract upfront, not bolted on afterward.

The tradeoff to own

Cursor pagination has a real functional cost: it makes it impossible to jump directly to an arbitrary page ("go to page 47"), since there's no notion of a page number anymore, only a starting point and a direction. For a UI that genuinely needs clickable page numbers, that's a UX constraint to negotiate, not a technical detail to wave away. The API contract changes too: it exposes an opaque cursor (often base64-encoded) instead of a plain page number, which requires adapting the client.

It's also worth keeping perspective: on a table with a few thousand rows, or for shallow pages (the first three or four pages of a list), OFFSET's cost stays negligible. Replacing every pagination mechanism with keyset pagination, even where it costs nothing, just adds complexity for zero gain. The right default is hybrid: plain offset for low-depth, low-volume cases, cursor pagination for lists that grow unbounded (activity feeds, exports, public APIs consumed through infinite scroll).

When cursor pagination isn't enough

The cursor fixes the scan cost, not the cost of a total COUNT(*) if the UI still needs one to show "3 of 1,248 results". In that case, either accept an approximate count (an estimate from table statistics, pg_class.reltuples, good enough for an indicative display), or drop the exact count from the UI altogether, which is often a product decision as much as a technical one. On top of that, the cursor approach assumes a stable sort column: sorting dynamically on a column computed on the fly, like a relevance score recalculated on every request, breaks the cursor as-is, and requires materializing or freezing that score so the comparison stays consistent between two pages.

Takeaway

OFFSET's cost grows linearly with page depth because the engine has to walk and discard every preceding row before responding, a cost invisible on a dev database and very real in production at scale. Cursor pagination, with a composite index matching the sort order and a unique tiebreaker, removes that cost at the price of losing direct access to a numbered page: a worthwhile tradeoff for deep, high-volume lists, not a default to apply to every pagination on principle.