Couchbase N1QL OFFSET pagination for large datasets with non-unique sort keys

Answer

Determinism: OFFSET pagination cannot be made stable with a non-unique ORDER BY key alone in current N1QL. Rows with equal sort values can be returned in different relative order across executions, so page boundaries drift with inserts, updates or deletes.

Performance threshold: No official hard OFFSET limit is published for Couchbase Server 7.x+. Performance degrades with offset depth because the engine must scan and discard preceding rows. Couchbase guidance favors keyset pagination for deep paging rather than growing OFFSET.

Index requirement: Efficient paging requires an index whose key prefix matches the ORDER BY columns in the same order, with WHERE predicates covered when possible. For keyset pagination the index must include the sort columns plus the unique tie-breaker used in the continuation filter.

Confirmed facts

• N1QL LIMIT OFFSET does not guarantee stable page boundaries when ORDER BY uses a non-unique sort key. Ties are non-deterministic.
• Deterministic ordering is achieved by making the ORDER BY total, e.g. ORDER BY sort_key, META().id. Adding a unique tie-breaker removes non-determinism for ties. This is a general ordering property, not an OFFSET-specific feature.
• Efficient paging requires an index with a key prefix that matches the ORDER BY columns in the same order. Filter predicates should be covered when possible for index-only execution.

Likely explanation

• Large OFFSET values require scanning and discarding preceding rows, so latency and resource use tend to increase with offset depth. The exact curve depends on data volume, cluster load and query shape.
• Planner and index behavior is version-sensitive across Couchbase Server 7.x and later.

Steps needed for this case

1. Make ordering total.

Change the query to include a unique tie-breaker:

SELECT ... FROM bucket
WHERE ...
ORDER BY sort_key ASC, META().id ASC
LIMIT 20 OFFSET ?

For keyset pagination replace OFFSET with a continuation filter:

SELECT ... FROM bucket
WHERE ... AND (sort_key > $last_sort_key OR sort_key = $last_sort_key AND META().id > $last_id)
ORDER BY sort_key ASC, META().id ASC
LIMIT 20

2. Ensure index prefix matches ORDER BY.

Create or verify an index that starts with the sort columns in the same order as ORDER BY, followed by the tie-breaker:

CREATE INDEX ix_paging ON bucket(sort_key ASC, META().id ASC)
WHERE ...

If filters are used, include them before the sort prefix or make the index covering.

3. Verify plan and stability.
• Run EXPLAIN on the paginated query to confirm index usage and that ORDER BY is satisfied by the index prefix.
• Compare latency for increasing OFFSET vs keyset filter on the same index.

One missing diagnostic detail that changes the recommendation: the exact Couchbase Server version and the current index definition used for the query. Planner behavior and covering index support are version-sensitive, and the index must be rebuilt if META().id is added to ORDER BY.