Question
Pagination Limits: Handling Duplicate Sort Values in search_after
Lax Byte
0 reputation · 26 Jul 2023, 13:56 UTC
19.4K views1
Loading…
0 reputation · 26 Jul 2023, 13:56 UTC
Goal: Ensure continuous pagination using search_after when the primary sort field may contain duplicate values across documents, without encountering errors that halt scrolling.
Constraint/uncertainty: Documentation states that the sort field must be unique per document; duplicates cause an error, but it is unclear whether Elasticsearch automatically incorporates a tie‑breaker such as _doc or if the user must explicitly add a secondary sort field to guarantee uniqueness. This ambiguity affects version‑specific behavior and the reliability of deep pagination.
Does Elasticsearch throw a specific error (e.g., IllegalArgumentException) when search_after is used with a non‑unique sort field and no tie‑breaker is provided, and does this behavior differ between versions 7.x and 8.x?
If an error is thrown, what is the exact error message and HTTP status code returned by the REST API?
Can adding _doc as a secondary sort field reliably prevent the error and guarantee a stable cursor, or are there cases where additional tie‑breakers are still required?
32275 reputation · 26 Jul 2023, 15:15 UTC
Elasticsearch returns a 400 Bad Request with the message search_after expects the sort values to be unique per document when the sort array supplied to search_after contains duplicate values for all of its fields. The error is identical in both the 7.x and 8.x series.
elasticsearch{
"error": {
"root_cause": [
{
"type": "illegal_argument_exception",
"reason": "search_after expects the sort values to be unique per document"
}
],
"type": "search_phase_execution_exception",
"reason": "all shards failed"
},
"status": 400
}
The search_after mechanism relies on a deterministic ordering of documents. If two documents share the same values for every field in the sort array, Elasticsearch cannot decide which document comes next, which leads to ambiguous cursors and the 400 error.
_id or a unique numeric column) as the final sort criterion resolves the error._doc as a tie‑breaker works for a single index refresh but can change after segment merges, so it is not stable for long‑lived pagination.sort clause with a field that is guaranteed unique per document. Place it last.
GET /my-index/_search
{
"size": 10,
"query": { "match_all": {} },
"sort": [
{ "created_at": { "order": "asc" } },
{ "_id": { "order": "asc" } }
]
}
search_after array.
GET /my-index/_search
{
"size": 10,
"query": { "match_all": {} },
"search_after": [ "2026-09-30T12:34:56Z", "AbCdEfGhIjKlMnOpQrSt" ],
"sort": [
{ "created_at": { "order": "asc" } },
{ "_id": { "order": "asc" } }
]
}
_doc as a tie‑breaker, be aware that its value can change after a segment merge or refresh. In that case, the cursor may become invalid.Will your pagination span multiple index refreshes or merges? If yes, prefer an immutable field such as _id or a dedicated unique column; if you only paginate within a single refresh, _doc is acceptable.
Use comments to ask for clarification. Post a solution as an answer.
32,275 reputation · 27 Jul 2023, 01:02 UTC
While adding a tie-breaker resolves the ambiguity of the cursor, the choice between _doc and _id involves a critical trade-off between performance and stability.
_doc: This is the most efficient tie-breaker because it sorts by the internal Lucene document ID. However, it is not stable across segment merges. If a merge occurs between pagination requests, the internal ID changes, which can cause documents to be skipped or duplicated in the result set._id: Sorting by the document's unique ID is globally stable and persists across merges. The trade-off is a higher performance cost, as _id is not stored in the same optimized way as _doc for sorting.For short-lived sessions where speed is priority, _doc is often sufficient. For long-lived cursors or critical data exports, _id is the safer requirement to ensure a deterministic sequence.