Diagnosing and Fixing Couchbase N1QL Index Rebuild Failures

Recognizable Condition

N1QL queries return errors such as index not found, stale index, or index build failed. Running SELECT * FROM system:indexes WHERE keyspace="bucket_name"; shows index_state stuck at rebuilding or error for one or more indexes.

Cause and Diagnostic Quick Reference

Ordered Diagnostic Checks

1. Confirm index state. Run cbq -e "SELECT name, index_state, state_msg FROM system:indexes WHERE keyspace='bucket_name';". Note any index not in online state.
2. Check indexer memory pressure. Execute cbstats localhost:11210 -b bucket_name index | grep -E 'mem_used|mem_quota|num_docs_pending'. If mem_used approaches mem_quota, the indexer cannot allocate rebuild buffers.
3. Inspect indexer logs. Tail /opt/couchbase/var/log/couchbase/indexer.log for lines containing ERROR, rebuild, or corrupt. Correlate timestamps with the failed query.
4. Verify bucket RAM quota and index count. In the Couchbase Web Console, navigate to Buckets → bucket_name → Indexes. Count total indexes and compare to the bucket’s RAM quota. A rule of thumb: each secondary index consumes roughly 1–2 % of bucket RAM per million documents.
5. Check for concurrent rebalance or XDCR. Run couchbase-cli rebalance-status -c localhost:8091 -u admin -p password and review XDCR replication status. Active rebalance or heavy XDCR can starve the indexer of CPU and disk I/O.

Fixes Mapped to Findings

Memory Pressure

If mem_used > 80 % of mem_quota:

• Increase the bucket RAM quota (requires cluster restart if using Enterprise Edition) or add a node with index service enabled.
• Reduce index count: drop unused indexes with DROP INDEX bucket_name.index_name;.
• Consider enabling index partitioning (Enterprise) to spread index memory across nodes.

Corrupted Index Entries

When a single index shows error and logs mention corruption:

ALTER INDEX bucket_name.index_name REBUILD;

If multiple indexes are affected, rebuild all:

ALTER INDEX ALL ON bucket_name REBUILD;

Monitor system:indexes until index_state returns to online.

Version-Specific Rebuild Stall (Couchbase 6.5.x, 7.0.x)

Apply the relevant patch release (e.g., 6.5.3+, 7.0.2+). If immediate upgrade is not possible, workaround by dropping and recreating the affected index:

DROP INDEX bucket_name.idx_name;
CREATE INDEX idx_name ON bucket_name(field1, field2);

Verify the new index reaches online state.

Rebuild During Rebalance

Pause index rebuilds until rebalance completes:

cbq -e "ALTER INDEX ALL ON bucket_name PAUSE;";
# wait for rebalance to finish
cbq -e "ALTER INDEX ALL ON bucket_name RESUME;";

Alternatively, schedule index maintenance windows outside rebalance operations.

Escalation Criteria

Open a Couchbase support ticket when:

• Index rebuild fails on multiple nodes after applying the above fixes.
• Query latency degrades > 2× baseline for > 30 minutes due to missing indexes.
• Rebuild errors persist during a cluster rebalance that cannot be postponed.

Attach the following to the ticket:

• cbcollect_info output from each affected node.
• Relevant snippets from indexer.log covering the failure window.
• Output of SELECT * FROM system:indexes WHERE keyspace='bucket_name'; and cbstats index counters.

Verification After Remediation

1. Run cbq -e "SELECT name, index_state FROM system:indexes WHERE keyspace='bucket_name';". Confirm all indexes report online.
2. Compare cbstats index counters before and after fix; num_docs_pending should drop to zero.
3. Execute a representative N1QL query and verify it completes without stale index warnings.
4. Monitor indexer.log for 15 minutes; no new ERROR or rebuild stalled entries should appear.

Limitations and Environment Notes

This guide assumes Couchbase Server 6.0+ with the standard N1QL index API. Couchbase Autonomous Operator (Kubernetes) and Couchbase Capella (managed cloud) may expose different diagnostic endpoints, enforce automatic reindex policies, or restrict direct cbstats access. Adjust commands accordingly and consult platform-specific documentation.