Quick wins for a faster PC:
Scan for outdated or missing drivers - takes under a minuteDriver Scan →Repair Windows errors before they cause bigger problemsFix Now →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
Offset pagination can make early API tests look fine, then slow down on deep pages or return duplicates and omissions when the result order is unstable. The cause is not that skip() literally lies: databases may still need to process rows before the requested offset, and separate page requests can see a changing dataset. A deterministic sort and, for sequential traversal, indexed keyset pagination address those problems—but they do not automatically provide a frozen snapshot.
Why is my API pagination slow?
With page-number pagination, an API commonly converts a page number and page size into an offset. For example, page 100 at 25 results per page starts after 2,475 rows. The response may contain just 25 records, but the database may still have to do work to reach the starting position.
MongoDB documents that skip() scans from the beginning of the input result set before returning documents, and warns that it becomes slower as the offset increases. PostgreSQL likewise says rows skipped by OFFSET still have to be computed inside the server, so a large offset might be inefficient. See the MongoDB Manual’s cursor.skip() reference and PostgreSQL 18’s LIMIT and OFFSET documentation.
This explains why page 100 can take longer than page 1 even if both return the same number of rows. It is not a universal latency curve: the actual work depends on the database, query plan, indexes, filters, data size, and workload. The documentation establishes the skipped-row work, not a particular slowdown factor or page-depth threshold.
#1 Best Overall
Does MongoDB skip() scan every document?
Not necessarily every document in the collection. MongoDB’s documentation describes scanning from the beginning of the input result set. What counts as that input depends on the query and its execution plan. The practical point is that a large offset can require traversing the preceding results; skip() is not a promise that the database can jump directly to the requested row.
PostgreSQL makes a related point about OFFSET: skipped rows still have to be computed. Neither statement means that every database, query, or index behaves identically, so assess the real query rather than inferring a fixed cost from the API’s page size.
Rank #2
- Used Book in Good Condition
How can pagination create duplicates or missing records?
An incomplete sort makes page boundaries unpredictable
Pagination selects a slice from an ordered result. If the ordering is not unique, rows tied on the sort key can move relative to one another, changing which records fall on each page. MongoDB warns that documents with duplicate sort values may be returned inconsistently across executions, particularly while writes occur, and recommends including a unique value such as _id. PostgreSQL similarly advises using an ORDER BY that constrains rows into a unique order; otherwise, the selected subset is unpredictable. The relevant guidance appears in the MongoDB Manual and PostgreSQL 18 documentation.
For example, sorting only by created_at is not enough if several records share a timestamp. Add a unique tie-breaker, such as id, so the effective order is (created_at, id). The continuation logic must use both values in the same order; validate its behavior for the database’s sort direction, null handling, filters, and indexes.
Writes can shift offset pages between requests
Offset pages are separate queries, not necessarily slices of one permanent result set. If records are inserted, deleted, or otherwise change their position between requests, the next offset can refer to a different slice. A client may then see an item twice or skip one. A unique sort makes each individual query’s order predictable, but it does not by itself freeze the dataset across requests.
Should I use cursor pagination instead of offset pagination?
For deep, sequential traversal, keyset (also called range) pagination is often a better fit. Instead of asking the database to skip a growing prefix, the next request starts from the last-seen ordered key and asks for records beyond or before it. With a suitable index, that range predicate can avoid scanning unwanted rows. MongoDB documents this approach and says range queries typically perform better than skip() as offsets grow.
A simplified pattern for ascending traversal is:
sort by a unique indexed key ascending
filter where key is greater than the last key returned
limit to the requested page size
return the final key as the next positionUse the opposite comparison for descending traversal. For a non-unique primary sort, the continuation condition must account for the tie-breaker too; for an ascending pair (created_at, id), that means continuing after the last timestamp-and-ID pair, not merely after the timestamp. The exact query syntax and index depend on the database and filters.
Outdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchWindows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstallKeyset pagination is naturally sequential: each continuation starts from a prior result. It is therefore less convenient when users need to jump directly to an arbitrary numbered page. Offset pagination supports that page-number experience more directly, though deep offsets can carry more database work.
Best Value
How should I choose a pagination contract?
| Approach | Deep-page work | Jump to a numbered page | What the client sends |
|---|---|---|---|
| Offset | May require the database to process skipped rows; large offsets can be inefficient. | Naturally supports page numbers and direct offsets. | A page number or offset, usually with a page size. |
| Keyset or range | With a suitable index and predicate, can seek from the last-seen key instead of traversing the unwanted prefix. | Less suited to arbitrary jumps; naturally continues from a prior position. | A continuation position, commonly represented by an opaque token. |
Both approaches need a deterministic total order, usually including a unique tie-breaker. For either contract, decide whether results are a moving view or whether the API promises snapshot-like traversal. MongoDB Search documents that its pagination token is not tied to a database snapshot; a token alone should not be treated as a frozen view. Its pagination documentation says this token support applies to clusters running MongoDB 7.0.5 or later. That product-specific behavior should not be generalized to every database cursor or API token.
For APIs that need both direct navigation and efficient “load more” or export flows, a practical design is to allow bounded offsets for shallow pages and use keyset traversal for sequential deep access. This is a design choice, not a vendor rule. Measure representative data with the real filters and indexes before choosing any offset boundary. If using opaque tokens, define their validation, filter binding, expiry, and versioning as part of the API contract.
Quick Recap
What to check when page 100 is slow or pages have gaps
- Inspect the query and execution plan with the production filters and sort, not just an unfiltered test dataset.
- Ensure the sort defines a unique order; add a unique tie-breaker when the visible sort key can repeat.
- Check that the index supports the filter, sort, and keyset continuation predicate actually used.
- For sequential deep traversal, compare an indexed range query from the last-seen key with the offset query.
- Test inserts, deletes, and updates between page requests, and make the API’s moving-view or snapshot expectations explicit.
- Verify the behavior against the deployed database product and version. MongoDB’s cited
skip()page documents themongoshmethod and distinguishes language-specific driver documentation; a framework or driver may translate pagination differently.
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.
The Tool Desk
Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →
