Efficient Pagination in Couchbase Using Covering Indexes and Keyset Seek

Why OFFSET‑based pagination becomes a bottleneck

When a blog‑style article list grows, a simple N1QL query like SELECT * FROM `bucket` WHERE type='article' ORDER BY createdAt LIMIT 10 OFFSET :skip forces the query engine to scan and discard :skip entries before returning the page. As :skip grows, the scan work increases linearly, raising latency and CPU usage. This behavior is predictable but costly for deep pages or high‑traffic sites.

Solution: a covering index plus keyset (seek‑based) pagination

The idea is two‑fold:

1. Create a covering index that contains every field needed by the query (the filter, sort, and selected columns). The index can satisfy the query without fetching the full document.
2. Replace OFFSET with a keyset seek: remember the last sort value from the previous page and use it as a lower bound for the next page (WHERE createdAt > :lastSeen). This avoids scanning earlier entries and yields stable results even when concurrent writes occur.

Building the covering index

Assume articles are stored as JSON documents with fields type, createdAt (ISO‑8601 timestamp), and title. The following index covers a typical list query that returns title and createdAt:

CREATE INDEX idx_article_cover
ON `bucket`(type, createdAt, title)
WHERE type='article'
USING GSI;

Required permissions: Query Manage Index to create the index and Query Execute to run N1QL statements. The index consumes additional RAM and disk; each write to an article document must also update this index, which can affect write throughput.

Verifying the index is used

Run the EXPLAIN statement on a sample LIMIT/OFFSET query:

EXPLAIN SELECT title, createdAt
FROM `bucket`
WHERE type='article'
ORDER BY createdAt
LIMIT 10 OFFSET 0;

Look for an IndexScan step that references idx_article_cover and ensure there is no Fetch step. The absence of a Fetch indicates the index is covering.

Keyset pagination query

For the first page, request the earliest articles:

SELECT title, createdAt
FROM `bucket`
WHERE type='article'
ORDER BY createdAt
LIMIT 10;

Assume the last returned createdAt value is 2026-09-15T08:30:00Z. The next page uses that value as a seek point:

SELECT title, createdAt
FROM `bucket`
WHERE type='article'
  AND createdAt > "2026-09-15T08:30:00Z"
ORDER BY createdAt
LIMIT 10;

Again, run EXPLAIN to confirm the same covering index is used and no Fetch appears.

Trade‑offs and limitations

• Index maintenance cost: Every insert, update, or delete on an article document triggers an index write. Heavy write workloads may see increased latency.
• Storage overhead: The covering index duplicates the indexed fields; monitor bucket size and adjust the index definition if only a subset of fields is needed.
• Keyset complexity: The client must store and pass the last sort value securely. If the sort column is not unique, add a tie‑breaker (e.g., document key) to avoid duplicates or gaps.
• Non‑random access: Keyset pagination does not support jumping directly to page N without iterating through previous pages. If random access is truly required, a hybrid approach (limited OFFSET for shallow pages) may be necessary.

Actionable checklist

1. Identify the fields used in your list query (filter, sort, selected columns).
2. Create a covering index that includes those fields, adding a WHERE clause to limit it to the relevant document type.
3. Validate the index with EXPLAIN; confirm an IndexScan without a Fetch step.
4. Implement keyset pagination in your application layer, storing the last sort value (and tie‑breaker if needed).
5. Benchmark: measure latency for increasing OFFSET values (e.g., 0, 10 k, 50 k) and compare with the keyset query using the same LIMIT. Observe the growth curve for OFFSET versus the flat latency of keyset.
6. Monitor index size and write throughput; adjust or drop the index if write impact outweighs read gains.

By pairing a covering index with a seek‑based pagination pattern, you replace linear scan costs with constant‑time index lookups, delivering faster, more predictable response times for article lists as your data set grows.