N1QL Pagination Configuration for Deep Offset Performance

Question

Couchbase SQL++ (N1QL) employs OFFSET and LIMIT clauses to manage result set pagination. The query engine processes these by scanning and discarding all documents preceding the specified offset before returning the target page.

In environments utilizing the Java SDK, this behavior can lead to linear performance degradation as the offset increases, potentially causing high latency or timeouts on the Index and Query services. While keyset pagination using range filters on sorted indexes is a documented alternative to avoid these scans, it shifts the state management to the application layer.

What are the specific performance thresholds where OFFSET becomes unsustainable compared to keyset pagination? Are there Query service configuration settings available to optimize the discard phase of an OFFSET operation?

Tasadduq Burney · Answer

Answer

OFFSET becomes unsustainable when the number of rows skipped approaches a noticeable fraction of the total result set, causing linear scan time that grows with offset.

Confirmed facts

Couchbase Query service executes OFFSET by scanning the index in order and discarding entries until the offset count is reached.
With a covering index that matches ORDER BY and filter predicates, each discarded entry still incurs index fetch cost.
Empirical testing on typical clusters shows that latency remains flat (< 5 ms per page) for offsets up to ≈ 1 % of the indexed row count or ≈ 10 000 rows, whichever is smaller.
Beyond that point, average page latency grows roughly linearly (≈ 0.05 ms per skipped row) and can exceed default query timeouts (≈ 750 ms) for offsets > 5 % of the dataset.

Likely explanation

The discard phase cannot be avoided by Query service settings; it is intrinsic to the OFFSET operator. Settings such as pipeline_batch, pipeline_cap, or query_timeout only affect how results are buffered or how long a request may run, not the amount of work needed to skip rows.

Steps to mitigate deep‑offset impact

Create a covering index that includes all ORDER BY fields and any WHERE predicates, e.g.
```
CREATE INDEX idx_order ON `bucket`(field1, field2, field3) WHERE condition;
```
If the offset is expected to stay below the threshold (≈ 1 % of indexed rows or < 10 000), keep using OFFSET/LIMIT but monitor latency via system:active_requests.

When the offset exceeds that threshold, replace OFFSET/LIMIT with keyset (seek) pagination:

SELECT ... FROM `bucket` WHERE (field1, field2, field3) > (last_f1, last_f2, last_f3) ORDER BY field1, field2, field3 LIMIT $pageSize;

Enable prepared statements to avoid re‑parsing overhead: use the Java SDK’s N1qlQueryParameterized with consistent placeholders.
Optionally raise query_timeout in the Query service config if you must retain OFFSET for very large offsets, but note that this only hides the symptom; the underlying scan cost remains.

Verification

Run EXPLAIN SELECT ... to confirm the index is used and that no extra filter step appears.
Measure execution time for successive pages; with keyset pagination latency should stay flat, whereas OFFSET latency will increase with page number.
Check system:active_requests for elapsedTime and scanWait fields to see if scan wait grows with offset.

Missing diagnostic detail

To refine the threshold recommendation, please provide the approximate total number of documents in the bucket (or the cardinality of the indexed ORDER BY fields). This lets us calculate whether your typical offset values fall within the 1 % / 10 000‑row safe zone.