Recommended Free Tools
Large offsets can make a small API response expensive: MongoDB says skip() scans through the input results before returning the requested documents, and PostgreSQL says rows skipped by OFFSET still have to be computed. Unstable ordering creates a separate risk: records can be duplicated or missed between pages. For deep, sequential browsing, an indexed keyset (range) query can avoid traversing the unwanted prefix; for direct jumps to page numbers, offsets may still fit better.
Why is my API pagination slow at page 100?
Page-number pagination commonly turns a page and page size into an offset. For example, page 100 at 20 records per page starts after 1,980 records. The database may need to do work to reach that point even though the response contains only 20 records.
As an Amazon Associate I earn from qualifying purchases.
What MongoDB and PostgreSQL document
MongoDB documents that skip() scans from the beginning of the input result set before it can return the requested documents, and warns that it becomes slower as the offset increases. PostgreSQL 18 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 documentation on LIMIT and OFFSET.
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 reinstallThis explains why page 100 can take longer than page 1: the database may traverse a larger skipped prefix to return the same number of rows. It does not establish a universal latency curve or mean every database, query plan, filter, and index behaves identically. Measure the actual query against representative data and workload before setting a page-depth limit or performance threshold.
#1 Best Overall
- API Design Patterns
- ABIS BOOK
- Manning Publications
Does MongoDB skip() scan every document?
The documented behavior is that MongoDB scans from the beginning of the input result set until it reaches the offset. That is not the same as saying every query scans every document in the collection: the result set and work depend on the query and its plan. The practical concern is that a deeper offset can require traversing more of the matching, ordered results before the page can be returned.
How can pagination return duplicates or miss records?
Pagination returns slices of an ordered result. If the order is not fully specified, the boundaries between slices are not dependable. PostgreSQL warns that without an ORDER BY which constrains rows into a unique order, the selected subset is unpredictable. MongoDB similarly warns that documents with duplicate sort values can be returned inconsistently across executions, particularly while writes occur. Both recommendations point to the same fix: define a deterministic total order, including a unique tie-breaker. See the MongoDB guidance and PostgreSQL guidance.
Rank #2
Make the sort order unique
If records are sorted by a non-unique field such as created_at, add a unique field such as id as a tie-breaker. Conceptually, sort by (created_at, id). The continuation condition must account for both values, and the index, filter combinations, null behavior, and sort directions need to match the database and query. A unique ordering makes page boundaries predictable for a given result set; it does not freeze that result set while clients make separate requests.
Account for writes between requests
Offset pages are separate queries, not automatically successive views of one frozen result. If rows are inserted, deleted, or otherwise change the ordering between requests, later offsets can refer to shifted positions, causing a record to appear twice or not at all. A cursor token alone does not guarantee snapshot consistency: MongoDB Search explicitly says its pagination token is not tied to a database snapshot. If an endpoint promises snapshot-like traversal, it needs a separately implemented and documented consistency mechanism. The Search token guidance applies to MongoDB Search, not every database cursor API; its pagination support is documented for clusters running MongoDB 7.0.5 or later. See MongoDB Search pagination documentation.
Should I use cursor pagination instead of offset pagination?
For deep, sequential traversal, keyset pagination—also called range pagination—uses the last-seen ordered key as the starting point for the next request. With a suitable index, the database can seek from that key rather than traverse the entire unwanted prefix. MongoDB documents this range-query approach as typically performing better than skip() as the offset grows. It is a natural fit for “load more” feeds and sequential exports, but less natural when users must jump directly to an arbitrary numbered page.
| Approach | Deep-page work | Jump to page N | Ordering and consistency |
|---|---|---|---|
| Offset / page number | Skipped rows still require work; large offsets may be inefficient. | Natural fit for direct page jumps. | Needs a deterministic unique order; separate requests can see a changing result set. |
| Keyset / range | A suitable indexed range predicate can avoid scanning the unwanted prefix; MongoDB says it typically performs better than skip() as offsets grow. |
Less suited to arbitrary jumps because continuation starts from a last-seen key. | Also needs a deterministic unique order; a continuation token alone does not promise a snapshot. |
The performance and range-query descriptions above are documented by MongoDB and PostgreSQL; the page-jump trade-off follows from how last-seen-key continuation works.
How do I implement a safer next-page query?
- Choose a total order. Sort by the user-visible ordering field plus a unique tie-breaker, such as
(created_at, id). Use the same ordering for each request. - Match the predicate to the sort. For a simple unique key, request values greater than or less than the last-seen value according to the sort direction. For a two-field order, the continuation predicate must handle both fields and their tie cases; validate it for the database, filters, null behavior, and sort direction.
- Use a supporting index. Build and verify an index appropriate to the actual filter and ordering. The range method avoids the unwanted prefix only when the database can efficiently use the predicate and index.
- Limit the result and retain the continuation key. Return the requested number of records, then use the final record’s ordered key or keys to construct the next continuation value.
- Define the API contract. Offset clients send a page or offset; keyset clients receive and resend a continuation value. Decide explicitly how the API validates tokens, binds them to filters, expires them, and versions them. These are API design decisions, not guarantees provided by a database token.
- Test the behavior under writes. Establish whether clients see a moving result set or a snapshot-like traversal, then implement and document that consistency contract rather than assuming pagination supplies it.
MongoDB’s documented pattern is to sort on a unique indexed field, filter from the last-seen value with $lt or $gt as appropriate, limit the result count, and carry the final key forward. See the MongoDB Manual. A compound ordering such as (created_at, id) applies the same unique-order principle, but requires a continuation predicate that correctly handles ties.
The Tool Desk
Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →When should an API keep offsets?
Offsets remain useful when direct navigation to a numbered page is important or when pages are shallow and bounded. A practical design is to support offsets for that use case and offer keyset traversal for “load more” or export workflows. This is a design choice, not a vendor prescription; benchmark with representative data, production filters, indexes, and query plans before choosing a cutoff. No general percentage, row-count threshold, or latency multiplier follows from the vendor guidance.
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.
