Pagination

Why every collection must be paginated

A list endpoint without a limit is a promise you cannot keep. The code may be correct on the day it ships, when the table holds a few hundred rows, and quietly become a liability as the table grows. One SELECT * over a million-row table will serialize tens of megabytes, hold it in memory, and stream it to a client that likely wanted the first twenty items.

The failure is not gradual. At some table size the endpoint crosses a threshold: memory spikes, the event loop stalls, requests time out, retries pile on, and a single slow route takes the whole service down with it. An unauthenticated attacker does not even need a bug, only a URL.

The fix is a hard bound on every collection:

• A default page size so clients get a useful response without thinking about it.
• A maximum page size so no client can ask for everything.
• A stable order so pages do not overlap or skip.
• A continuation token so the client can ask for the next slice.

Even endpoints you believe are small deserve a limit. Tables grow, imports happen, and the endpoint you protect today is the one that stays up tomorrow.

Offset pagination and where it breaks

The most familiar form is LIMIT with OFFSET: return limit rows, starting after offset rows.

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

It is easy to understand, maps naturally to page numbers (page * limit), and lets a client jump to any page. For small, slowly changing tables it is perfectly adequate. For everything else it has three problems that get worse as the data grows.

Deep pages are expensive. To return the rows after an offset, the database must first produce and discard every row before it. OFFSET 1000000 reads a million rows to return twenty. The work grows linearly with the page number, so page 1 is fast and page 50,000 is not.

The window drifts. Offset is a position in a list that is changing. If a row is inserted before your position, the next page repeats an item; if a row is deleted, an item is skipped. The client sees duplicates and holes, and no amount of retrying fixes it.

The order may be unstable. If the sort key has ties, as created_at often does, the database is free to return tied rows in any order. Two requests for the same offset can produce different results.

Offset is not wrong, it is just unsuited to large or fast-moving collections. Use it for admin tables with page numbers over modest data, and reach for a cursor when the list can grow.

Cursor pagination: stable by construction

A cursor replaces “skip N rows” with “start after this row”. The client sends an opaque token that the server produced, and the server turns it back into a precise position in the sort.

Because the token names a row rather than a count, inserts and deletes elsewhere in the result set cannot shift it. Because the server seeks directly to that row, depth costs nothing extra. The two guarantees — stability and constant cost — are exactly the ones offset cannot provide.

The price is that a cursor only moves forward or backward. There is no “page 50”. For feeds, timelines, exports and infinite scroll, that is no loss at all. For a search results grid with numbered pages, offset remains the natural fit until the data gets large.

A cursor should be opaque to clients: base64url-encoded, treated as a black box, and returned unchanged. Opacity lets the server change the sort columns later without breaking clients, and it discourages hand-crafted tokens.

Keyset pagination in SQL

Keyset pagination is what a cursor does at the database level. Instead of counting, you compare the sort columns against the values of the last row you returned.

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

The (created_at, id) < ($1, $2) form is a row-value comparison, and it expresses the logic cleanly: take rows whose sort key comes strictly after the last one. Postgres and most relational databases can use an index that matches the ordering to seek directly to the starting point.

That index is not optional. Keyset pagination is only fast when the sort is backed by an index over the exact columns and directions used in ORDER BY.

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

Two details cause most bugs. First, the comparison direction must match the sort: a DESC sort uses <, an ASC sort uses >. Second, the cursor must include every sort column. If you sort by created_at alone, ties make the cursor ambiguous, which is why id is always appended.

Building and encoding a cursor

A cursor is just the sort values of the last row on the page, serialized and encoded. Keep it small and validate it on the way back in.

export type Cursor = { createdAt: string; id: string };

export function encodeCursor(cursor: Cursor): string {
  return Buffer.from(JSON.stringify(cursor)).toString("base64url");
}

export function decodeCursor(token: string): Cursor {
  let parsed: unknown;
  try {
    parsed = JSON.parse(Buffer.from(token, "base64url").toString("utf8"));
  } catch {
    throw new Error("invalid_cursor");
  }

  const value = parsed as Record<string, unknown>;
  if (typeof value.createdAt !== "string" || typeof value.id !== "string") {
    throw new Error("invalid_cursor");
  }
  return { createdAt: value.createdAt, id: value.id };
}

The limit-plus-one trick tells you whether another page exists without a count. Fetch limit + 1 rows; if you get more than limit, there is a next page, and you drop the extra row before responding.

const rows = await db.query(
  `SELECT id, title, created_at
   FROM posts
   WHERE ($1::timestamptz IS NULL OR (created_at, id) < ($1, $2))
   ORDER BY created_at DESC, id DESC
   LIMIT $3`,
  [cursor?.createdAt ?? null, cursor?.id ?? null, limit + 1],
);

const hasMore = rows.length > limit;
const data = hasMore ? rows.slice(0, limit) : rows;
const last = data.at(-1);
const nextCursor = hasMore && last
  ? encodeCursor({ createdAt: last.created_at, id: last.id })
  : null;

Base64url is an encoding, not a signature. A client can decode it and craft a new one, so never treat a cursor as trusted input. Validate every field, and if tampering matters, sign the payload with an HMAC or keep the sort keys server-side and store the cursor in Redis.

The paginated response envelope

Return one consistent shape from every collection endpoint. Clients then have a single pattern to parse, and you can evolve the internals without changing the contract.

{
  "data": [
    { "id": "post_1042", "title": "Hello", "createdAt": "2026-09-16T10:00:00Z" }
  ],
  "pagination": {
    "nextCursor": "eyJjcmVhdGVkQXQiOiIyMDI2LTA5LTE2VDEwOjAwOjAwWiIsImlkIjoicG9zdF8xMDQyIn0",
    "hasMore": true,
    "total": 1284
  }
}

data holds the page and never exceeds the limit. nextCursor is the token for the following page, or null when the list is exhausted, which is the signal for infinite scroll to stop. hasMore is a convenience that saves clients from checking for null, and it falls out of the limit-plus-one fetch for free.

total is optional and should be treated as such. A cursor page does not need it, and computing it on every request is often more expensive than the page itself. Include it only when the UI genuinely shows “1,284 results”, and then cache it or approximate it.

Sorting stability and tie-breakers

A cursor is only meaningful if the sort is a total order: for any two rows, one is definitively before the other. Most natural sort keys are not. Many posts share the same created_at, and the database may return them in any order, so a cursor that encodes only the timestamp can skip or repeat rows.

The fix is to append a unique column, almost always the primary key, as the final sort key.

ORDER BY created_at DESC, id DESC

Now the order is deterministic, and the cursor (created_at, id) is unique. The same rule applies to any sort: ORDER BY score DESC, id DESC, ORDER BY name ASC, id ASC. The tie-breaker must be unique, and it must be part of both the index and the cursor.

The sort keys must also be stable over time. Sorting by updated_at and using it in a cursor is a trap: when a row is edited, its sort position moves, and a cursor captured before the edit can point into the wrong place. Prefer immutable keys such as created_at or a monotonic id, and if you must sort by a mutable field, accept that cursors may become stale.

Total counts are expensive

A total count is the most requested and least necessary part of pagination. SELECT count(*) with a filter must examine every matching row, and on a large table that can take longer than fetching the page.

-- Runs on every request if you are not careful.
SELECT count(*) FROM posts WHERE author_id = $1;

A cursor page does not need it. hasMore answers the question the client actually has — “is there more?” — without touching a single extra row. If the UI truly needs a number, choose an approach that fits the accuracy it can tolerate:

• Omit it. Most infinite-scroll and feed interfaces never show a total.
• Approximate it. Postgres exposes reltuples on pg_class and the planner can estimate with EXPLAIN; both are fast and wrong by a few percent.
• Cache it. Compute the count on a schedule or after writes and serve the stored value.
• Maintain a counter. Keep a running count in a summary table updated inside the same transaction as the writes.
• Cap it. Stop counting at 1,000 and return “1000+”, which bounds the cost.

Whatever you pick, do not run an uncached count(*) on every page request of a large table.

Page size: defaults and limits

Two numbers protect the server: a default when the client says nothing, and a hard maximum when the client asks for too much.

const DEFAULT_LIMIT = 20;
const MAX_LIMIT = 100;

function parseLimit(raw: string | undefined): number {
  const requested = Number.parseInt(raw ?? "", 10);
  if (!Number.isFinite(requested) || requested < 1) return DEFAULT_LIMIT;
  return Math.min(requested, MAX_LIMIT);
}

Clamp rather than reject. A client asking for limit=1000 should receive 100 rows and a cursor, not a 400 error that forces it to guess your rules. Validate that limit is a positive integer and never pass a client string straight into SQL.

Page size is a latency dial. Larger pages mean fewer round-trips but more work per request and more bytes on the wire. For interactive UIs, 20 to 50 is usually right. For bulk exports, use a dedicated endpoint with a much larger cap and streaming, rather than raising the limit on the interactive route.

Filtering and sorting parameters

Pagination composes with filtering and sorting, but both must be handled carefully because they change the meaning of a cursor.

GET /posts?author_id=42&sort=-created_at&limit=20&cursor=eyJjcmVhdGVkQXQiOi...

Whitelist sort fields. Never interpolate a client-supplied column name into SQL. Map an allowed set of names to expressions, and pick the direction from an explicit prefix or parameter.

const SORTS = {
  created_at: "created_at",
  title: "title",
  score: "score",
} as const;

const column = SORTS[sortField] ?? "created_at";
const direction = order === "asc" ? "ASC" : "DESC";

Make the cursor match the sort. If the sort changes, the cursor is meaningless. Either encode the sort into the cursor and reject a mismatch, or include the sort in the cursor’s payload and verify it on decode. The same goes for filters: a cursor from an unfiltered list should not be replayed against a filtered one.

Add every sort column to the index. A composite index on (author_id, created_at DESC, id DESC) serves the filter and the keyset seek in one structure, which is the difference between a page in a millisecond and a page in a second.

Pagination over search and aggregations

Search engines and aggregations have their own rules. Full-text search backends usually cap from + size at around ten thousand results, because deep offset is expensive for them too. The equivalent of keyset there is a search_after token built from the sort values of the last hit.

POST /posts/_search
{
  "size": 20,
  "sort": [{ "created_at": "desc" }, { "id": "desc" }],
  "search_after": ["2026-09-16T10:00:00Z", "post_1042"]
}

Aggregations are best returned separately from the page. Computing facet counts over every match on every request is the same trap as count(*). Either compute them once and cache them, or expose a dedicated endpoint the UI calls when the user opens a filter panel.

For SQL aggregates, the same keyset idea applies: order by a stable aggregate key such as a date bucket or an id, and use that key in the cursor. Do not paginate a GROUP BY with OFFSET over a large table; materialize the aggregate first and paginate the materialized result.

Choosing a strategy

Most teams only need one rule: if the collection can grow large or change while it is being read, use a cursor; if it is small, slow-moving and shown as a numbered grid, offset is fine.

Small table, numbered UI          -> offset
Large or fast-changing collection -> keyset cursor
Infinite scroll or mobile feed    -> keyset cursor
Search results                    -> search_after token
Bulk export                       -> dedicated streaming endpoint

Offset and cursor can coexist. An admin table might offer page numbers for browsing and a cursor for an “export all” flow. What matters is that each endpoint picks one style and documents it, rather than mixing page and cursor parameters in a way clients cannot predict.

Backward pagination

Forward pagination gets all the attention, but many interfaces also need a previous button. The technique is to reverse the comparison and the ordering, fetch one page, then reverse the rows in application code before returning them.

async function pageBackward(prev: Cursor, limit: number) {
  const rows = await db.query(
    `SELECT id, title, created_at
     FROM posts
     WHERE (created_at, id) > ($1, $2)
     ORDER BY created_at ASC, id ASC
     LIMIT $3`,
    [prev.createdAt, prev.id, limit + 1],
  );

  // Reverse back into the canonical descending order.
  return rows.reverse();
}

Return a prevCursor built from the first row of the current page alongside nextCursor built from the last. A client walking backward flips to the forward direction when the user returns to scrolling down, so the cursors must be interchangeable rather than tied to a direction.

Keeping cursors honest

Base64url is an encoding, not a signature. A client can decode a cursor, edit it, and send it back, so a cursor is untrusted input exactly like a query parameter. Validate every field on decode, and reject anything that does not fit the expected shape before it reaches SQL.

If tampering is a real concern — for example, if a cursor carries a tenant id — sign it with an HMAC and verify the signature in constant time.

import { createHmac, timingSafeEqual } from "node:crypto";

const secret = process.env.CURSOR_SECRET!;

export function signCursor(payload: string): string {
  const mac = createHmac("sha256", secret).update(payload).digest("base64url");
  return `${Buffer.from(payload).toString("base64url")}.${mac}`;
}

export function verifyCursor(token: string): string {
  const [encoded, mac] = token.split(".");
  const expected = createHmac("sha256", secret).update(encoded).digest("base64url");
  const a = Buffer.from(mac);
  const b = Buffer.from(expected);
  if (a.length !== b.length || !timingSafeEqual(a, b)) {
    throw new Error("invalid_cursor");
  }
  return Buffer.from(encoded, "base64url").toString("utf8");
}

An alternative is to keep cursors fully server-side: store the position in Redis under a random id and hand the client only that id. It hides the sort columns completely and allows expiry, at the cost of a lookup on every page.

A client that follows cursors

A cursor is designed to be followed, so the client code is a simple loop: request a page, append the data, and continue while nextCursor is not null. There is no page arithmetic and no risk of skipping a page.

async function fetchAll<T>(path: string): Promise<T[]> {
  const items: T[] = [];
  let cursor: string | null = null;

  do {
    const url = new URL(path, "https://api.example.com");
    url.searchParams.set("limit", "100");
    if (cursor) url.searchParams.set("cursor", cursor);

    const res = await fetch(url);
    if (!res.ok) throw new Error(`request_failed_${res.status}`);

    const page = await res.json();
    items.push(...page.data);
    cursor = page.pagination.nextCursor;
  } while (cursor);

  return items;
}

The loop terminates on nextCursor === null, which is why that field must be reliably set on the final page. For infinite scroll, the same pattern runs one page at a time as a sentinel element enters the viewport, and the token is kept in component state rather than the URL.

Pagination and the query plan

Keyset pagination is fast only when the database can use an index. Always confirm it with EXPLAIN ANALYZE rather than assuming.

EXPLAIN ANALYZE
SELECT id, title, created_at
FROM posts
WHERE (created_at, id) < ('2026-09-16T10:00:00Z', 'post_1042')
ORDER BY created_at DESC, id DESC
LIMIT 20;

Limit  (cost=0.43..8.94 rows=20 width=40)
       (actual time=0.021..0.058 rows=20 loops=1)
  ->  Index Scan Backward using posts_created_id_idx on posts
        (cost=0.43..521.10 rows=417 width=40)
        (actual time=0.019..0.051 rows=20 loops=1)
        Index Cond: (ROW(created_at, id) < ROW('2026-09-16T10:00:00Z'::timestamptz, 'post_1042'))
Execution Time: 0.081 ms

The Index Scan Backward with an Index Cond is the result you want: the database seeks to the cursor position and stops after twenty rows. A Seq Scan with a Filter means the index does not match the sort, and the query is scanning the whole table on every page. The index must list the same columns in the same order and directions as ORDER BY, with the tie-breaker last.

Testing pagination

The properties worth testing are stability and termination, not just the happy path. A test that walks every page and asserts no duplicates and no missing rows catches the subtle tie-breaker bugs that are otherwise invisible.

test("cursor pagination never repeats or skips rows", async () => {
  const seen = new Set<string>();
  let cursor: string | null = null;

  do {
    const page = await request(app)
      .get("/posts")
      .query({ limit: 10, cursor: cursor ?? undefined })
      .expect(200);

    for (const post of page.body.data) {
      expect(seen.has(post.id)).toBe(false);
      seen.add(post.id);
    }
    cursor = page.body.pagination.nextCursor;
  } while (cursor);

  expect(seen.size).toBe(totalPosts);
});

test("rejects a malformed cursor", async () => {
  await request(app).get("/posts?cursor=not-a-cursor").expect(400);
});

Also test the boundaries: the first page with no cursor, the last page where nextCursor is null, a page larger than the maximum, and a sort that changes mid-walk. The last one is the test that proves your tie-breaker works.

Best practices

• Give every collection a default limit and a hard maximum, and clamp rather than reject.
• Prefer keyset or cursor pagination for anything that grows or changes.
• Always sort with a unique tie-breaker such as id, and include it in the index and the cursor.
• Keep cursors opaque, encode them as base64url, and validate every field on decode.
• Fetch limit + 1 to determine hasMore instead of running a count.
• Return a consistent envelope with data, nextCursor and hasMore everywhere.
• Treat total as optional; omit, approximate or cache it.
• Whitelist sort fields and directions, and bind all values as parameters.
• Make the cursor encode the sort and filter context so a stale token cannot be replayed.

Common mistakes

• Shipping a list endpoint with no limit and discovering it at scale.
• Using OFFSET for deep pages and watching latency grow with the page number.
• Sorting on a non-unique column without a tie-breaker, so rows repeat and vanish.
• Sorting by a mutable column and treating the cursor as permanent.
• Passing a client-provided limit, column name or direction straight into SQL.
• Running an uncached count(*) on every page request.
• Exposing raw internal ids or timestamps in a cursor and calling it secure.
• Returning a bare array with no cursor, forcing clients to guess how to continue.
• Allowing a limit of a million because the UI never asks for it.

Where to go next

Pagination is part of designing a predictable API, so the REST guide is the natural companion for resource shapes, status codes and query conventions. If you want to stop recomputing the same page, Caching covers serving it from memory, and Connection Pooling keeps each paged query cheap on the database. To make those queries fast in the first place, PostgreSQL explains the composite indexes and query plans that keyset pagination depends on.