Pagination

Pagination divides a potentially large result set into bounded responses, so a client reads a long list one page at a time instead of receiving every row at once. It is the main tool an API has for keeping the work per request, and therefore latency and memory, predictable. It is also a consistency problem: while a client walks a list, the list keeps changing, and the pagination scheme decides which rows that client will see, miss, or see twice. API pagination is therefore a contract between server and client about ordering, page boundaries, and what a "next page" token promises, not just a LIMIT clause.

Consider GET /orders on a table with 50 million rows and no paging rule. The database scans and serialises everything that matches, the application holds the whole result in memory, the response saturates the network connection, and the client either freezes or runs out of memory while parsing it. Even when one such request survives, a handful of them in parallel is enough to degrade the database for every other user. Returning only what the screen needs, quickly, protects the database as much as it helps the client. Pagination is far easier to design in from the first version of a list endpoint than to retrofit into one that clients already depend on, because a "return everything" response cannot gain a page limit without breaking them.

A pagination contract is complete only when it answers four questions, and every strategy in this lesson is a different set of answers to them:

1. How are records ordered? A page is a slice of an ordered sequence, so the order must be total and deterministic. If two rows can tie, the database may return them in either order on different calls.
2. How does the client identify the next page? By counting (skip N rows), or by position (continue after this row). The first is positional arithmetic; the second is an anchor into the data itself.
3. What happens when rows are inserted or deleted between requests? Each request is a separate read. Unless you do something extra, nothing ties request two to the data request one saw.
4. How much work must the database do to find each page? Finding the first row of a page can cost far more than returning the twenty rows on it.

The mental model to carry through the rest of the article is the difference between a count and an anchor. Offset pagination says "skip this many rows", a number that means something only relative to the list as it was an instant ago. Keyset and cursor pagination say "continue after this row", a position that stays meaningful when rows are added or removed elsewhere. The count is simple and supports page numbers; the anchor is cheaper to resolve and steadier under change. For where the cursor and page-size parameters live in an HTTP list endpoint, see REST; for the contract conventions that apply to every endpoint, see API Design.

Offset pagination expresses the page as a count of rows to skip. The query below returns the third page of twenty orders for one customer:

SELECT id, created_at, total_minor
FROM orders
WHERE customer_id = :customer_id
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 40;   -- skip (page - 1) * 20 = 40 matching rows, return the next 20

This maps naturally onto numbered pages, needs no state beyond two integers, and is perfectly adequate for small result sets, internal dashboards, and any screen where jumping straight to page 17 matters. Note that the ORDER BY already includes id as a tie-breaker: even offset pagination is only repeatable when the order is total.

The offset tax

The weakness is that an index does not make an arbitrary offset free. To return rows 100,001 to 100,020 the database still has to locate and step over the first 100,000 matching entries; PostgreSQL's documentation notes that rows skipped by OFFSET are still computed inside the server. The exact cost depends on the plan, filters, index, and engine, but it generally grows with the number of skipped rows. A keyset query, covered below, instead seeks to the anchor through the index and reads only the page, so the work it does is set by the page size. At scale, finding the rows often costs more than returning them.

The figure counts index entries visited, which is a property of the algorithm rather than a benchmark. Elapsed time follows it only roughly: caching, parallelism, and the engine's skip implementation all change the constant. As an illustration of the shape (one dataset, one engine, numbers not portable), a query that takes a couple of milliseconds on page 1 can take hundreds of milliseconds at page 1,000 and well over half a second at page 5,000, while the equivalent keyset query stays near its page-1 time at every depth. Measure on your own data with EXPLAIN (ANALYZE) before deciding that you have a depth problem at all.

Take a newest-first order list at time t1: O9, O8, O7, O6, O5, O4, with three rows per page. The client fetches page 1 and receives O9, O8, O7. Before it asks for page 2, a new order O10 is created. At t2 the list is O10, O9, O8, O7, O6, O5, O4, and page 2 is OFFSET 3, which skips O10, O9, O8 and returns O7, O6, O5. The client now holds O7 twice. The new row pushed every existing row one position down, so "skip three" no longer means "skip what I already saw."

Deletions break the other way. If O9 is deleted after page 1, the list at t2 is O8, O7, O6, O5, O4; OFFSET 3 skips O8, O7, O6 and returns O5, O4. O6 was never delivered, and nothing in the response says so. Duplicates are at least visible to a client that de-duplicates by id; a silent skip is not recoverable by the client at all. Offset pagination gives no stable snapshot across independent requests, which matters for feeds, exports, reconciliation jobs, and any workflow that must process each record exactly once.

The figure below runs both strategies over the same data with real pagination logic: the offset window is a slice at (page - 1) × 3, and the keyset page is every row strictly after the last key the client saw. Use the buttons to move between "nothing changed", "a row was inserted", and "a row before the offset was deleted", and raise the page count to 3 to see the traversal complete.

Read the keyset timeline carefully, because it shows what keyset does and does not promise. After the insert, page 2 asks for rows strictly after (7, O7) and returns O6, O5, O4; the new O10 sorts before the cursor, so this client never sees it. That is the correct outcome for a walk that started at t1: the new row belongs to the top of the list, and the client can see it by refreshing from the beginning. Keyset removes the shifting-window artefact; it does not make the client's view complete or up to date. The cases where keyset itself repeats or misses a row are covered in Changing Sort Keys, Snapshots, and Exports.

With a descending (created_at, id) order, the first page is an ordinary ordered query that asks for one more row than it will return:

SELECT id, created_at, total_minor
FROM orders
WHERE customer_id = :customer_id
ORDER BY created_at DESC, id DESC
LIMIT 21;

Suppose the last returned row has created_at = '2026-10-29T10:00:00Z' and id = 'ord_1042'. The next page asks for rows strictly after that key:

SELECT id, created_at, total_minor
FROM orders
WHERE customer_id = :customer_id
  AND (created_at, id) < (:last_created_at, :last_id)
ORDER BY created_at DESC, id DESC
LIMIT 21;

The row-wise comparison (created_at, id) < (:c, :i) is lexicographic: it means created_at < :c OR (created_at = :c AND id < :i). It is valid here because both columns sort in the same direction, so the ordering the query asks for and the ordering the comparison tests are the same. The tie-breaker is not decoration. Orders are created in bursts, and many can share one timestamp, so created_at alone is not a unique position in the list.

The figure uses three orders created in the same second and a page size of two. Page 1 ends at ord_1042. Comparing on the timestamp alone with < excludes the whole tied group, so ord_1041 and ord_1040 are silently skipped. Using <= repairs the skip but re-delivers ord_1042, and when more rows tie than fit on a page the cursor never advances: page 3 equals page 2 forever. The composite comparison returns exactly ord_1041, ord_1040, then ord_1039. The same mistake shows up on the ordering side: if the query orders only by created_at, the database may order tied rows differently on successive calls, producing repeats and gaps even when nobody is writing. A deterministic, unique tie-breaker is mandatory for any sort key that can repeat.

When the tuple comparison is not valid

Row-value comparison is only a shorthand for same-direction keys. Not every engine supports it (SQL Server, for instance, has no row-value comparison), and even where it exists the optimizer's use of it for index range scans varies, so check the plan with EXPLAIN. Two common cases need the predicate written out explicitly.

Mixed directions. For ORDER BY created_at DESC, id ASC the tuple form would compare id in the wrong direction. The correct continuation expands to:

WHERE customer_id = :customer_id
  AND (   created_at < :last_created_at
       OR (created_at = :last_created_at AND id > :last_id))
ORDER BY created_at DESC, id ASC
LIMIT 21;

Nullable sort keys. In SQL, NULL compared with anything is unknown, so a row whose key is null silently drops out of a predicate built from <. State where nulls sort, and handle them in the predicate. With NULLS LAST, continuing after a row with a real timestamp must still admit the null rows that follow it:

-- cursor row has a non-null created_at
AND (   created_at < :last_created_at
     OR (created_at = :last_created_at AND id < :last_id)
     OR created_at IS NULL)

-- cursor row itself has created_at IS NULL
AND (created_at IS NULL AND id < :last_id)

The supporting composite index

A keyset query is only cheap if the database can seek to the cursor and read forward. That needs an index whose leading columns are the equality filters, followed by the sort keys in query order:

CREATE INDEX idx_orders_customer_created_id
ON orders (customer_id, created_at DESC, id DESC);

The engine descends the B-tree to the first entry after the cursor within one customer and then reads a bounded number of entries, so the cost depends on the page size rather than on how far into the list the client is. In PostgreSQL a B-tree can also be scanned backwards, so declared directions matter mostly when they are mixed. Real performance still depends on selectivity, heap lookups for columns outside the index, and the plan the optimizer picks, so verify with EXPLAIN (ANALYZE) on realistic data rather than assuming.

Backward traversal

Keyset is natural for "next". "Previous" is the same query mirrored: flip the comparison, flip the order, and reverse the rows in the application. Starting from the first row of the current page:

SELECT id, created_at, total_minor
FROM orders
WHERE customer_id = :customer_id
  AND (created_at, id) > (:first_created_at, :first_id)
ORDER BY created_at ASC, id ASC     -- reversed so LIMIT takes the rows nearest the cursor
LIMIT 21;
-- then: drop the 21st row, reverse the remaining 20 into display order

A cursor is an opaque token that tells the server where to continue. Keyset pagination is the most common way to implement that token because the position is a real key in the data, but the two terms are not synonyms. Other cursor systems encode a position in a snapshot, a database-specific continuation token, or a handle to server-side state. "Cursor" describes the contract the client sees; "keyset" describes how the server turns the token into a query. Keeping them apart lets you change the implementation (say, from a single key to a composite key, or from live traversal to a snapshot) without breaking clients.

{
  "data": [{ "id": "ord_1043" }, { "id": "ord_1042" }],
  "page": {
    "has_more": true,
    "next_cursor": "<opaque-token>"
  }
}

A useful token carries a format version, the last sort key, enough context to recognise the query it belongs to, and an expiry, and is authenticated so it cannot be edited. Base64 is an encoding, not protection: anyone can decode and modify eyJpZCI6OTk5OX0=. Avoid putting sensitive values in a readable token, and sign it (an HMAC) or encrypt it as your threat model requires.

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

interface CursorPayload {
  v: 1;                    // format version, lets you change the key shape later
  k: [string, string];     // last created_at, last id
  f: string;               // hash of canonical filter + sort + tenant
  exp: number;             // expiry, epoch seconds
}

const sign = (body: string, key: Buffer) =>
  createHmac('sha256', key).update(body).digest('base64url');

export function encodeCursor(p: CursorPayload, key: Buffer): string {
  const body = Buffer.from(JSON.stringify(p)).toString('base64url');
  return body + '.' + sign(body, key);
}

export function decodeCursor(token: string, key: Buffer, expectedF: string, now: number): CursorPayload {
  const [body, sig] = token.split('.');
  const good = Buffer.from(sign(body ?? '', key));
  const given = Buffer.from(sig ?? '');
  if (good.length !== given.length || !timingSafeEqual(good, given)) throw new Error('invalid_cursor');
  const p = JSON.parse(Buffer.from(body, 'base64url').toString()) as CursorPayload;
  if (p.v !== 1 || p.exp < now) throw new Error('expired_cursor');
  if (p.f !== expectedF) throw new Error('cursor_does_not_match_query');
  return p;
}

Signing only gives integrity. The decisive protection is binding: the cursor is valid only for the filter, sort order, and tenant it was issued for, and the server recomputes that fingerprint from the current request and compares it. Otherwise a client can take a cursor from ?status=paid and replay it with ?status=refunded&sort=total, where its key means nothing, or take a cursor issued to one tenant and use it for another. Binding is not authorization either: every query must still apply the authenticated caller's scope (WHERE customer_id = :caller) independently of whatever the token says. A request that fails validation should be an explicit error, not an empty page, so a client bug is not mistaken for "no more data".

The incident this prevents is mundane. A list API used base64({"id": 4231}) as its cursor and its query was WHERE id > :cursor_id with no owner filter; a client edited the token to a neighbouring id and read other users' rows. The encoding was never the defect. The query trusted the token instead of the authenticated caller. Signing makes tampering detectable and discourages probing, but it is an integrity measure, not an authorization control.

A client must not construct, parse, or compare cursors. Treat the token as a bookmark valid for the same logical query, and document an expiry (for example, minutes to days depending on how long clients legitimately pause) so you can retire old key formats.

Keyset pagination avoids one specific failure: the shifting window caused by rows inserted before the cursor. It does not provide snapshot isolation across requests, and the guarantee is easy to overstate: a deterministic, tie-broken cursor stops inserts and deletes before the cursor from shifting later pages, but nothing more. If a row's sort key changes, the row can jump across the cursor boundary. Newest-first by updated_at, an unseen row that gets updated moves to the top, behind the cursor, and is never delivered. Oldest-first, an already-delivered row that gets updated moves to the end and is delivered a second time. Deletions and changed filters likewise alter what is visible mid-walk.

For a live feed this is often acceptable: the client sees a plausible, mostly-current list and refreshes from the top. The remedy for correctness-sensitive reads is to sort on a key that never changes once written (created_at and a unique id, or an append-only sequence) so that no row can cross the cursor.

For an export that must represent one consistent point in time, live traversal is not enough. Two techniques are common, and both have limits. A high-water mark captures the maximum immutable sequence number when the export starts and pages only through rows at or below it:

-- once, at export start
SELECT max(sequence) AS hwm FROM ledger_entries;

-- every page
SELECT sequence, account_id, amount_minor
FROM ledger_entries
WHERE sequence > :last_sequence AND sequence <= :hwm
ORDER BY sequence
LIMIT 1000;

This bounds the set of new rows, but it does not freeze updates or deletions to older rows. It also has a subtle hole: if sequence values are assigned when a transaction inserts but become visible only when it commits, a transaction holding a sequence value below hwm can commit after you read the maximum, and its row would be missed. You must wait for in-flight writers to finish, or use a commit-ordered feed. The alternative is a real database snapshot (for instance a repeatable-read transaction, optionally shared with workers through an exported snapshot in PostgreSQL). That gives the strongest semantics, at the price of a long-lived transaction that holds a connection, can delay cleanup of old row versions, and cannot be carried across stateless HTTP requests without a server-side export job that owns it. Strong snapshot semantics need an appropriate consistency mechanism; pagination alone never supplies one.

A total_count is not free. On a large filtered set, an exact count may require scanning every matching index entry (in an MVCC database such as PostgreSQL, even an index-only count must still check row visibility), and the number is stale as soon as it is returned. Decide per screen whether you need an exact count, an approximate one, or none. Infinite-scroll lists only need has_more, which the LIMIT n+1 trick provides for nearly nothing. When a count is wanted for display, a capped count is a good middle path:

SELECT count(*) FROM (
  SELECT 1 FROM orders
  WHERE customer_id = :customer_id
  LIMIT 1001
) capped;   -- 1001 means "more than 1000"

Offset pagination supports "jump to page 10" because page numbers are just arithmetic. Keyset pagination optimizes next and previous; reaching an arbitrary position without walking there requires resolving a number into a key, which the database cannot do without counting. Do not promise numbered pages on a keyset API unless you have designed how those positions are resolved. Practical options are to jump by a meaningful key instead (a date or an alphabetical prefix), to offer numbered pages for the first few pages only and switch to next/previous beyond them, or to keep a precomputed table of page-boundary keys for data that rarely changes.

Two mistakes recur. The first, sorting only by a non-unique column, was shown above. The second is letting the query shape escape the design. Allowing a client to send limit=1000000 turns a bounded response into the unbounded one pagination was meant to prevent, so set a default (say 20), enforce a maximum (say 100; both illustrative), and document whether you clamp or reject values above it. Clamping is friendlier; rejecting makes client bugs visible. Pair it with request limits from rate limiting, because one client paging aggressively through deep offsets consumes far more database time than its request count suggests.

The deeper mistake is index mismatch. An index on (customer_id, created_at DESC, id DESC) serves the order-history query perfectly and does nothing for a client that filters by status and sorts by price. The database will filter and sort a large intermediate set for every page, and the "fast" keyset query becomes a repeated sort. The fix is to support only the access patterns you have indexed, for example with a whitelist of sort fields (which also keeps arbitrary column names out of your ORDER BY), and to build one composite index per supported combination, equality filters first and then the sort keys with the unique tie-breaker last, such as (customer_id, status, price, id). Each index costs write throughput and storage, which is a good reason to keep the list of sortable fields short.

Three failures account for most pagination incidents in practice. A bulk export walks a table with OFFSET in steps of a few thousand; the first pages are fast, and by page 50,000 each request makes the database step over a million entries, so the job gets slower as it progresses and drags interactive traffic down with it. Cap the maximum offset on interactive endpoints, move deep traversal to keyset, and run exports as a separate job (ideally against a replica). A team adds ORDER BY popularity DESC to a listing whose column has no index, and every page request now sorts the whole filtered set; index the sort key as part of the same change. And an API that returns a total on every page runs a counting query on every page view, which is why has_more is the default and totals are opt-in.

One correction to a common rule of thumb: a random UUID is a perfectly good unique tie-breaker, because it only has to make the order total. It becomes a problem only when it is the sole sort key and users expect the list to be chronological; pair it with created_at, or use a time-ordered identifier such as UUIDv7 or ULID.

Production APIs commonly impose endpoint-specific pagination limits. Cursor-based pagination helps navigate changing datasets but does not, by itself, guarantee a consistent snapshot across requests. The summaries below are illustrative and endpoint-specific; check the current docs before copying a parameter name, since they do change.

Stripe

Every list endpoint takes limit, starting_after and ending_before, where each cursor is the id of an object you already received, and the response carries has_more. The cursor is a real record id, which works because Stripe orders lists by a creation-ordered key.

GitHub

The REST API returns RFC 8288 Link headers with rel="next", "prev", "first" and "last", and clients are told to follow them rather than build URLs. Many REST endpoints are page-numbered (page, per_page) and some use before/after cursors; the GraphQL API uses cursor connections (first, after, pageInfo.endCursor).

Instagram Graph API

Cursor-based paging with before and after cursors inside a paging object, plus ready-made next and previous URLs, because a feed has no meaningful page numbers and new content keeps arriving.

X (Twitter) API v2

Timelines and search return a next_token in the response meta, which the client sends back as pagination_token, again an opaque token for a fast-moving feed.

These APIs paginate results and apply endpoint-specific page-size limits, and cursor pagination does not, by itself, provide a stable snapshot across pages: a cursor promises to continue from where you were. That is the contract worth copying.

Take GET /products over about one million rows. A first version, built for a numbered-page UI, looks like this:

GET /products?limit=20&offset=0
200 OK
{ "data": [ ...20 items ], "total": 1000000, "page": 1, "pages": 50000 }

GET /products?limit=20&offset=100000
200 OK     (illustrative: ~300 ms, about 100,020 index entries visited, 20 returned)

Moving to a cursor changes the request and response, and changes where the work happens. The server orders by (created_at, id), asks for 21 rows, and returns a token for the last row it kept:

GET /products?limit=20
200 OK
{ "data": [ ...20 items ],
  "page": { "has_more": true, "next_cursor": "eyJ2IjoxLCJrIjpbIjIwMjYtMTAtMjlUMTA6MDA6MDBaIiwicHJkXzQyMCJdfQ.q7..." } }

GET /products?limit=20&cursor=eyJ2IjoxLCJrIjpb...
200 OK     (illustrative: ~2 ms at any depth; a B-tree seek plus 21 entries)

GET /products?limit=20&cursor=<edited-token>
400 Bad Request   { "type": ".../invalid-cursor", "title": "Invalid cursor" }

Three decisions made this change safe. The cursor carries the full composite key, not just an id, so it keeps working when the sort is anything other than a unique monotonic column. The token is opaque and verifiable, so the server can later change what it encodes. And the total field disappeared on purpose: the UI switched to infinite scroll, which needs only has_more. If product management insists on a count, the capped count shown earlier ("1000+") gives them something honest at a bounded cost. The list is a GET, so a client that loses a response can safely retry with the same token; see Retry & Backoff for how to do that politely.

Start from consistency and navigation requirements, then let data size break ties. The flow below encodes the decisions in the order that matters: a point-in-time requirement overrides everything, a need for page numbers comes next, and only then does table size decide how long offset stays acceptable.

Compared directly, the two main approaches trade in opposite directions. Offset gives you numbered pages, a trivial implementation, and stateless URLs a human can edit, and costs you depth-dependent work and shifting windows under writes. Keyset gives you work proportional to the page size, a stable walk when rows arrive before the cursor, and cheap has_more, and costs you arbitrary page jumps, a unique ordered key, a matching composite index per supported sort, and a more careful implementation (mixed directions, nulls, backward traversal). Both inherit the same blind spot: neither is a snapshot. The scale at which offset stops being acceptable is a measured property of your table and queries, not a universal number, which is why "ten million rows" is a hint and not a rule.

The pattern across the workloads: keyset improves deep-page efficiency and avoids offset shifting, but it does not create a frozen snapshot of changing data. The right strategy follows from the access pattern and the consistency requirement, not from which one is more fashionable.

Where the limit and cursor parameters, Link headers, and error responses belong in a resource-oriented API is the subject of REST, and the conventions that keep list endpoints consistent across a whole API are in API Design. A client that pages aggressively can cost far more database time than its request count suggests, so pair deep or bulk traversal with rate limiting, and make resumed walks polite with retry and backoff. Writes that accompany a paginated workflow, such as an export that creates a job, are the territory of Idempotency.