iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
Use offset pagination when clients must jump to numbered pages and the result set is modest and relatively stable. Use cursor pagination for a continuation-token interface over large or frequently changing collections; implement that cursor with keyset conditions when the database can seek through an ordered index. Cursor describes the API contract, while keyset describes the database technique, so they are often used together rather than treated as competing choices.
What API pagination does
Pagination divides a collection response into bounded pages instead of returning every matching record at once. A request supplies a page-size limit and a position; the server returns items plus enough information for the client to request the next portion. This limits response size and lets a service work through a collection incrementally.
Pagination is part of an API contract, not merely a SQL optimization. The contract must define the position model, ordering, page-size rules, end-of-results signal, valid filters, and what happens when records are inserted or deleted while a client is traversing the collection. Google’s AIP-158 pagination guidance recommends adding pagination when a collection method is designed; retrofitting it later can break callers that assumed one response contained the complete collection.
Offset pagination: simple page numbers and direct jumps
How it works
An offset is the number of rows to skip before returning the requested limit. A request such as GET /orders?limit=20&offset=40 asks for up to 20 orders after the first 40. The equivalent SQL shape is:
Popular on Amazon
SELECT id, created_at, total
FROM orders
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 40;The client can calculate a page number, for example page=3 with a page size of 20, and translate it to offset=40. This makes offset attractive for search results, reporting screens, and administrative tables where users expect “page 12” or a last-page control.
Why teams choose it
- The request and response are easy to explain, test, cache, and expose in generic frameworks.
- A client can jump directly to an arbitrary page without first downloading earlier pages.
- Existing UI components commonly understand page numbers, offsets, and total-count displays.
Deep-offset cost
Offset does not mean the database can ignore the skipped rows. PostgreSQL documents that rows skipped by OFFSET still have to be computed, so a large offset can become increasingly expensive as the requested page moves deeper into a collection. See PostgreSQL’s LIMIT and OFFSET documentation for the planner and ordering implications.
Performance also depends on the filter, sort, and indexes. A query that scans or sorts a large candidate set for every page can consume more CPU and I/O than a continuation query that seeks directly from the last row. Measure the actual query plan for your database rather than assuming that a small limit makes every offset inexpensive.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Why ordering is mandatory
Without a predictable ORDER BY, the database is free to return different subsets for different requests. PostgreSQL explicitly warns that an unordered limited result is unpredictable. Even with ordering, use a unique tie-breaker: created_at alone is not sufficient when several records share the same timestamp, whereas created_at, id establishes a total order. Keep that order identical on every page request.
Changes between requests
Offset identifies a position in the current result, not a particular record. Suppose page one returns 20 rows and a new row is inserted at the beginning before page two is requested. The row that was formerly at position 20 can shift to position 21, causing a duplicate or omission across the two responses. Deletions can shift rows in the opposite direction. PostgreSQL’s guidance covers deterministic ordering, but repeatable results across separate requests require an explicit service consistency policy; pagination alone does not create a snapshot.
Also popular on Amazon
- Used Book in Good Condition
Cursor pagination: a continuation-token API
Interface and response
A cursor is a marker returned by one response and supplied to request the next response. A typical exchange looks like this:
GET /orders?limit=20
{
"items": [ ... ],
"next_page_token": "eyJ..."
}GET /orders?limit=20&page_token=eyJ...Some APIs use a response field such as next_page_token; others provide navigation links in a response header. The field names are an API choice, so document them rather than making clients infer them.
Opaque, URL-safe tokens
Google AIP-158 requires page tokens to be opaque and URL-safe. “Opaque” means clients treat the value as an uninterpreted continuation handle, not as a public encoding of an ID or timestamp. The server can change whether it stores a signed payload, an encrypted state object, or a lookup key without changing the client contract. The token should indicate where to continue, not grant access; authorize every request normally and do not use a page token as a credential.
A robust token can contain the last resource ID and the index values needed to resume the ordered query. Google’s guidance also expects the other arguments that affect the collection to match the call that produced the token. If a client changes a filter, sort order, tenant, or equivalent argument, reject the token or start a new traversal according to your documented behavior.
End-of-results and short pages
Define the end condition explicitly. Under AIP-158, an empty next_page_token indicates that there is no further page. A response may contain fewer items than requested even when more results remain if the service follows that behavior, so clients should continue based on the token rather than on items.length < page_size.
Also popular on Amazon
Advantages and limitations
- Continuation is naturally sequential and avoids exposing internal row positions.
- Tokens can preserve the server’s continuation state while allowing the implementation to evolve.
- Clients generally cannot jump efficiently to page 50 without walking the preceding continuation chain.
- Stable results still depend on the chosen ordering and on how the service handles writes during traversal; a cursor does not automatically provide a transaction snapshot.
Keyset pagination: seek from ordered key values
The database technique
Keyset pagination continues relative to the last row’s values in the ordering columns. For descending order by timestamp and ID, a conceptual query is:
SELECT id, created_at, total
FROM orders
WHERE (created_at, id) < (:last_created_at, :last_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;The first request omits the continuation predicate. The response supplies the final row’s created_at and id to the next request, usually inside an opaque cursor rather than as raw query parameters. Exact tuple syntax and comparison rules vary by database, so adapt the predicate to the engine and to ascending or descending order.
Use a deterministic tie-breaker
Every row must have an unambiguous place in the sequence. If timestamps can collide, append a unique key such as id to both ORDER BY and the continuation condition. The same columns, directions, and null-ordering rules must be used when finding the next page. Omitting the tie-breaker can make records with equal primary sort values repeat or disappear.
Indexes make the seek useful
A matching ordered index can allow the database to retrieve the first limited rows in order without scanning the remainder. PostgreSQL describes this relationship in Indexes and ORDER BY. For the example, an index beginning with (created_at, id) may support the seek, subject to the query’s filters, directions, and the database planner. Confirm with an execution plan; an index that does not match the predicate and order may not deliver the expected benefit.
Keyset is not the same word as cursor
Keyset names how the server finds the next records. Cursor names how the client asks for continuation. An API can expose an opaque cursor whose internal payload contains keyset values. It can also expose a cursor backed by a server-side snapshot or another mechanism. Therefore, “cursor versus keyset” is often a category mistake: compare the client contract separately from the query strategy.
The Tool Desk
Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Also popular on Amazon
Offset versus cursor and keyset
| Need or constraint | Offset | Cursor backed by keyset (typical) |
|---|---|---|
| Jump directly to an arbitrary page | Natural fit through page number or offset. | Usually awkward because the client must follow continuations sequentially. |
| Familiarity and framework support | Widely understood by clients and UI components. | May require endpoint-specific token handling. |
| Deep traversal of a large collection | Deep skips can become inefficient because skipped rows are still computed. | A seek can avoid relying on a deep skip when a suitable ordered index exists. |
| Rows added or removed during traversal | Position shifts can create duplicates or missing rows. | Often better for forward traversal, but exact consistency depends on token semantics and the service’s data-change policy. |
| Stable order | Requires a unique, predictable ORDER BY. |
Requires stable sort keys and a unique tie-breaker in the continuation condition. |
| Public contract | The position is visible as a page number or offset. | An opaque token hides internal continuation state and permits implementation changes. |
The comparison reflects guidance from Zalando’s REST pagination guidelines and the PostgreSQL documentation linked above. Neither pattern is universally faster: filters, write rate, ordering, index design, and consistency requirements determine the result.
How to choose a pattern
Choose offset when page jumping is a requirement
Offset is appropriate for a modest collection, a relatively stable dataset, and a UI where users must jump to a numbered page or inspect a specific range. Set a maximum offset or provide a different export mechanism if unbounded deep traversal could overload the database. Always impose a deterministic order and test representative high offsets, not only the first page.
Choose cursor or keyset for large, changing collections
Use sequential continuation for feeds, event histories, audit records, inboxes, and other collections that clients consume from beginning to end. Cursor tokens keep the public interface stable; keyset conditions and a matching index can make each subsequent query independent of how many earlier rows exist. Decide whether newly inserted records should appear during an in-progress traversal or whether the service should hold a snapshot-like view, and document that choice.
Use a hybrid when users need both experiences
An endpoint can offer offset navigation for interactive search and a separate cursor-based export or “load more” path for bulk traversal. Keep the semantics explicit: do not silently reinterpret an offset as a cursor, and do not promise that a page number remains stable while records are changing.
Do these 3 things before closing this tab:
1Fix the driver behind crashes, sound loss and screen glitches2Clear out junk files and repair common Windows errors3Scan for outdated or missing drivers - takes under a minuteDesign the pagination contract step by step
- Define the collection and order. Choose filters and a total order. Add a unique tie-breaker to the sort keys, and state whether the order is ascending or descending.
- Set page-size rules. Document the default and maximum. AIP-158 says an omitted or zero page size should select the documented default and values above the maximum should be coerced down. Defaults and maxima can vary by endpoint; GitHub’s documentation is an example of an API that tells callers to check each endpoint and notes that most endpoints cap
per_pageat 100. - Select the position model. Use
limitplusoffsetfor direct page access, or a continuation token for sequential access. If using a token, state which response field or link carries it. - Make tokens opaque and URL-safe. Encode or store the continuation state server-side. Do not require clients to parse it, and do not treat it as authorization.
- Bind tokens to request arguments. Specify which filters, sort settings, tenant identifiers, and other parameters must remain unchanged. Return a clear client error when a token is incompatible, or define a safe restart behavior.
- Define termination. Say whether an absent or empty next token means the collection is exhausted. Ensure clients can distinguish “no next page” from a transient transport failure.
- Document short pages. Tell clients that fewer items than requested may be returned and that the continuation token, not item count alone, determines whether to continue.
- Choose consistency semantics. Explain how inserts, updates, and deletes affect an active traversal. Repeatable reads or a snapshot are service-level choices, not automatic properties of cursor or keyset pagination.
- Authorize every request. A valid token should not bypass authentication, tenant checks, or row-level authorization. Reapply those checks when resolving the next page.
- Test the contract at boundaries. Cover an empty collection, one item, exactly one full page, a partial final page, an invalid or reused token, changed filters, duplicate sort values, deletions, insertions, and the maximum page size.
Implementation and client behavior
Offset request handling
Parse and validate the limit and offset as bounded integers. Clamp an excessive limit to the documented maximum, reject negative values, apply the fixed order, and return links or metadata that let a client calculate the next offset. If a total count is expensive or approximate, do not imply that a displayed page count is exact; expose a count only when its semantics are defined.
Also popular on Amazon
Cursor request handling
On the first request, build the ordered query without a continuation predicate. On later requests, validate and decode the token, verify that its bound arguments match, apply the stored position, and fetch one more row than the public page size when you need to determine whether another page exists. Return only the requested number of items and an empty or omitted next token according to the documented contract. The extra-row technique is an implementation option, not a client-visible rule.
Keyset query handling
Construct the continuation predicate from exactly the final row’s ordered values. For descending order use a “less than” comparison; for ascending order use “greater than,” with the compound comparison matching every sort column. Include the same columns in the index where practical, then inspect the database’s execution plan under realistic filters and data volume.
Following a real API’s links
GitHub’s REST pagination documentation illustrates a client-facing contract based on the HTTP Link header. It also demonstrates Octokit’s paginate() helper for fetching all pages. This is an example of one service’s conventions, not a universal page-size or header standard; clients should follow the links and endpoint documentation supplied by each API.
Troubleshoot common pagination failures
| Symptom | Likely cause | Correction |
|---|---|---|
| Duplicates or gaps between offset pages | Rows changed position between requests, or the query has no unique order. | Add a deterministic tie-breaker and choose documented consistency semantics; use continuation/keyset traversal when sequential consumption matters. |
| Later offset pages become slow | The database computes and discards a growing number of skipped rows. | Measure the plan, constrain deep offsets, add suitable indexes for filters and order, or move the endpoint to keyset-backed continuation. |
| Rows with equal timestamps disappear or repeat | The timestamp is not a unique sort key. | Append a unique ID to the order and to the keyset predicate, and encode both values in the cursor. |
| “Next” request returns an error after a filter change | The token was created for different arguments. | Require the original parameters, reject the mismatched token clearly, and begin a new traversal for the changed query. |
| Client stops too early on a short page | It assumes fewer items than requested means end-of-results. | Continue while a next token or next link is present. |
| Token appears to grant access | Authorization was incorrectly coupled to token possession. | Authenticate and authorize every request independently; treat the token only as a continuation marker. |
Practical decision rule
Start with the user’s navigation requirement. If random page access is essential and the dataset is modest, use bounded offset pagination with a unique order. If clients mainly move forward through a large or frequently changing collection, expose an opaque cursor and implement it with keyset conditions when an appropriate index can support the order. Whichever pattern you select, publish page-size limits, token and filter rules, end-of-results behavior, ordering, authorization, and data-change semantics as part of the API’s contract.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
