Eliminating Full Bucket Scans in Couchbase with SQL++ Indexing

The Cost of the Primary Scan

When you run a SQL++ (N1QL) query in Couchbase, the engine must decide how to find your data. If you haven't defined a specific index for your WHERE clause, Couchbase defaults to a Primary Scan. This means the system reads every single document in the bucket to check if it matches your criteria.

For a few hundred documents, this is negligible. For millions, it is a performance killer. A Primary Scan consumes massive CPU and I/O, often leading to query timeouts and degraded cluster performance. The goal for any production environment is to move from a Primary Scan to an Index Scan using Global Secondary Indexes (GSI).

Leveraging Global Secondary Indexes (GSI)

Unlike traditional databases where indexes are tied to the data's physical location, Couchbase uses GSI. This decouples the Index Service from the Data Service. You can scale your index nodes independently of your data nodes, ensuring that complex query processing doesn't starve your key-value operations of resources.

To stop a full bucket scan, your WHERE clause must contain a predicate that matches the keys of a defined index. If you are querying by a user_id, you need an index on that specific field. Without it, the query engine has no "map" to the data and is forced to scan the entire bucket.

Optimizing with Covering Indexes

A standard index helps the engine find the document, but the engine still has to go back to the Data Service to fetch the full JSON document to return the requested fields. This "fetch" step adds network latency.

A Covering Index includes not only the fields used in the WHERE clause but also the fields requested in the SELECT statement. When an index "covers" a query, the Index Service returns the result directly from the index memory, bypassing the Data Service entirely.

Example: From Primary Scan to Covering Index

Consider a scenario where you need to retrieve the email addresses of users in a specific city.

The inefficient query (triggers Primary Scan):

SELECT email FROM `users` WHERE city = "New York";

Step 1: Create a basic GSI (removes Primary Scan)

Run this in the Query Workbench of the Couchbase Web Console with cluster-admin or equivalent index-management permissions. This ensures we only scan users in New York, but we still fetch the full document to get the email.

CREATE INDEX adv_city ON `users`(city);

Step 2: Create a Covering Index (removes the data fetch)

By adding email to the index, the query becomes "covered." Note that creating indexes on a live cluster consumes memory and CPU; schedule large index builds during low-traffic windows where possible.

CREATE INDEX adv_city_email ON `users`(city, email);

Adaptive Indexes for Unpredictable Patterns

In some development phases, you may not know which fields users will filter by. Couchbase provides Adaptive Indexes. An adaptive index creates a mapping for every field in the document automatically.

While convenient, these are significantly larger and slower than targeted indexes. Use them for prototyping or low-volume internal tools, but replace them with specific GSIs before moving to production to avoid excessive memory consumption.

Trade-offs and Limitations

Indexing is not free. Every new index introduces a "write penalty." When a document is updated, the Index Service must also update all corresponding GSIs. Over-indexing can lead to increased write latency and high memory pressure on your Index nodes.

Additionally, if your index is too large to fit in RAM, the Index Resident Ratio will drop, forcing the system to read from disk and slowing down your queries significantly.

Verification and Diagnostics

To verify whether your indexing strategy is working, prepend the EXPLAIN keyword to your query in the Query Workbench:

EXPLAIN SELECT email FROM `users` WHERE city = "New York";

Check the output plan for the following indicators:

• PrimaryScan: This is a failure. You need a better index.
• IndexScan: The query is using a GSI. This is the baseline for production.
• Covering Index: Look for the absence of a "Fetch" operator in the explain plan. This indicates maximum efficiency.

To monitor health, check the Index Resident Ratio in the Couchbase Web Console under the Indexes statistics. If the ratio is consistently low, consider adding more RAM to your Index nodes or removing unused indexes. Exact operator names and console labels can vary by Couchbase Server version, so confirm against your version's documentation.