Pagination Patterns for APIs: Offset, Cursor, and Keyset Compared

Offset pagination is one of those things that feels completely fine until it isn’t. You ship ?page=1&limit=20, everything works in development, and then six months later someone runs a query with OFFSET 50000 and your database starts sweating. By that point, the API contract is public, clients are built against it, and changing the pagination model feels like a rearchitecting project.

This guide covers the three main pagination patterns used in production APIs, explains why each behaves the way it does at the database level, and gives you a decision framework for choosing the right one before you ship the wrong one.

The Problem With Pagination

Pagination exists because returning unbounded result sets is not practical. A table with 10 million rows cannot be returned in a single response. Clients need chunks, and APIs need a way to express “give me the next chunk after this one.”

The tricky part is that “next chunk” is not as simple as it sounds. Rows can be inserted or deleted between requests. Sort orders may involve multiple columns. Some clients need random access (jump to page 47), while others just need to scroll forward. These requirements pull in different directions, and no single pattern handles all of them equally well.

Offset Pagination

Offset pagination is the default choice for most teams. The API accepts a page and limit (or offset and limit directly), and translates that into a SQL LIMIT and OFFSET clause.

import { Request, Response } from "express";
import { db } from "./db";

interface OffsetPaginationParams {
  page: number;
  limit: number;
}

interface PaginatedResponse<T> {
  data: T[];
  total: number;
  page: number;
  totalPages: number;
  hasNext: boolean;
  hasPrev: boolean;
}

async function getOrders(
  req: Request,
  res: Response
): Promise<void> {
  const page = Math.max(1, parseInt(req.query.page as string) || 1);
  const limit = Math.min(100, parseInt(req.query.limit as string) || 20);
  const offset = (page - 1) * limit;

  const [rows, [{ count }]] = await Promise.all([
    db.query(
      `SELECT id, customer_id, total, created_at
       FROM orders
       ORDER BY created_at DESC
       LIMIT $1 OFFSET $2`,
      [limit, offset]
    ),
    db.query(`SELECT COUNT(*) FROM orders`),
  ]);

  const total = parseInt(count);

  const response: PaginatedResponse<typeof rows[0]> = {
    data: rows,
    total,
    page,
    totalPages: Math.ceil(total / limit),
    hasNext: page * limit < total,
    hasPrev: page > 1,
  };

  res.json(response);
}

This pattern is familiar to every developer. It maps directly to SQL, supports random access (clients can jump to any page), and the total count lets clients render pagination controls with numbered pages.

Why it breaks at scale

The OFFSET clause does not mean “start reading from row N.” It means “read all rows up to N, then discard them.” The database still scans, sorts, and materializes every row before the offset. At OFFSET 50000, you are paying the cost of 50,000 rows you immediately throw away.

Even with an index on created_at, the database must traverse 50,000 index entries before it can begin returning results. The query gets slower the deeper you paginate. At OFFSET 100000 on a busy table, you will see multi-second query times that no amount of connection pooling fixes.

The second problem is correctness. If a new row is inserted between page 1 and page 2 requests, and the sort order puts it near the top, all rows shift by one. Page 2 will repeat the last row from page 1. If a row is deleted, page 2 will skip one row. These are silent, undetectable inconsistencies from the client’s perspective.

When to use offset pagination

Use it when: the dataset is small (under 100k rows), clients need random access or total counts, and the data is relatively static (low insert/delete rate). Admin dashboards, reporting tools, and internal tools are good fits.

Avoid it when: the dataset is large and growing, clients only scroll forward, or data consistency across pages matters.

Cursor-Based Pagination

Cursor-based pagination replaces the numeric page number with an opaque token that encodes enough information to fetch the next set of results. The client passes this cursor back on the next request, and the server decodes it to pick up where it left off.

import crypto from "crypto";

interface CursorPayload {
  id: string;
  createdAt: string;
}

function encodeCursor(payload: CursorPayload): string {
  return Buffer.from(JSON.stringify(payload)).toString("base64url");
}

function decodeCursor(cursor: string): CursorPayload {
  return JSON.parse(Buffer.from(cursor, "base64url").toString("utf8"));
}

interface CursorPaginatedResponse<T> {
  data: T[];
  nextCursor: string | null;
  hasNext: boolean;
}

async function getOrdersCursor(
  req: Request,
  res: Response
): Promise<void> {
  const limit = Math.min(100, parseInt(req.query.limit as string) || 20);
  const rawCursor = req.query.cursor as string | undefined;

  let rows: Array<{ id: string; customer_id: string; total: number; created_at: Date }>;

  if (rawCursor) {
    const cursor = decodeCursor(rawCursor);
    rows = await db.query(
      `SELECT id, customer_id, total, created_at
       FROM orders
       WHERE (created_at, id) < ($1, $2)
       ORDER BY created_at DESC, id DESC
       LIMIT $3`,
      [cursor.createdAt, cursor.id, limit + 1]
    );
  } else {
    rows = await db.query(
      `SELECT id, customer_id, total, created_at
       FROM orders
       ORDER BY created_at DESC, id DESC
       LIMIT $1`,
      [limit + 1]
    );
  }

  const hasNext = rows.length > limit;
  if (hasNext) rows.pop();

  const lastRow = rows[rows.length - 1];
  const nextCursor = hasNext && lastRow
    ? encodeCursor({ id: lastRow.id, createdAt: lastRow.created_at.toISOString() })
    : null;

  const response: CursorPaginatedResponse<typeof rows[0]> = {
    data: rows,
    nextCursor,
    hasNext,
  };

  res.json(response);
}

Notice the (created_at, id) < ($1, $2) clause. This is a row value comparison, and it is the key to cursor pagination correctness. The id column acts as a tiebreaker when two rows share the same created_at timestamp, ensuring deterministic ordering even under concurrent inserts.

Requesting one extra row (limit + 1) is the standard trick for detecting whether a next page exists without a separate COUNT query.

Why cursor pagination is faster

The database does not scan discarded rows. With a composite index on (created_at DESC, id DESC), the query seeks directly to the cursor position and reads forward. The cost is proportional to the page size, not the position in the dataset. Page 500 is as fast as page 1.

It also handles concurrent modifications gracefully. New inserts do not shift existing rows relative to the cursor. The cursor encodes a position in the sort order, not a row number.

The tradeoff: clients lose random access. There is no way to jump to page 47. The cursor is opaque, and clients must follow the chain. Total counts are also gone unless you run a separate COUNT query, which you probably should not if the table is large.

When to use cursor pagination

Use it when: the dataset is large, clients scroll forward (feeds, timelines, audit logs, activity streams), and random access is not required. This is what Twitter, Stripe, GitHub, and most modern APIs use for their list endpoints.

Keyset Pagination

Keyset pagination is closely related to cursor pagination and is sometimes used interchangeably. The distinction is that keyset pagination uses the actual column values as the page token rather than an encoded cursor blob.

interface KeysetParams {
  afterId?: string;
  afterCreatedAt?: string;
  limit: number;
}

async function getOrdersKeyset(params: KeysetParams) {
  const { afterId, afterCreatedAt, limit } = params;

  if (afterId && afterCreatedAt) {
    return db.query(
      `SELECT id, customer_id, total, created_at
       FROM orders
       WHERE (created_at, id) < ($1::timestamptz, $2::uuid)
       ORDER BY created_at DESC, id DESC
       LIMIT $3`,
      [afterCreatedAt, afterId, limit]
    );
  }

  return db.query(
    `SELECT id, customer_id, total, created_at
     FROM orders
     ORDER BY created_at DESC, id DESC
     LIMIT $1`,
    [limit]
  );
}

// API response includes the raw values, not an opaque cursor
interface KeysetPaginatedResponse<T> {
  data: T[];
  pagination: {
    afterId: string | null;
    afterCreatedAt: string | null;
    hasNext: boolean;
  };
}

The API response exposes the actual afterId and afterCreatedAt values. Clients use these directly in the next request query string: ?afterId=abc&afterCreatedAt=2026-03-09T10:00:00Z.

The database performance characteristics are identical to cursor pagination. Both rely on an index on the sort columns and avoid scanning discarded rows.

Cursor vs keyset: the real difference

The choice between cursor and keyset is mostly about API design philosophy:

Opaque cursors (cursor pagination) hide implementation details. You can change the underlying sort mechanism without breaking clients. The cursor blob is an API contract, not the raw column values. Stripe uses this approach.

Transparent keysets expose actual values. Clients can construct requests without receiving a cursor first (useful for some sync patterns). The tradeoff is that changing the sort key breaks existing integrations.

For most APIs, opaque cursors are the safer default. The added flexibility of changing sort implementation is worth the small encoding overhead.

Tradeoffs at a Glance

Dimension	Offset	Cursor	Keyset
Random access	Yes	No	No
Total count	Yes (with COUNT)	No	No
Deep page performance	Degrades (O(offset))	Stable (O(limit))	Stable (O(limit))
Consistency during writes	No (skips/duplicates)	Yes	Yes
Implementation complexity	Low	Medium	Medium
Client complexity	Low	Medium	Low
Supports complex sorts	Yes	Requires encoding	Yes (exposed columns)
Bidirectional traversal	Yes	Possible (prev cursor)	Possible (reversed clause)

Production Considerations

Index design matters more than the pattern

Cursor and keyset pagination both depend on a composite index that matches the ORDER BY clause exactly. If you sort by (created_at DESC, id DESC), your index must cover those columns in that order. A missing or mismatched index will cause a sequential scan, which eliminates the performance advantage of cursor pagination entirely.

-- Create a covering index for the sort and the selected columns
CREATE INDEX orders_pagination_idx
ON orders (created_at DESC, id DESC)
INCLUDE (customer_id, total);

The INCLUDE clause (supported in PostgreSQL 11+) adds customer_id and total to the index leaf pages, allowing index-only scans for the full query. No heap access required.

UUID sort order pitfalls

If your primary key is a random UUID (v4), do not use it as the sole sort column. Random UUIDs have no temporal ordering, so ORDER BY id produces unpredictable results. Use a time-ordered ID (UUIDv7, ULID, or a created_at + id composite) or always pair the UUID tiebreaker with a timestamp column.

import { ulid } from "ulid";

// ULIDs are lexicographically sortable by time
const orderId = ulid(); // "01ARYZ6S41TSV4RRFFQ69G5FAV"

// Safe to sort by ULID alone
const rows = await db.query(
  `SELECT id, customer_id, total, created_at
   FROM orders
   WHERE id > $1
   ORDER BY id ASC
   LIMIT $2`,
  [afterId, limit]
);

Handling deleted rows in cursor chains

If a row is deleted after a cursor is issued, the cursor position remains valid. The next query seeks to the position in the sort order and reads forward, skipping the deleted row naturally. This is correct behavior.

The edge case is deleting the exact row the cursor points to. Since the cursor encodes the column values of the last returned row, and the query uses a strict inequality (< not <=), the deleted row is simply not in the result set. No error, no infinite loop. The query moves to the next row cleanly.

Bidirectional traversal

Implementing “previous page” with cursor pagination requires a reverse cursor. When building the cursor, also encode a prev token based on the first row of the current page. The previous page query flips the sort order and reverses the results:

async function getPrevPage(prevCursor: string, limit: number) {
  const cursor = decodeCursor(prevCursor);

  // Flip the comparison and order to go backwards
  const rows = await db.query(
    `SELECT id, customer_id, total, created_at
     FROM orders
     WHERE (created_at, id) > ($1, $2)
     ORDER BY created_at ASC, id ASC
     LIMIT $3`,
    [cursor.createdAt, cursor.id, limit]
  );

  // Reverse to restore the original descending order
  return rows.reverse();
}

This is the main reason cursor pagination adds complexity compared to offset pagination. Bidirectional traversal requires careful cursor management on both ends of the current page.

When you actually need total counts

If a client genuinely needs a total count (to render “Showing 1,200 of 48,293 results”), a COUNT(*) query on a large table is expensive. A few alternatives:

Use an approximate count. PostgreSQL’s pg_stat_user_tables provides a near-real-time estimate without scanning the table:

SELECT n_live_tup
FROM pg_stat_user_tables
WHERE relname = 'orders';

Cache the count. Run a COUNT(*) on a schedule and cache the result. Stale by seconds or minutes, but acceptable for most UIs.

Maintain a counter. For insert-heavy workloads, keep a separate order_counts table updated via triggers or application code.

None of these are perfect. The right choice depends on how accurate the count needs to be and how often it changes.

Decision Framework

Start here:

Dataset under 50k rows, mostly static? Use offset pagination. The simplicity is worth more than the marginal performance difference.

Dataset large and growing, clients scroll forward (feeds, logs, streams)? Use cursor pagination with opaque tokens. Adopt UUIDv7 or ULID for primary keys if you haven’t already.

Need to expose raw sort keys for client-side sync or external tooling? Use keyset pagination with transparent column values.

Need random access and total counts at scale? Consider whether you actually need them. Random access is rare in real usage. If you do need it, Elasticsearch or a dedicated search layer is often a better fit than fighting SQL OFFSET at depth.

Multi-column sort with filtering? Cursor and keyset both require that the WHERE clause correctly encodes all sort columns. The more complex the sort, the more carefully the cursor encoding must be designed. Test with production-scale data before shipping.

Pagination is one of those API decisions that looks trivial and turns out to matter a lot. Offset pagination will work fine until it doesn’t, and the point where it stops working is always at the worst possible time. Pick the pattern that matches the actual access patterns of your clients, not the one that is easiest to implement today.