Stop the Full Bucket Scan: Optimizing Couchbase N1QL with Covering Indexes

The Cost of the 'Primary Index' Safety Net

In Couchbase, it is tempting to rely on the Primary Index during development. It acts as a catch-all, ensuring that any N1QL (SQL++) query returns a result regardless of the filters used. However, in a production environment with millions of JSON documents, a Primary Index is a performance liability. It forces the Query Service to perform a full bucket scan, reading every single document to find a handful of matches.

The goal for production-grade performance is to move from Index Scans (finding the keys) to Covering Indexes (finding the data within the index itself), completely bypassing the Data Service fetch phase.

Understanding the 'Fetch' Bottleneck

When you execute a standard Global Secondary Index (GSI), Couchbase performs a two-step process: first, it scans the index to find the document keys that match your criteria; second, it goes to the Data Service to fetch the full JSON document to retrieve the requested fields. This second step, known as the fetch, introduces network latency and disk I/O.

A Covering Index is an index that contains all the fields referenced in the SELECT and WHERE clauses. When an index is "covering," the Query Service has everything it needs within the index entry and skips the fetch step entirely, resulting in sub-millisecond response times for large datasets.

Designing for Selectivity and Order

To build an effective composite index, you must order your fields by selectivity—the degree to which a field narrows down the result set. Fields with high cardinality (like user_id or email) should come before fields with low cardinality (like status or country).

If you index (status, user_id) but your query only filters by user_id, the query planner cannot perform a range scan; it must scan the entire index because the prefix (status) is missing. Always align your index leading keys with your most frequent WHERE clause filters.

Worked Example: From Slow Scan to Covered Query

Consider a dataset of customer orders where we frequently need to find the order date for a specific customer in a specific city.

The Inefficient Approach

Using a basic index on just the customer ID:

CREATE INDEX idx_cust_id ON `orders`(customerId);

Query:

SELECT orderDate FROM `orders` WHERE customerId = "C123" AND city = "New York";

What happens: The engine uses idx_cust_id to find all documents for "C123", but then must fetch every one of those documents from disk to check if the city is "New York" and to retrieve the orderDate.

The Optimized Covering Approach

Create a composite index that includes the filter criteria and the projected result:

CREATE INDEX idx_cust_city_date ON `orders`(customerId, city, orderDate);

What happens: The query planner sees that customerId, city, and orderDate are all present in the index. It returns the orderDate directly from the index memory, bypassing the Data Service entirely.

Verification

To verify this behavior, run the query with the EXPLAIN keyword in the Query Workbench:

EXPLAIN SELECT orderDate FROM `orders` WHERE customerId = "C123" AND city = "New York";

Look for the indexscan operator in the output. If the covers property is true, the query is covered. If you see a fetch operator following the scan, the index is not covering the query.

Trade-offs: The Indexing Tax

While covering indexes accelerate reads, they are not free. Every additional field added to an index increases:

• Memory Usage: GSIs are primarily memory-resident; larger indexes consume more RAM in the Index Service.
• Write Latency: Every time a document is updated, Couchbase must update every index that references the changed fields.
• Disk Space: Large composite indexes increase the storage footprint of the index buckets.

Avoid the "index everything" strategy. Only create covering indexes for the most critical, high-frequency query paths.

Actionable Summary

To optimize your Couchbase queries, follow this diagnostic flow: use EXPLAIN to identify if a fetch is occurring, analyze your WHERE clause for the most selective fields, and expand your GSI to include the SELECT fields to achieve a covered state. If you are dealing with nested arrays, look into ARRAY indexing to avoid application-side filtering.