Choosing a Pagination Strategy for High-Concurrency Couchbase Catalog Reads

The Pagination Decision

When building a product catalog with tens of millions of documents, the primary challenge is maintaining a p95 latency under 50ms as users navigate deep into result sets. In Couchbase N1QL, the standard LIMIT/OFFSET approach creates a linear performance degradation because the index must scan and discard all preceding rows to reach the requested offset.

To achieve bounded latency and minimal index memory pressure, you must choose between three primary patterns based on your requirements for random access versus sequential performance.

Comparison of Pagination Patterns

Pattern	N1QL Implementation	Cost Behavior	Random Access
LIMIT/OFFSET	OFFSET $offset LIMIT $page_size	Linear: O(offset + page_size)	Supported (Jump to page X)
Keyset Pagination	WHERE (sort_key, id) > ($last_val, $last_id)	Constant: O(page_size)	Sequential only (Next/Prev)
FTS Bookmark	Search Service Query + Bookmark	Efficient deep paging for relevance	Bookmark-based

Trade-offs and Engineering Constraints

LIMIT/OFFSET is the simplest to implement but is dangerous for high-concurrency workloads. As the offset increases, CPU and memory pressure on index nodes rise, often leading to query timeouts on deep pages. It is only suitable for small datasets or interfaces where users rarely go beyond the first few pages.

Keyset Pagination (also known as the Seek Method) provides predictable latency regardless of page depth. It is resilient to inserts; if a new document is added to page 1, it does not shift the results of page 2. The trade-off is the loss of arbitrary page jumping and the requirement for the client to track and return the last seen value (the cursor).

Full-Text Search (FTS) pagination is optimized for relevance ranking. While efficient for deep paging, it introduces a dependency on the Search Service and a different consistency model than N1QL. It is not a replacement for strict chronological or alphabetical catalog listings.

Implementing Keyset Pagination

To implement this effectively, you need a covering Global Secondary Index (GSI). A covering index allows the query to be satisfied entirely from the index without fetching the full document from the Data Service, significantly reducing latency.

1. Create the Covering Index

Run the following command in the Query Workbench or cbq shell. This requires index build permissions.

CREATE INDEX idx_catalog_tenant_sort 
ON `catalog`(tenant_id, updated_at DESC, meta().id ASC) 
WHERE type = 'product'

2. Execute the Seek Query

Use a composite key consisting of the sort field (updated_at) and the document ID to ensure a unique, stable order. This prevents rows from being skipped or duplicated if multiple documents share the same timestamp.

SELECT meta().id, name, price
FROM `catalog`
WHERE tenant_id = $t
  AND type = 'product'
  AND (updated_at, meta().id) > ($last_ts, $last_id)
ORDER BY updated_at DESC, meta().id ASC
LIMIT $page_size

Implementation Detail: The client must store the updated_at and id of the last item on the current page. For the initial request, use a sentinel value (e.g., a future date for DESC sorts) to trigger the first page.

Validation and Operational Limits

To verify the implementation, run an EXPLAIN plan on the query. Confirm that the index_scan operator shows a range_scan rather than a full index scan. This ensures the query is seeking directly to the cursor.

Performance Check: Measure p95 latency across three depths: page 1, page 100, and page 1,000. In a correct keyset implementation, latency should remain nearly flat. If latency increases linearly, the index is not being used for seeking.

Operational Risks:

• Mutable Sort Keys: Avoid using fields like price as the primary sort key. If a price changes while a user is paginating, the document may move to a different page, causing it to appear twice or disappear entirely.
• Memory Footprint: Covering indexes increase the memory usage of the Index Service. Monitor the index_resident_set metric to ensure the index does not swap to disk.
• Tenant Isolation: Always include tenant_id as the leading key in the index and the WHERE clause to prevent cross-tenant data leakage and ensure efficient partitioning.