Optimizing Couchbase N1QL Queries with Composite Global Secondary Indexes

The problem of drifting query performance

Couchbase N1QL embraces a SQL-like syntax for JSON documents, but as a document-oriented database the default query path can shift unexpectedly. When a filter on a non-key field grows more selective, the query service may fall back to a full collection scan, especially if the only available index is the primary key. On a cluster holding millions of documents, that fallback translates into seconds-long latency, increased CPU, and downstream application timeouts.

Why the default index often falls short

The primary index on the document key guarantees fast lookups by key, but it does not help with arbitrary WHERE clauses. Couchbase's Global Secondary Index (GSI) service extends queryability, yet a single-field GSI only covers predicates that match its leading key. Composite conditions, ranges on multiple attributes, or OR-combined filters often slip past a solitary index, forcing the query planner into a scan.

Building a composite GSI the practical way

Creating a composite GSI is straightforward when the query pattern is known. Suppose you frequently run a N1QL statement like:

SELECT * FROM `travel-sample` WHERE city = $1 AND type = $2;

To support this, a composite GSI on (city, type) eliminates the scan. Run the index creation from any cluster node where the cbq shell or the REST API is available. The command requires cluster-admin privileges or at least the index-manage role.

cbq> CREATE INDEX idx_city_type ON `travel-sample`(city, type) WITH {"deferred": false};

After the index finishes building, the diagnostic step is where EXPLAIN earns its keep. Prefixing any N1QL query with EXPLAIN reveals the planned operation:

cbq> EXPLAIN SELECT * FROM `travel-sample` WHERE city = "Seattle" AND type = "airport";

In the plan output, look for IndexScan keyed against idx_city_type instead of CollectionScan. The former indicates the GSI is being used; the latter means the optimizer reverted to a full scan, typically because the index build is still in progress or the predicate does not align with the composite key order.

Relevant risks to note: the WITH {"deferred": false} flag builds the index immediately, which consumes RAM proportional to the document volume. On a production cluster, this may trigger rebalancing or temporarily increase query latency. If you cannot afford a blocking build, omit the flag and rebuild during a maintenance window, then re-run EXPLAIN to confirm index usage.

Trade-offs and when to stop

Composite indexes accelerate targeted filters, but they are not a free lunch. Each additional indexed field increases the index size and the cost of write operations, because every document mutation must update the index structure. When your query workload mixes OR conditions, leading-range predicates, or joins across unrelated collections, a composite GSI may provide diminishing returns and a memory-optimized index may require re-configuration once document volumes exceed allocated RAM.

Couchbase cautions that N1QL JOIN operations can be expensive on large document sets without proper index coverage. If your pattern involves cross-collection lookups, consider the analytics service or materialized view patterns instead of forcing a GSI to cover every join.

Verification checklist

1. Run the EXPLAIN-prefixed query and confirm IndexScan appears on the intended GSI.
2. Monitor the Query metrics tab in the Couchbase Web Console; latency should drop for the targeted predicate.
3. Use cbbench with a representative data set to benchmark before and after, noting any change in ops-per-second and average response time.
4. Check cluster-level memory usage; if the index pushes RAM close to its limit, consider scaling the node or revisiting the indexed fields.

If any step shows no improvement, revisit the predicate order in the composite key or evaluate whether a separate single-field index better matches the most common query path.

Closing

A well-designed composite GSI can turn a sluggish N1QL query into a predictable, low-latency operation without touching application code. The key is to align the index key order with the frequent WHERE clause order, verify with EXPLAIN before declaring victory, and keep an eye on the write-path cost and memory footprint. Add the index to your regular query-performance review cycle, and you'll keep Couchbase queries fast as your dataset grows.