Couchbase N1QL: Replacing Deep OFFSET with Cursor-Based Keyset Pagination

Deep OFFSET pagination in Couchbase N1QL is easy to write and expensive to run. Every OFFSET 5000 clause forces the query engine to scan and discard 5,000 index entries before it returns the first row of the requested page, so latency grows with page depth. Concurrent writes can also shift rows between pages, producing duplicates or missing documents. The fix is keyset (cursor) pagination: encode the last row's sort key and a unique tiebreaker into a cursor, then issue a bounded range scan with ORDER BY and LIMIT.

The useful takeaway: a correctly designed composite index turns each page request into a short, ordered index scan whose cost stays close to the page size, regardless of how deep the user navigates.

Desired Outcome: Bounded Range Scans Instead of Scan-and-Skip

Keyset pagination replaces LIMIT 50 OFFSET 5000 with a predicate that starts immediately after the last document already returned. Instead of asking for rows 5,001–5,050, the query asks for rows strictly greater than the cursor value. The database seeks directly to that boundary in the index and returns the next N entries. The result is a predictable, bounded scan.

Prerequisites: Index Design and Sort Keys

You need three things before writing the query:

• A monotonic sort key that moves in one direction, such as updated_at or created_at. Avoid volatile fields that change on every update unless you accept the consequences.
• A unique tiebreaker appended to the sort key. If two documents share the same sort value, the tiebreaker (for example, a unique product_id or META().id) guarantees a total order so no row is skipped or repeated.
• A composite index whose keys match the equality filters, then the sort key, then the tiebreaker. Ideally the index also covers every field in the SELECT list so the query never touches the document body.

For example, in a keyspace inventory.catalog.products, suppose you page through active products ordered by updated_at. A partial covering index keeps the index small and avoids fetches:

CREATE INDEX idx_products_cursor
ON `inventory`.`catalog`.`products`(updated_at, product_id, name)
WHERE category = "active";

Run this in the Couchbase Query Workbench, the cbq shell, or through an SDK with a user that has bucket-admin or query-manage privileges. Index builds consume indexer memory and CPU; on large buckets use the online build process or defer the build to a maintenance window. If you need to undo the change, drop the index with DROP INDEX idx_products_cursor ON `inventory`.`catalog`.`products`;.

Querying with a Cursor Boundary

The cursor is an opaque token your application builds from the last row of the previous page. It contains the last updated_at value and the last product_id. The next page uses a lexicographic boundary that respects the composite sort order:

SELECT product_id, name, updated_at
FROM `inventory`.`catalog`.`products`
WHERE category = "active"
  AND (updated_at > $lastUpdated
       OR (updated_at = $lastUpdated AND product_id > $lastId))
ORDER BY updated_at, product_id
LIMIT $pageSize;

Prepare this statement through your SDK rather than concatenating values into the SQL string. Prepared statements let the query service reuse the plan and bind $lastUpdated, $lastId, and $pageSize as parameters. The first page passes an empty cursor and uses a sentinel low value for $lastUpdated and $lastId, or simply omits the boundary predicate.

Verifying the Plan with EXPLAIN

Before deploying, run EXPLAIN on the cursor query and on an equivalent OFFSET query. The cursor plan should show an index scan on idx_products_cursor and an ordered scan that matches the ORDER BY clause. The LIMIT should be applied after ordering. If the plan includes a document fetch operator, the index is not covering; add the missing fields to the index or accept the extra latency.

The OFFSET plan will typically show the same index scan but with a larger number of entries scanned to satisfy the offset. That contrast is the core operational signal: the cursor query scans roughly $pageSize entries, while the OFFSET query scans $pageSize + offset entries.

Operational Checks and Recovery

Bound the page size. A LIMIT of 50–200 keeps response times predictable and prevents a single request from monopolizing indexer resources. Set a statement timeout through your SDK query options so a slow page fails fast rather than hanging the client.

Watch query latency and indexer memory after rollout. Rebalances and failover can move index replicas and change plans, so re-run EXPLAIN after topology changes.

Recovery options depend on the failure mode:

• Timeout or lost response: retry the same page using the last acknowledged cursor. Because the cursor is a boundary, not a page number, the retry is idempotent.
• Invalid or stale cursor: restart from the first page or a shallow OFFSET page. If cursors expire, define a maximum age and treat older cursors as invalid.
• Index missing or rebuilding: pause pagination. Use the supported online index build process, or defer the build, before resuming traffic.

Limitations and Verification Steps

Keyset pagination is not a drop-in replacement for every UI. It cannot jump to an arbitrary page number, so "page 47" links must be redesigned as infinite scroll or explicit cursors. If documents are inserted or deleted between requests, the boundary remains stable, but a mutated sort key can move a document ahead of or behind the cursor. Define the mutation policy: either forbid updated_at changes while paginating, or accept that a moved document may appear on a later page or be skipped.

Default query consistency in Couchbase is not bounded, so a very recent write may not appear immediately. Stronger consistency levels are available but add latency, and the exact option names vary by Couchbase Server and SDK version. Confirm the correct setting for your deployment before relying on read-your-writes behavior.

To validate the implementation, seed a test keyspace with more documents than one page and duplicate updated_at values. Page through with the cursor and assert that the union of returned IDs contains no duplicates and no gaps. Run EXPLAIN for both the cursor and OFFSET variants to confirm the scan difference. Simulate a client crash after page one and resume from the saved cursor; the first returned ID must match the boundary. Finally, mutate a document's sort key between pages and confirm the observed behavior matches your documented policy.

Because index hints, consistency options, and keyspace qualification rules vary across Couchbase Server versions, verify the exact syntax and plan output in your target version before production rollout.