ALTER INDEX

The ALTER INDEX statement moves the placement of an existing index or replica among different GSI nodes.

Purpose

You can use the ALTER INDEX statement to change the placement of an existing index or replica among different GSI nodes. You can also use it to change the placement of a partitioned index and any replica partitions. You may use this statement when you encounter any of the following situations:

  • An imbalance occurs due to a particular index growing faster than expected and is needed on a different node.

  • An imbalance occurs due to a cluster of indexes being dropped on a single node.

  • A machine is scheduled for removal, so its indexes need to move off its current node.

  • The automated process of rebalancing does not give the expected results.

  • Other types of scaling up or scaling down are needed.

For example, if a node fails and you need to move it from node 172.23.130.24 to node 172.23.130.25, then you can simply execute the following code with your own index names and addresses:

CREATE INDEX idx1 ON `travel-sample`(id, airline) WHERE type="route";

ALTER INDEX `travel-sample`.idx1
WITH {"action": "move", "nodes": ["172.23.120.25:8091"]}
The ALTER INDEX move operation is asynchronous. As soon as the move alter index command is executed, the command returns. If there is no error in the input, the move operation can be tracked through the console UI and any error can be found in the Console logs and Indexer logs.

If a node goes down while an ALTER INDEX operation is in progress, then the index would rollback to its original node (not affecting queries) and a notification would appear.

Prerequisites

Only users with the RBAC role of Administrator are allowed to run the ALTER INDEX directive.

This directive is applicable only for Standard GSI (Plasma) and MOI Indexes; and hence supported only for Enterprise Edition nodes. (Not applicable for Forest DB.)

This statement is supported only from 5.5 onwards; if the cluster is in mix-mode (a mix of 5.5 and previous versions), then this directive will not work.

ALTER INDEX will not work while the cluster is undergoing a rebalance.

Syntax

alter-index ::= ALTER INDEX key-expr [ USING GSI ] WITH options
'ALTER' 'INDEX' key-expr ( 'USING' 'GSI' )? 'WITH' options

Key Expression

A string representing the corresponding named keyspace reference, with an optional index name, for example named_keyspace_ref.index_name.

USING GSI

The ALTER INDEX statement uses the Global Secondary Index. The USING GSI keywords are optional and may be omitted.

Options

An object with the following properties:

action

[Required] A string denoting the operation to be performed. The only possible value is:

move

Moves only 1 index (or its replica) at a time to a different node while not making any changes to the index topology — for example, the number of replicas remains the same.

nodes

[Required] An array of strings, specifying the new destination nodes for the index and its replicas.

  • The number of destination nodes must be the same as the number of nodes on which the index and any replicas are currently placed. You must specify the full node list, even if only one replica needs to be moved.

  • Likewise, when moving a partitioned index, the number of destination nodes must be the same as the number of nodes on which the index partitions and any replicas are currently placed. You cannot use this statement to repartition an index across a different number of nodes.

  • The source and destination node ranges may overlap, for example you may move a partitioned index from ["192.168.0.15:9000", "27.0.0.1:9001"] to ["192.168.0.15:9000", "127.0.0.1:9002"].

Return Value

If the ALTER INDEX succeeds, then:

  • The Query Workbench shows { Results: [] }

  • The index progress is visible on the UI.

  • After the movement is complete, the new indexes begin to service query scans.

  • The command line displays the new index nodes.

If the ALTER INDEX fails, then:

  • The original indexes continue to service query scans.

  • The UI Log and Query Workbench has the appropriate error message.

  • Some common errors include:

    Error Message Possible Cause

    GSI index xxxxxxxx not found

    • Mistyped an index name

    Missing Node Information For Move Index

    • Mistyped "node" instead of "nodes"

    • Mistyped punctuation or other item

    No Index Movement Required for Specified Destination List

    • Entered the current node instead of the target node

    syntax error - at \",\"

    • Missed a double-quote mark (")

    Unable to find Index service for destination xxx.xxx.xxx.xxx:8091 or destination is not part of the cluster

    • Address doesn’t exist or was mistyped

    • Node isn’t running

    • Node not properly added to the cluster

    Unsupported action value

    • Mistyped the "action"

Examples

When using the below examples, make sure Couchbase Server 5.5 Enterprise Edition is already running on the named nodes.

Example 1. Move the def_faa index from one node to another

Create a cluster of 3 nodes and then go to Settings  Sample buckets to install the travel-sample bucket. The indexes are then installed in a round-robin fashion and distributed over the 3 nodes. Then move the def_faa index from the first node (192.168.10.10 in the screenshot) to the second node (192.168.10.11 in the screenshot).

alter index servers step1
ALTER INDEX `travel-sample`.def_faa
WITH {"action": "move", "nodes": ["192.168.10.11:8091"]}

You should see:

{
  "results": []
}
alter index servers step2
Example 2. Create and move an index replica from one node to another

Create an index on node 192.168.10.10 with a replica on node 192.168.10.11, then move its replica from node 192.168.10.11 to 192.168.10.12.

CREATE INDEX country_idx ON `travel-sample`(country, city)
       WHERE type="route" USING GSI
       WITH {"nodes": ["192.168.10.10:8091", "192.168.10.11:8091"]};

ALTER INDEX `travel-sample`.country_idx
WITH {"action": "move", "nodes": ["192.168.10.10:8091", "172.23.120.12:8091"]}
Example 3. Moving multiple replicas

Create an index on node 192.168.10.10 with replicas on nodes 192.168.10.11 and 192.168.10.12, then move the replicas to nodes 192.168.10.13 and 192.168.10.14.

CREATE INDEX country_idx ON `travel-sample`(country, city)
WITH {"nodes": ["192.168.10.10:8091", "192.168.10.11:8091", "192.168.10.12:8091"]}

ALTER INDEX `travel-sample`.country_idx
WITH {"action": "move", "nodes":
      ["192.168.10.10:8091", "192.168.10.13:8091", "192.168.10.14:8091"]}
Example 4. Removing an extra replica
To avoid any downtime, before removing a replica (or index), first create an equivalent index for your queries to continue using.

If you created an index on node 192.168.10.10 with replicas on nodes 192.168.10.11 and 192.168.10.12 and later decided you didn’t want the second replica, you’d need to remove the index (which removes all replicas) and then recreate the index with only one replica.

CREATE INDEX country_idx ON `travel-sample`(country, city)
WHERE type="route" USING GSI
WITH {"nodes": ["192.168.10.10:8091", "192.168.10.11:8091", "192.168.10.12:8091"]};

DROP INDEX `travel-sample`.country_idx;

CREATE INDEX country_idx ON `travel-sample`(country, city)
WHERE type="route" USING GSI
WITH {"nodes": ["192.168.10.10:8091", "192.168.10.11:8091"]};
You would use a similar procedure to remove a partitioned index and recreate the partitioned index on a smaller or greater number of nodes. However, refer also to the section on rebalancing a partitioned index.