Tip&tricks to build indexes cmd for collections which are newly added

Couchbase Server
1

Dear ALL,

Here is the query to get the build cmd to do, it will fail some times due to another index build is in progress , you may re-trigger it.

#clasic buckets

BUILD INDEX ON `travel-sample` ((
SELECT RAW name
FROM system:indexes
WHERE keyspace_id = 'travel-sample'
AND state = 'deferred' ));

#scopes&collections

SELECT RAW
  "BUILD INDEX ON `" || bucket_id || "`.`" || scope_id || "`.`" || keyspace_id || "` (`" || name || "`);"
AS build_stmt
FROM system:indexes
WHERE state IN ["deferred", "created"]
  AND bucket_id = "yourbucket";

other automated scripts are there in below page for same

#clasic buckets

#!/bin/bash

QUERY_HOST=http://localhost:8091
USERNAME=Administrator
PASSWORD=password

for i in "BucketName1" "BucketName2" "BucketName3"
do
  /opt/couchbase/bin/cbq -e $QUERY_HOST -u $USERNAME -p $PASSWORD -s="$( \
    echo "BUILD INDEX ON \`$i\` (\`$( \
      /opt/couchbase/bin/cbq -e $QUERY_HOST -u $USERNAME -p $PASSWORD -q=true -s="SELECT name FROM system:indexes where keyspace_id = '$i' AND state = 'deferred'" | \
        sed -n -e '/{/,$p' | \
        jq -r '.results[].name' | \
        sed ':a;/.*/{N;s/\n/\`,\`/;ba}')\`)")"

  # Wait for completion
  until [ `/opt/couchbase/bin/cbq -e $QUERY_HOST -u $USERNAME -p $PASSWORD -q=true -s="SELECT COUNT(*) as unbuilt FROM system:indexes WHERE keyspace_id = '$i' AND state <> 'online'" | sed -n -e '/{/,$p' | jq -r '.results[].unbuilt'` -eq 0 ];
  do
    sleep 5
  done
done

#scopes&collections

I Have tried to prepare the script using Chat GPT and i have modified few things it is working fine build the indexes , but here there is one problem build indexes are going by sequential order, in your case above code trigger whole data stream in _default data space so all indexes are build at once. but here it is considering each collection as one data space. so if any of indexes under that collection then all of that indexes might build all at once.

in my case there are 3 scope and each scope contains 23 collections and each collection have one single index. that’s might be the reason for its behaviour.
thank you in advance , please add any changes are require in this code for better performance or improvements
i have not tested it for normal buckets which does not have scopes and collections.

#!/bin/bash

#set -x # Enable debugging

QUERY_HOST=http://localhost:8091
USERNAME=Administrator
PASSWORD=password

# Function to build indexes for a given bucket, scope, and collection
build_indexes() {
local bucket=$1
local scope=$2
local collection=$3

echo "Building indexes for bucket: $bucket, scope: $scope, collection: $collection ..."

# Query to get index names for the collection
index_names=$( /opt/couchbase/bin/cbq -e $QUERY_HOST -u $USERNAME -p $PASSWORD -q=true -s="SELECT name FROM system:indexes WHERE keyspace_id = '$collection' AND scope_id = '$scope' AND bucket_id = '$bucket' AND state = 'deferred'" | \
sed -n -e '/{/,$p' | \
jq -r '.results[].name' | \
sed ':a;/.*/{N;s/\n/\`,\`/;ba}' )

echo "Index Names: $index_names"

if [ -n "$index_names" ]; then
# Build the indexes
/opt/couchbase/bin/cbq -e $QUERY_HOST -u $USERNAME -p $PASSWORD -s="$(echo "BUILD INDEX ON \`$bucket\`.\`$scope\`.\`$collection\` (\`$index_names\`)")"
else
echo "No deferred indexes to build for bucket: $bucket, scope: $scope, collection: $collection ..!"
fi

# Wait for completion
until [ $(/opt/couchbase/bin/cbq -e $QUERY_HOST -u $USERNAME -p $PASSWORD -q=true -s="SELECT COUNT(*) as unbuilt FROM system:indexes WHERE keyspace_id = '$collection' AND scope_id = '$scope' AND bucket_id = '$bucket' AND state <> 'online'" | sed -n -e '/{/,$p' | jq -r '.results[].unbuilt') -eq 0 ]; do
echo "Waiting for indexes to build for bucket: $bucket, scope: $scope, collection: $collection ..."
sleep 5
done

echo "All indexes built for bucket: $bucket, scope: $scope, collection: $collection !!!"
}

# Main loop to iterate over each bucket
for bucket in "bkt" "bkt_cache" ; do
echo "###Processing bucket: $bucket ...###"

# Get all scopes for the bucket
scopes=$( /opt/couchbase/bin/cbq -e $QUERY_HOST -u $USERNAME -p $PASSWORD -q=true -s="SELECT DISTINCT raw \`keyspaces\`.\`scope\` from system:keyspaces where \`keyspaces\`.\`bucket\`='$bucket'; " |jq -r '.results[]' )

echo "Scopes: $scopes"

if [ -z "$scopes" ]; then
echo "No valid scopes found for bucket: $bucket ..!"
continue
fi

for scope in $scopes; do
# Skip if the scope is null
if [ "$scope" == "null" ] || [ -z "$scope" ]; then
echo "Skipping null or empty scope for bucket: $bucket ..!"
continue
fi

echo "Processing scope: $scope >#"

# Get all collections for the scope
collections=$( /opt/couchbase/bin/cbq -e $QUERY_HOST -u $USERNAME -p $PASSWORD -q=true -s="select DISTINCT raw \`keyspaces\`.\`id\` FROM system:keyspaces where \`keyspaces\`.\`scope\`='$scope' and \`keyspaces\`.\`bucket\`='$bucket';" | jq -r '.results[]' )

echo "Collections: $collections "

if [ -z "$collections" ]; then
echo " No collections found for scope: $scope in bucket: $bucket ..!"
continue
fi

for collection in $collections; do
# Build indexes for each collection
build_indexes "$bucket" "$scope" "$collection"
done
done
done

the output will be like bellow ,

root@cbr-725-couchbase-cluster-3:/data# cat script_output1.log
Processing bucket: upc
Scopes: null
scope1
stg3
Skipping null or empty scope for bucket: upc
Processing scope: scope1
Collections: product
attachment
glCode
....

Building indexes for bucket: bucket1, scope: bucket1, collection: product
Index Names:
No deferred indexes to build for bucket: bucket1, scope: bucket1, collection: product
All indexes built for bucket: bucket1, scope: bucket1, collection: product

2 
SELECT RAW CONCAT("BUILD INDEX ON ", k ,  "(['", CONCAT2 ("','", inames), "']);")
FROM system:indexes AS s
LET bid = CONCAT("`",s.bucket_id, "`"),
    sid = CONCAT("`", s.scope_id, "`"),
    kid = CONCAT("`", s.keyspace_id, "`"),
    k = NVL2(bid, CONCAT2(".", bid, sid, kid), kid)
WHERE s.namespace_id = "default"
GROUP BY k
LETTING inames = ARRAY_AGG(s.name) FILTER (WHERE s.state = 'deferred')
HAVING ARRAY_LENGTH(inames) > 0;

This gives build index commands (one per collection) for whole cluster then use cbq shell (introduce delay between) (you may need to remove quotes from results)