Keyset pagination with compound sort keys
Parent: MongoDB Query Shape Optimization: Sort, Pagination, Operators and Sharded Targeting · Published reference · snapshot 2026-10-21 · skill ranked-b37a4eca
↓ Facts as markdownall context files
Source-anchored research for Keyset pagination with compound sort keys. Run status: COMPLETED.
These notes link each claim to its source. Some citations identify archived research reports that are no longer hosted on this site. A published reference means the content is available; it does not certify independent review or accuracy.Read the editorial policy and follow the sources before relying on a claim.
Definitions
- Although _id is a useful unique tie-breaker, an ObjectId is not a strict clock: MongoDB notes that ObjectIds have one-second timestamp resolution and client clocks can differ. For chronological feeds, sort on an explicit timestamp followed by _id. (medium confidence) [source]
How it works
- Keyset paging is less affected than offset paging by inserts or deletes before the current boundary because it anchors to values, but it is not snapshot-consistent. Concurrent changes or updates to mutable sort fields can make items appear twice or be missed. Prefer immutable sort fields and a unique tie-breaker when walking a changing collection. (medium confidence) [source]
How-to and procedures
- Use a compound index whose sort-key fields appear in the same order, with directions matching the index pattern or its full inverse. Equality-prefix fields can precede those keys only when the query constrains each prefix field by equality. Inspect explain as well: a multi-key $or boundary can prevent one index from efficiently serving both the range filter and requested sort in some query plans. (high confidence) [source]
Problems, failure modes and limitations
- A compound lexicographic $or can still yield a poor plan. In one MongoDB case study, a find() predicate using a > cursorA OR (a == cursorA AND b > cursorB) with sort({a: 1, b: 1}) did not efficiently combine index filtering with ordered retrieval; the author used a staged aggregation to position near the cursor before applying the full boundary. This case report does not establish a general MongoDB limitation. (low confidence) [source]
Facts and statements
- Treat the sort tuple as the cursor position. For sort {createdAt: -1, _id: -1}, the next-page boundary is createdAt < lastCreatedAt OR (createdAt == lastCreatedAt AND _id < lastId). For more keys, compare the first differing key after matching preceding keys for equality, using the comparison direction that matches that key's sort direction. (high confidence) [source]
- Sort fields that can tie need a unique final key to define a repeatable page boundary, often _id. MongoDB documents that tied sort keys can be returned in any relative order; MongoDB examples and implementations add _id after a timestamp or other paginated field. The unique tuple defines a deterministic order for a fixed dataset, but it does not create a snapshot across requests. (high confidence) [source]
- An indexed keyset range boundary avoids walking the prefix that skip() must pass, so it typically scales better for deep sequential pages. It does not guarantee constant latency or a universal O(log n) page cost: filters and the selected plan still matter, and a literal multi-key $or form may filter or sort inefficiently. (high confidence) [source]
Children
- No children recorded.