Index Placement
- how-to
How to place indexes on specified nodes, create index replicas, and partition indexes.
Introduction
To improve query responsiveness, you can choose where to save primary and secondary indexes. You can also partition a large secondary index across multiple nodes. In Couchbase Server Enterprise Edition, you can create replicas of primary indexes, secondary indexes, and secondary index partitions, to enhance index availability.
If you want to try out the examples in this section, follow the instructions given in Do a Quick Install to install Couchbase Server, configure a cluster, and load a sample dataset. Read the following for further information about the tools available for editing and executing queries:
This how-to guide focuses on index placement, partitioning, and replication using N1QL queries. The SDKs do not currently support all of these features fully. |
Placing a Single Index
When you create a primary or secondary index, you can specify the placement of the index.
To specify the placement of a single index:
-
Use the
WITH
clause to specify the index options. -
In the index options, use the
nodes
attribute to specify an array containing a single node. The node name must include the cluster administration port, by default 8091.
The following query creates a secondary index that contains airports with an alt
value greater than 1000 on the node 127.0.0.1
.
CREATE INDEX idx_airport_over1000
ON `travel-sample`.inventory.airport(geo.alt)
WHERE geo.alt > 1000
USING GSI
WITH {"nodes": ["127.0.0.1:8091"]};
For further details and examples, refer to CREATE PRIMARY INDEX and CREATE INDEX.
Partitioning an Index
When you create a secondary index, you can split the index into multiple partitions. You can specify the placement of the partitions of an index, just as you can for a single index.
To partition a secondary index:
-
Use the
PARTITION BY HASH
clause to specify the field expression or expressions by which you want to partition the index. -
If required, use the
WITH
clause to specify the index options, and use thenum_partition
attribute to specify the number of partitions. If you don’t specify the number of partitions, the index has 8 partitions.
The following query creates an index partitioned by the document ID.
CREATE INDEX idx ON `travel-sample`.inventory.airline
(country, name, id)
PARTITION BY HASH(META().id);
For further details and examples, refer to Index Partitioning.
Replicating an Index
In Couchbase Server Enterprise Edition, when you create a secondary index, you can replicas of the index across multiple nodes.
You can specify replicas for a partitioned index, just as you can for a single index.
To replicate a secondary index by specifying the number of replicas:
-
Use the
WITH
clause to specify the index options. -
In the index options, use the
num_replica
attribute to specify the number of replicas to create. The value of this attribute must be less than or equal to the number of index nodes.
If you specify num_replica
but not nodes
, the replicas are distributed amongst index nodes automatically.
The following query creates an index with two replicas, with no destination-nodes specified.
CREATE INDEX country_idx ON `travel-sample`.inventory.airport(country, city)
WITH {"num_replica": 2};
To replicate a secondary index by specifying the replica nodes:
-
Use the
WITH
clause to specify the index options. -
In the index options, use the
nodes
attribute to specify an array containing the nodes on which you want to create replicas. Each node name must include the cluster administration port, by default 8091.
If you specify nodes
but not num_replica
, the number of replicas (plus the original index) is equal to the number of nodes.
The following query creates an index on node1
, with replicas on node2
and node3
.
CREATE INDEX country_idx ON `travel-sample`.inventory.airport(country, city)
WITH {"nodes": ["node1:8091", "node2:8091", "node3:8091"]};
If you specify both num_replica and nodes , the number of nodes must be one greater than the number of replicas.
|
For further details and examples, refer to Index Replication.
Altering Index Placement
In Couchbase Server Enterprise Edition, you can alter the placement of an existing index or replica, increase or decrease the number of replicas, or to drop a replica temporarily. You can also perform the same alterations to a partitioned index and any replica partitions.
To alter index placement:
-
Use the
ALTER INDEX
statement to specify the the index to alter. -
Use the
ON
clause to specify the keyspace on which the index was built. -
Use the
WITH
clause to specify the action to take and the parameters of that action.
The following query moves the index def_inventory_airport_faa
to node 192.168.10.11
.
ALTER INDEX def_inventory_airport_faa ON `travel-sample`.inventory.airport
WITH {"action": "move", "nodes": ["192.168.10.11:8091"]};
For further details and examples, refer to ALTER INDEX.