Speeding Up Ad‑Hoc Queries in Couchbase with Targeted Secondary Indexes

The Problem: Ad‑Hoc Filters Cause Full Bucket Scans

When your application frequently filters JSON documents by fields that are not the document key, Couchbase’s primary‑key lookup cannot satisfy the predicate. The query engine then falls back to a sequential scan of the bucket, which can take hundreds of milliseconds even on a modest cluster.

Solution: Add a Targeted Secondary Index

Creating a secondary index that matches the filtered fields lets the N1QL engine push the predicate down to the index. The query can be resolved with an IndexScan, often reading only the index pages and returning results in a few milliseconds.

Worked Example: Indexing Product Type and Price

Assume a bucket named inventory that stores product documents like:

{
  "type": "laptop",
  "price": 1200,
  "sku": "ABC-123"
}

To support queries that filter on type and a range on price, create a partial secondary index:
cbq -u Administrator -p $CB_PASSWORD -s "
CREATE INDEX idx_product_type ON inventory(type)
WHERE type IS NOT NULL;
"

Replace $CB_PASSWORD with the password for a user that has the Query Index Admin role. The command can be run from any host with network access to the Couchbase cluster; it requires no special shell privileges beyond being able to execute the cbq CLI.
The index is built asynchronously. While it is building, monitor RAM usage via the Web Console under Server → Indexes or the CLI command cbstats index_ram_quota_used.
Trade‑offs and Limitations

  
Each secondary index consumes additional RAM and disk space proportional to the number of indexed documents.
  
Every write (insert, update, delete) must also update the index, which can reduce write throughput.
  
Index definitions are version‑dependent; the syntax for partial indexes shown above is valid for Couchbase Server 6.x and later. In 5.x the WHERE clause was not supported, requiring a different approach.
  
Over‑indexing can cause memory pressure that leads to ejection of active data, degrading both read and write latency.

Verification and Monitoring

  
Confirm the query plan uses the new index:
    
cbq -u Administrator -p $CB_PASSWORD -s "
EXPLAIN SELECT * FROM inventory WHERE type = 'laptop' AND price > 800;
"

    Look for an IndexScan entry that references idx_product_type in the output.
  
  
Measure latency: run the query repeatedly (e.g., 20 times) and average the response time reported by cbq or your client library. Compare this to the same query executed before the index was created.
  
  
Check index health:
    
cbq -u Administrator -p $CB_PASSWORD -s "
SELECT * FROM system:indexes WHERE name = 'idx_product_type';
"

    Ensure state is online and num_docs matches the expected count.
  

Actionable Steps (Including Rollback)
If the index delivers the expected latency gain and fits within your memory budget, keep it as part of your deployment pipeline. If you need to remove it—for example, after changing access patterns—run:
cbq -u Administrator -p $CB_PASSWORD -s "
DROP INDEX inventory.idx_product_type;
"

Dropping the index immediately stops further index maintenance, freeing RAM and disk. Verify removal with the same system:indexes query; the entry should disappear or show state as deleted.
Finally, automate index creation in your CI/CD pipeline by storing the CREATE INDEX statement in a version‑controlled script and applying it with the cbq CLI during deployment. Pair this with a monitoring alert on index_ram_quota_used to catch unexpected growth before it impacts performance.