A newer version of this documentation is available.

View Latest

CREATE INDEX statement

The CREATE INDEX statement allows you to create a secondary index. Secondary indexes contain a filtered or a full set of keys in a given bucket. Secondary indexes are optional but increase query efficiency on a bucket.

CREATE INDEX is by default a synchronous operation. CREATE INDEX statement blocks until the operation finishes. Index building starts by creating a task that is queued for index build. After this phase, if you lose connectivity, index build operation continues to happen in the background. You can also run index creation asynchronously using the "defer_build" clause. In the async mode, CREATE INDEX starts a task to create the primary index and returns as soon as the task is queued for execution. The full index creation operation happens in the background.

Both GSI and View indexes provide a status field and mark index status pending. With GSI indexer, index status continues to report "pending". This status field and other index metadata can be queried using system:indexes.

Indexes cannot be built concurrently on a given bucket unless the defer_build option of the CREATE INDEX statement is used in combination with BUILD INDEX statement. The following error is reported if a second index creation operation is kicked off before the completion of the ongoing index creation.

"errors": [{"code": 12014,
    "msg": "error: Build Already In Progress. Bucket BUCKET_NAME. Index INDEX_NAME. Index state: pending"}]

You can create multiple identical secondary indexes on a bucket for better index availability and place them on separate nodes using the "nodes" clause.

Couchbase Server 4.5 release adds the capability to create global indexes on array elements and optimizes the execution of queries involving array elements. See Array Indexing for details.


CREATE INDEX [ index_name ] ON named_keyspace_ref(expression [ , expression ]* )
    WHERE filter_expressions
    [ USING GSI | VIEW ]
    [ WITH {"nodes": [ "node_name" ], "defer_build":true|false } ];


index name is a unique name that is provided to identify the index. The index name is optional in the case of the secondary index and there can be one index that is unnamed.

Valid GSI index names can contain any of the following characters: A-Z a-z 0-9 # , and must start with a letter, [A-Z a-z]. The minimum length of an index name is 1 character and there is no maximum length set for an index name. When querying, if the index name contains a '#' or '' character, you must enclose the index name within backticks.


[ namespace-name :] keyspace-name


An identifier that refers to the bucket name. Specifies the bucket as the source for which the index needs to be created. You can add an optional namespace name to the keyspace name in this way:

namespace-name : keyspace-name

For example, main:customer indicates the customer keyspace in the main namespace. If the namespace name is omitted, the default namespace in the current session is used.


Refers to an attribute name or a function.


USING clause specifies the index type to use. Secondary indexes can be created using global secondary indexes (GSI) or views. If the USING clause is not specified, by default GSI is used as the indexer.

WITH options

With clause is used to specify additional options with the GSI type indexes.

"nodes":["node name"]

A single secondary index of type GSI can only be placed on a single node that runs the indexing service. The "nodes" option allows you to specify the node that the index is placed on. If nodes is not specified, one of the nodes running the index service is randomly picked for the index.

The node name passed to the nodes parameter must include the cluster administration port (by default 8091). For example WITH {"nodes": [""]} instead of WITH {"nodes": [""]}.

"defer_build":true | false

With defer_build set to true, CREATE INDEX operation queues the task for building the index but immediately pauses the building of the index of type GSI. Index building require an expensive scan operation. Deferring building of the index with multiple indexes can optimize the expensive scan operation. Admins can defer building multiple indexes and, using BUILD INDEX statement, multiple indexes to be built efficiently with one efficient scan of bucket data.

With defer_build set to false, CREATE INDEX operation queues the task for building the index and immediately kicks off the building of the index of type GSI.

We recommend that you do not create (or drop) secondary indexes when any node with a secondary index role is down as this may result in duplicate index names.

Using the meta().id function

You can use the meta() function when creating an index. In this context, the meta() function does not require a parameter. It implicitly uses the keyspace being indexed.

CREATE INDEX id_ix on `beer-sample`(meta().id);

The meta() function in a query is given an expression; if this resolves to a keyspace alias, the requested field (id or CAS) is returned.

SELECT b.name, meta(b).id
FROM `beer-sample` b
WHERE meta(b).id > "g" limit 1;

Using indices for aggregates

If there is an index on the expression of an aggregate, that index may be used to satisfy the query. For example, given the index "abv_idx" created using the following statement:

CREATE INDEX abv_idx ON `beer-sample`(abv);

The query engine will use the index "abv_idx" for the following query:

SELECT min(abv), max(abv) FROM `beer-sample`;


The following example creates a secondary index that contains beers with an abv value greater than 5 on the node

CREATE INDEX over5 ON `beer-sample`(abv) WHERE abv > 5 USING GSI WITH {"nodes": [""]};

The following example creates a secondary index on the beer-sample bucket and then queries system:indexes for status of the index:

CREATE INDEX `beer-sample-type-index` ON `beer-sample`(type) USING GSI;
SELECT * FROM system:indexes WHERE name="beer-sample-type-index";

The following example creates the same secondary index by using the deferred build option and then queries system:indexes for status of the index:

CREATE INDEX `beer-sample-type-index` ON `beer-sample`(type) USING GSI
    WITH {"defer_build":true};
SELECT * FROM system:indexes WHERE name="beer-sample-type-index";

Because the deferred build option was enabled, the output from the query on system:indexes shows beer-sample-type-index shows the index has not finished building ("state": "pending").

The following example uses the BUILD INDEX statement to kick off the deferred build on the beer-sample-type-index index and then queries system:indexes for status of the index:

BUILD INDEX ON `beer-sample`(`beer-sample-type-index`) USING GSI;
SELECT * FROM system:indexes WHERE name="beer-sample-type-index";

This time the query on system:indexes shows that the index is built ("state": "online").