Optimizing Couchbase Query Performance with Global Secondary Indexes (GSI)

The Cost of Primary Scans

When executing a N1QL (SQL++) query in Couchbase without a specific index, the engine performs a primary scan. This requires the query service to fetch every document in the bucket to check if it matches the filter criteria. On large datasets, this results in high latency, excessive CPU usage, and potential timeouts.

The solution is the Global Secondary Index (GSI). GSIs decouple the indexing process from the data storage, allowing a dedicated Index Service to maintain a sorted map of specific document attributes. This allows the query engine to jump directly to the relevant documents rather than scanning the entire dataset.

Prerequisites

• A running Couchbase Server cluster (Version 6.5+ recommended).
• The Index Service must be enabled and assigned to at least one node in the cluster.
• Administrative permissions to execute CREATE INDEX statements on the target bucket and scope.
• A defined bucket containing JSON documents with consistent attribute naming.

Implementing Optimized Indexes

1. Creating a Basic Secondary Index

Use a basic index when you frequently filter by a single attribute. For example, if you have a users bucket and frequently query by email, a primary scan is inefficient.

-- Run this in the Couchbase Query Workbench
CREATE INDEX idx_user_email ON `users`(email);

Risk: Creating indexes on very low-cardinality fields (fields with very few unique values, such as gender or active_status) provides minimal performance gain and consumes unnecessary memory.

2. Implementing Composite Indexes for Complex Filters

When queries filter by multiple fields, a composite index is more efficient than multiple single-field indexes. The order of fields in the index must match the order of the filter criteria for maximum efficiency.

-- Optimizing for queries filtering by both 'status' and 'last_login'
CREATE INDEX idx_user_status_login ON `users`(status, last_login);

3. Creating Covering Indexes to Eliminate Data Fetches

A Covering Index is an index that contains all the fields requested in the SELECT clause. When an index covers a query, the Index Service returns the result directly without needing to contact the Data Service to fetch the full document, significantly reducing latency.

Example Scenario: You need to retrieve only the username for users in a specific city.

-- The index includes both the filter field (city) and the result field (username)
CREATE INDEX idx_user_city_username ON `users`(city, username);

-- This query is now 'covered'
SELECT username FROM `users` WHERE city = 'New York';

Verifying Index Utilization

Creating an index does not guarantee the query optimizer will use it. You must verify the execution plan using the EXPLAIN keyword.

Run the following command in the Query Workbench:

EXPLAIN SELECT username FROM `users` WHERE city = 'New York';

What to check in the output:

• Look for the index property. It should list idx_user_city_username.
• Ensure the op (operation) is IndexScan and not PrimaryScan.
• Check for the covers flag; if true, the query is fully covered and no document fetch is occurring.

Resource Management and Rollback

Performance Trade-offs

Index Type	Read Impact	Write Impact	Memory Usage
None (Primary Scan)	Very High (Slow)	None	Low
Single Field GSI	Low (Fast)	Low	Moderate
Composite/Covering	Very Low (Fastest)	Moderate	High

Rollback Procedure

If you observe a significant spike in Index Service memory usage or a drop in write throughput (due to index maintenance overhead), remove the index to restore performance.

-- Run in Query Workbench
DROP INDEX `users`.idx_user_city_username;

Practical Limitations

• Memory Pressure: GSIs are stored in memory for performance. If the index size exceeds the allocated Index Service RAM, Couchbase will swap to disk, increasing latency.
• Write Amplification: Every time a document is updated, all associated GSIs must be updated. Avoid indexing every field in a document.
• Index Build Time: Creating an index on a multi-terabyte bucket can take significant time and resource overhead; perform these operations during maintenance windows.