Choosing the Right N1QL Index in Couchbase for Faster Queries

Problem: Queries Scan Whole Buckets and Slow Down

When a N1QL query lacks a matching secondary index, Couchbase falls back to a primary scan, reading every document in the bucket. This works fine in a small dev set but becomes a bottleneck as data grows, causing high latency and unnecessary load on the Data Service.

Thesis: A well‑designed covering secondary index lets the query engine return results directly from the index, eliminating document fetches and reducing resource usage.

Understanding N1QL Index Types

Couchbase offers two main index kinds:

• Primary index: built on the document key; useful for ad‑hoc exploration but inefficient for production workloads.
• Global Secondary Index (GSI): created on one or more fields; enables index‑only scans when the index contains all fields needed by the query (a covering index).

The order of fields in the index key matters. The query optimizer can use the index only if the WHERE predicates match the leading fields in the same sequence.

Worked Example: Building a Covering Index for Route Queries

Assume a travel-sample bucket with documents of type route that contain fields airline, sourceairport, destinationairport, and stops. A common query looks for routes operated by a specific airline with zero stops:

SELECT airline, sourceairport, destinationairport
FROM `travel-sample`
WHERE type = 'route'
  AND airline = 'AA'
  AND stops = 0;

To serve this query from the index alone, create a GSI that includes the filter fields and the selected fields:

CREATE INDEX idx_route_covering
ON `travel-sample`(type, airline, stops, sourceairport, destinationairport)
WHERE type = 'route';

After creation, verify the plan with EXPLAIN:

EXPLAIN SELECT airline, sourceairport, destinationairport
FROM `travel-sample`
WHERE type = 'route'
  AND airline = 'AA'
  AND stops = 0;

Look for an IndexScan node whose covering property is true. If the plan shows a Fetch operation, the index is not covering and you may need to add the missing fields to the index key or include them via the INCLUDE clause (Couchbase 7.0+).

Trade‑offs and Limitations

While covering indexes boost read performance, they introduce costs:

• Storage overhead: each index entry duplicates the indexed fields.
• Write latency: every mutation (insert, update, delete) must update all matching GSIs.
• Index build impact: large indexes consume CPU and I/O during creation; schedule builds during low‑traffic windows or use the defer_build option.

Over‑indexing can degrade overall throughput. Monitor the Index Service via the Couchbase Web Console (Metrics → Index Service) during peak loads to spot rising latency or saturation.

Actionable Closing

1. Identify your most frequent query patterns and the fields they filter or return.
2. Create a secondary index that leads with the filter fields in the exact order used in the WHERE clause and includes any selected fields to achieve coverage.
3. Validate with EXPLAIN; ensure the plan shows an IndexScan with covering:true and no Fetch.
4. Set up alerts on Index Service CPU, disk I/O, and write latency to catch over‑indexing early.
5. Periodically review and drop unused indexes (DROP INDEX idx_name ON bucket;) to keep storage and write costs in check.