Choosing Between GSI and N1QL Views in Couchbase for Query Performance

Decision and Constraints

When designing a Couchbase application you must decide how to support frequent read‑only queries that filter on document fields. The two primary indexing mechanisms are Global Secondary Indexes (GSI) and N1QL Views (MapReduce). The decision hinges on query latency, update frequency, and resource isolation.

Comparison of Supported Options

Feature	Global Secondary Index (GSI)	N1QL Views
Index service	Dedicated Index Service (scales independently)	Runs within the Data Service (no separate scaling)
Update model	Incremental, real‑time (near‑zero lag)	Batch‑oriented MapReduce; updates occur on view rebuild
Query flexibility	Ad‑hoc N1QL with any predicate supported by the index	Pre‑defined map functions; limited to emitted keys/values
Typical use case	OLTP‑style lookups, range scans, composite filters	Pre‑aggregated reports, periodic analytics
Resource impact	Consumes memory and CPU in Index Service; write amplification	Consumes Data Service resources during rebuild; lower steady‑state overhead

Trade‑offs

• Performance: GSI provides sub‑millisecond latency for point lookups and range queries because the index is kept up‑to‑date with mutations. Views incur rebuild latency; query results reflect the index state at the last rebuild.
• Scalability: GSI can be scaled by adding Index Service nodes without affecting data storage. Views share the same nodes as data, so heavy view rebuilds can compete with data service throughput.
• Operational overhead: Creating a GSI is a single N1QL statement; maintaining many GSIs requires monitoring index memory and write amplification. Views require defining map/reduce functions and scheduling periodic rebuilds.
• Consistency: GSI offers consistency levels (e.g., request_plus) to trade latency for up‑to‑date results. Views are eventually consistent by design.

Concrete Implementation: Creating a GSI for a Common Query Pattern

Assume a bucket orders where each document has fields status (string) and orderDate (ISO‑8601 timestamp). A typical N1QL query retrieves all pending orders from the last 30 days:

SELECT * FROM orders
WHERE status = 'pending'
  AND orderDate >= STR_TO_MILLIS(NOW()) - 30*24*60*60*1000;

To support this pattern with a GSI:

1. Permissions: You need the Query Index role on the bucket.
2. Command (run via cbq, N1QL Workbench, or any N1QL client):

CREATE INDEX idx_orders_status_date ON `orders`(status, orderDate)
USING GSI;

1. Verification: Use EXPLAIN to confirm the optimizer picks the index:

EXPLAIN SELECT * FROM orders
WHERE status = 'pending'
  AND orderDate >= STR_TO_MILLIS(NOW()) - 30*24*60*60*1000;

Look for a #operator" : "IndexScan3" entry referencing idx_orders_status_date in the output.

Runtime check: Monitor Index Service memory via the Couchbase Web Console (Monitoring → Index) while inserting a burst of orders. Ensure memory growth stays within provisioned limits and that the write amplification metric does not exceed expected thresholds.

Limitations and Practical Checks

• Over‑indexing: each additional GSI adds memory consumption proportional to the indexed fields and write amplification on every mutation. Periodically run SELECT * FROM system:indexes WHERE keyspace_id = 'orders'; to review existing indexes.
• Consistency latency: if you require strict read‑your‑writes, use REQUEST_PLUS consistency in the query (... USING GSI WITH {"consistency":"request_plus"}). This adds latency proportional to the index catch‑up time.
• Index rebuild: after a cluster upgrade or topology change, GSIs may need to be rebuilt. Check the state field in system:indexes; a value of online indicates the index is ready for queries.

Rollback (if needed)

Creating a GSI changes cluster state. To remove the index:

DROP INDEX `orders`.idx_orders_status_date USING GSI;

Verify removal with SELECT * FROM system:indexes WHERE name = 'idx_orders_status_date'; which should return zero rows.