Couchbase N1QL Pagination: Consistency Issues with Concurrent Updates

Direct answer

No — keyset pagination by itself does not give you a snapshot-consistent view. Each page is a separate query execution, so documents inserted, updated, or deleted between page requests can cause duplicates or gaps. To get a stable view, you must explicitly request a stronger scan consistency level.

What is confirmed vs. likely

Confirmed: Couchbase N1QL supports a scan_consistency setting per query. Setting it to request_plus makes the query wait for the index to catch up so all documents visible at the start of the request are returned. This is a documented, per-request mechanism, and it applies equally to LIMIT/OFFSET and keyset pagination — the consistency semantics are identical for both pagination styles once scan_consistency is set.

Likely but worth verifying: The claim that snapshot consistency can be forced via a WITH SCAN CONSISTENCY clause inside the query text is uncertain. In Couchbase 7.x, scan_consistency is set as a query parameter (e.g., in the SDK options or REST API), not as a WITH clause. Treat the WITH-clause syntax as unverified and confirm against the 7.x documentation before use.

Steps for this case

1. Create an index on the sort key used for keyset pagination (e.g., CREATE INDEX idx ON bucket (sortKey)).
2. Run a paginated query with the keyset condition and set scan_consistency to request_plus: SELECT * FROM bucket WHERE sortKey > $lastKey ORDER BY sortKey LIMIT 10 with the query option scan_consistency = request_plus.
3. Insert or update documents that would appear between pages while the client is paginating; verify that duplicates or missing rows do not occur.
4. Repeat the same test without the consistency setting or with not_bounded to confirm that inconsistencies appear.

Practical verification

Use a test bucket with concurrent writers and a client that paginates with keyset pagination. Observe that with request_plus, pages remain stable; with not_bounded, duplicates or gaps appear. If the sort key is mutable, keyset pagination can still skip or duplicate rows even with request_plus, because the keyset value may change between page requests. Use an immutable sort key (e.g., a monotonic sequence or creation timestamp) to avoid this.

Caveats

• request_plus can increase query latency because the cluster must ensure a consistent view before returning results.
• Keyset pagination can still produce duplicates or gaps if the sort key is updated or deleted between page requests, unless the keyset value is immutable.
• If an index on the sort key is missing, keyset pagination will fall back to a full scan, negating its performance advantage.
• The snapshot guarantee applies only to the specific query instance; subsequent page requests must reuse the same scan_consistency value to maintain consistency.