Optimizing Couchbase Query Performance with Targeted GSI Indexing

Problem: Primary Index Scans Cause High Latency

Many developers start with a Primary Index in Couchbase because it allows any N1QL (SQL++) query to run immediately. However, a Primary Index forces the Query Service to scan every document in a bucket to find matches. In production with millions of documents this results in high latency, CPU spikes, and potential timeouts.

Prerequisites

• A running Couchbase Server cluster (Version 6.5+ recommended).
• The Index Service enabled on at least one node in the cluster.
• A bucket populated with JSON documents.
• Administrative permissions to execute N1QL statements via the Query Workbench.

Procedure

1. Create a Secondary Index

Run this command in the Query Workbench to index a specific field (e.g., user_id) within a bucket named users:

CREATE INDEX idx_user_id ON `users`(user_id);

Risk: Creating indexes on very large buckets can consume significant RAM and CPU. If you are loading a massive dataset for the first time, use the WITH deferred clause to prevent the index from building until the data load is complete, then call BUILD INDEX manually.

2. Create a Covering Index

Scenario: You frequently query for a user's email and status based on their ID.

Non‑Covering Index:

CREATE INDEX idx_user_id ON `users`(user_id);
-- This query still requires a Fetch for email and status
SELECT email, status FROM `users` WHERE user_id = "123";

Covering Index:

CREATE INDEX idx_user_lookup ON `users`(user_id, email, status);
-- This query is "covered"; no Data Service fetch is required
SELECT email, status FROM `users` WHERE user_id = "123";

Verification

Use the EXPLAIN keyword to see the execution plan:

EXPLAIN SELECT email, status FROM `users` WHERE user_id = "123";

• Index Scan: Look for indexScan in the plan. If you see primaryScan, the query is ignoring your secondary index.
• Fetch: If the query is covered, the fetch operator will be absent from the plan.
• Cost: Compare the cost value of the explain plan before and after adding the covering index.

Performance Trade‑offs

Index Type	Read Performance	Write Impact	Memory Usage
Primary	Very Low (Full Scan)	Low	Low
Secondary	High (Targeted)	Moderate	Moderate
Covering	Highest (No Fetch)	Higher	High

Warning: Over‑indexing can degrade write performance. Every time a document is updated, Couchbase must update every corresponding index entry. Avoid indexing fields with extremely high cardinality (like unique timestamps) unless absolutely necessary.

Rollback and Cleanup

If an index is consuming too much memory or slowing down writes, you can remove it without affecting the underlying data documents:

DROP INDEX `users`.idx_user_lookup;

To verify the index is gone, run SHOW INDEXES ON `users`; in the Query Workbench.