Choosing Between Deferred and Immediate Index Builds for Rollback Safety

Answer

For a rollback‑safe schema change, use a CREATE INDEX … WITH deferred followed by BUILD INDEX during a low‑write window. This approach keeps the old index alive, spreads I/O, and keeps foreground mutation latency within SLA. Use an immediate build only if the cluster is under light write load and you can tolerate a brief pause on the affected vBuckets.

Why Deferred Builds Are Preferable for Rollback

• I/O Predictability: Deferred builds let you schedule the scan, producing a smooth, low‑peak write pattern that is easier to monitor.
• Mutation Latency: The background scan adds a modest, controllable delay to writes. Throttling via maxIndexerThreads, memoryQuota, and priority keeps latency within SLA.
• Rollback Safety: The old index remains available until you confirm the new one is healthy, preventing query downtime.

Immediate Builds – When to Use

Immediate builds block the transaction that creates the index until the index is fully populated. Under high write load this can pause vBuckets, spike latency, and potentially bring queries down. Use this only if write traffic is low or you have a short, well‑planned maintenance window.

Admin Controls to Prioritize Mutations

• SET INDEXER PRIORITY LOW – Lowers indexer thread priority so foreground writes get precedence.
• Adjust maxParallelism or maxIndexerThreads to reduce the number of concurrent scan threads.
• Use INDEXER MEMORY QUOTA to limit indexer memory usage, forcing it to spill to disk and reducing active I/O.

Concrete Steps for a Rollback‑Friendly Change

1. Create the new index deferred:

   CREATE INDEX idx_new ON bucket (field) WITH {"deferred": true};

2. Verify the old index remains available (do not drop it yet).
3. Schedule BUILD INDEX during a low‑write period:

   BUILD INDEX ON bucket (idx_new);

4. Monitor I/O and mutation latency using cbstats or the Web UI dashboards. Expect a modest increase, but no spikes.
5. Once the new index is healthy, swap or rename (if you need to keep both for a while).
6. Drop the old index only after validation.

   DROP INDEX idx_old;

Diagnostic Detail Needed?

To fine‑tune the recommendation, could you share your current peak write throughput (ops/s) and the version of Couchbase you’re running? These details help decide whether the cluster can handle an immediate build or if deferred is mandatory.