Couchbase Duplicate GSI Index Guard with Kestra

id: couchbase-duplicate-gsi-index-guard
namespace: company.team

inputs:
  - id: connection_string
    type: STRING
    defaults: couchbase://couchbase.internal
    description: Couchbase connection string, for example couchbase://host1,host2.
  - id: username
    type: STRING
    defaults: Administrator
    description: Couchbase user with query and index privileges on the target keyspace.
  - id: bucket
    type: STRING
    defaults: travel-sample
    description: Bucket holding the keyspace to inspect.
  - id: scope
    type: STRING
    defaults: _default
    description: Scope inside the bucket. Use _default for the default scope.
  - id: collection
    type: STRING
    defaults: _default
    description: Collection inside the scope. Use _default for the default collection.
  - id: password
    type: STRING
    defaults: "{{ secret('COUCHBASE_PASSWORD') }}"
    description: Password for the Couchbase user. Defaults to a Kestra secret.
  - id: dry_run
    type: BOOL
    defaults: true
    description: When true, report the surplus indexes and never run DROP INDEX.
  - id: auto_remediate
    type: BOOL
    defaults: false
    description: When true and dry_run is false, drop the surplus index in every
      duplicate group.
  - id: min_indexes_retained
    type: INT
    defaults: 1
    description: Floor on how many online indexes the keyspace must still have after
      the drops. 1 means never drop the last index.

tasks:
  - id: detect
    type: io.kestra.plugin.couchbase.Query
    connectionString: "{{ inputs.connection_string }}"
    username: "{{ inputs.username }}"
    password: "{{ inputs.password }}"
    fetchType: FETCH
    query: >-
      SELECT g.keyspace_path, g.index_keys, g.index_condition,
      g.index_partition, members, members[0] AS keep_index, members[1:] AS
      surplus_indexes, ARRAY_LENGTH(members) AS member_count FROM ( SELECT "`{{
      inputs.bucket }}`.`{{ inputs.scope }}`.`{{ inputs.collection }}`" AS
      keyspace_path, ENCODE_JSON(idx.index_key) AS index_keys,
      IFMISSING(idx.`condition`, "") AS index_condition,
      IFMISSING(idx.`partition`, "") AS index_partition, idx.name AS member_name
      FROM system:indexes AS idx WHERE IFMISSING(idx.bucket_id, idx.keyspace_id)
      = "{{ inputs.bucket }}" AND IFMISSING(idx.scope_id, "_default") = "{{
      inputs.scope }}" AND (CASE WHEN idx.bucket_id IS MISSING THEN "_default"
      ELSE idx.keyspace_id END) = "{{ inputs.collection }}" AND idx.state =
      "online" AND idx.`using` = "gsi" AND IFMISSING(idx.is_primary, false) =
      false AND ARRAY_LENGTH(IFMISSING(idx.index_key, [])) > 0 ) AS g GROUP BY
      g.keyspace_path, g.index_keys, g.index_condition, g.index_partition
      LETTING members = ARRAY_SORT(ARRAY_AGG(g.member_name)) HAVING
      ARRAY_LENGTH(members) > 1 ORDER BY g.index_keys

  - id: count_online
    type: io.kestra.plugin.couchbase.Query
    connectionString: "{{ inputs.connection_string }}"
    username: "{{ inputs.username }}"
    password: "{{ inputs.password }}"
    fetchType: FETCH
    query: >-
      SELECT COUNT(1) AS online_total FROM system:indexes AS idx WHERE
      IFMISSING(idx.bucket_id, idx.keyspace_id) = "{{ inputs.bucket }}" AND
      IFMISSING(idx.scope_id, "_default") = "{{ inputs.scope }}" AND (CASE WHEN
      idx.bucket_id IS MISSING THEN "_default" ELSE idx.keyspace_id END) = "{{
      inputs.collection }}" AND idx.state = "online"

  - id: plan
    type: io.kestra.plugin.core.output.OutputValues
    values:
      group_count: >-
        {{ (outputs.detect.rows ?? []) | length }}
      surplus_count: >-
        {{ (outputs.detect.rows ?? []) | jq('[.[].surplus_indexes[]] | length')
        | first }}
      surplus_names: >-
        {{ (outputs.detect.rows ?? []) | jq('[.[].surplus_indexes[]] | tojson')
        | first }}
      surplus_label: >-
        {{ (outputs.detect.rows ?? []) | jq('[.[].surplus_indexes[]] | join(",
        ")') | first }}
      keep_label: >-
        {{ (outputs.detect.rows ?? []) | jq('[.[].keep_index] | join(", ")') |
        first }}
      drop_targets: >-
        {{ (outputs.detect.rows ?? []) | jq('[.[] | .keyspace_path as $k |
        .surplus_indexes[] | {keyspace: $k, name: .}] | tojson') | first }}
      online_total: >-
        {{ outputs.count_online.rows[0].online_total }}

  - id: guard_primary
    type: io.kestra.plugin.couchbase.Query
    connectionString: "{{ inputs.connection_string }}"
    username: "{{ inputs.username }}"
    password: "{{ inputs.password }}"
    fetchType: FETCH
    query: >-
      SELECT RAW idx.name FROM system:indexes AS idx WHERE
      IFMISSING(idx.bucket_id, idx.keyspace_id) = "{{ inputs.bucket }}" AND
      IFMISSING(idx.scope_id, "_default") = "{{ inputs.scope }}" AND (CASE WHEN
      idx.bucket_id IS MISSING THEN "_default" ELSE idx.keyspace_id END) = "{{
      inputs.collection }}" AND IFMISSING(idx.is_primary, false) = true AND
      idx.name IN {{ outputs.plan.values.surplus_names }}

  - id: report
    type: io.kestra.plugin.core.log.Log
    message: >-
      Keyspace {{ inputs.bucket }}.{{ inputs.scope }}.{{ inputs.collection }}:
      {{ outputs.plan.values.group_count }} duplicate group(s), {{
      outputs.plan.values.surplus_count }} surplus index(es) of {{
      outputs.plan.values.online_total }} online. Keep: [{{
      outputs.plan.values.keep_label }}]. Surplus: [{{
      outputs.plan.values.surplus_label }}]. Groups: {{ outputs.detect.rows }}

  - id: on_findings
    type: io.kestra.plugin.core.flow.If
    condition: "{{ (outputs.plan.values.surplus_count | number) > 0 }}"
    then:
      - id: findings_alert
        type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
        url: "{{ secret('SLACK_WEBHOOK_URL') }}"
        payload: |
          {"text": "Couchbase duplicate GSI guard: {{ outputs.plan.values.group_count }} duplicate group(s) on {{ inputs.bucket }}.{{ inputs.scope }}.{{ inputs.collection }}. Surplus: {{ outputs.plan.values.surplus_label }}. Keeping: {{ outputs.plan.values.keep_label }}. dry_run={{ inputs.dry_run }}, auto_remediate={{ inputs.auto_remediate }}."}

  - id: safety_guard
    type: io.kestra.plugin.core.flow.If
    condition: "{{ ((outputs.guard_primary.rows ?? []) | length) > 0 or
      ((outputs.plan.values.surplus_count | number) > 0 and
      ((outputs.plan.values.online_total | number) -
      (outputs.plan.values.surplus_count | number)) <
      inputs.min_indexes_retained) }}"
    then:
      - id: refuse_unsafe_plan
        type: io.kestra.plugin.core.execution.Fail
        errorMessage: >-
          Refusing the plan on {{ inputs.bucket }}.{{ inputs.scope }}.{{
          inputs.collection }}. Primary indexes caught in the plan: {{
          outputs.guard_primary.rows }}. Online indexes {{
          outputs.plan.values.online_total }} minus surplus {{
          outputs.plan.values.surplus_count }} is below the floor of {{
          inputs.min_indexes_retained }}. Nothing was dropped.

  - id: remediate
    type: io.kestra.plugin.core.flow.If
    condition: "{{ (outputs.plan.values.surplus_count | number) > 0 and
      inputs.auto_remediate and not inputs.dry_run }}"
    then:
      - id: approval_hold
        type: io.kestra.plugin.core.flow.Pause
        onResume:
          - id: approved
            type: BOOL
            required: false
            description: Set to true to drop the surplus indexes listed in the findings
              alert.
      - id: apply_or_skip
        type: io.kestra.plugin.core.flow.If
        condition: "{{ outputs.approval_hold.onResume.approved ?? false }}"
        then:
          - id: drop_surplus
            type: io.kestra.plugin.core.flow.Loop
            concurrencyLimit: 1
            values: "{{ outputs.plan.values.drop_targets }}"
            tasks:
              - id: drop_index
                type: io.kestra.plugin.couchbase.Query
                connectionString: "{{ inputs.connection_string }}"
                username: "{{ inputs.username }}"
                password: "{{ inputs.password }}"
                fetchType: NONE
                query: >-
                  DROP INDEX `{{ fromJson(item.value).name }}` ON {{
                  fromJson(item.value).keyspace }}
          - id: reverify
            type: io.kestra.plugin.couchbase.Query
            connectionString: "{{ inputs.connection_string }}"
            username: "{{ inputs.username }}"
            password: "{{ inputs.password }}"
            fetchType: FETCH
            query: >-
              SELECT g.keyspace_path, g.index_keys, g.index_condition,
              g.index_partition, members, ARRAY_LENGTH(members) AS member_count
              FROM ( SELECT "`{{ inputs.bucket }}`.`{{ inputs.scope }}`.`{{
              inputs.collection }}`" AS keyspace_path,
              ENCODE_JSON(idx.index_key) AS index_keys,
              IFMISSING(idx.`condition`, "") AS index_condition,
              IFMISSING(idx.`partition`, "") AS index_partition, idx.name AS
              member_name FROM system:indexes AS idx WHERE
              IFMISSING(idx.bucket_id, idx.keyspace_id) = "{{ inputs.bucket }}"
              AND IFMISSING(idx.scope_id, "_default") = "{{ inputs.scope }}" AND
              (CASE WHEN idx.bucket_id IS MISSING THEN "_default" ELSE
              idx.keyspace_id END) = "{{ inputs.collection }}" AND idx.state =
              "online" AND idx.`using` = "gsi" AND IFMISSING(idx.is_primary,
              false) = false AND ARRAY_LENGTH(IFMISSING(idx.index_key, [])) > 0
              ) AS g GROUP BY g.keyspace_path, g.index_keys, g.index_condition,
              g.index_partition LETTING members =
              ARRAY_SORT(ARRAY_AGG(g.member_name)) HAVING ARRAY_LENGTH(members)
              > 1 ORDER BY g.index_keys
          - id: reverify_gate
            type: io.kestra.plugin.core.flow.If
            condition: "{{ ((outputs.reverify.rows ?? []) | length) > 0 }}"
            then:
              - id: reverify_failed
                type: io.kestra.plugin.core.execution.Fail
                errorMessage: >-
                  Re-read of system:indexes still shows {{
                  (outputs.reverify.rows ?? []) | length }} duplicate group(s)
                  on {{ inputs.bucket }}.{{ inputs.scope }}.{{ inputs.collection
                  }}. Detail: {{ outputs.reverify.rows }}
            else:
              - id: confirm_deduplicated
                type: io.kestra.plugin.core.log.Log
                message: >-
                  Dropped {{ outputs.plan.values.surplus_count }} surplus
                  index(es) on {{ inputs.bucket }}.{{ inputs.scope }}.{{
                  inputs.collection }}. system:indexes now reports no keyspace
                  with two online GSI indexes on the same keys, condition and
                  partition.
        else:
          - id: approval_declined
            type: io.kestra.plugin.core.log.Log
            message: >-
              Approval was not granted on {{ inputs.bucket }}.{{ inputs.scope
              }}.{{ inputs.collection }}. Nothing was dropped. Surplus still
              present: [{{ outputs.plan.values.surplus_label }}].

triggers:
  - id: weekly_safe_check
    type: io.kestra.plugin.core.trigger.Schedule
    cron: "0 7 * * 1"
    disabled: true
    inputs:
      dry_run: true
      auto_remediate: false

errors:
  - id: error_alert
    type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
    url: "{{ secret('SLACK_WEBHOOK_URL') }}"
    payload: |
      {"text": "Couchbase duplicate GSI guard FAILED on {{ inputs.bucket }}.{{ inputs.scope }}.{{ inputs.collection }} (execution {{ execution.id }}). A query failed, the safety guard refused the plan or the re-read still found a duplicate group."}

Two online GSI indexes covering the same keys on the same keyspace do the same work twice. Every mutation updates both, both hold their own copy in index RAM and the query service picks one of them, so the second is paid for on every write and never read. Couchbase reports no error for it. It happens after a rename that left the old index behind, a migration that was re-run or two people adding the same index. This blueprint groups system:indexes, keeps one index per group, drops the surplus behind a dry-run gate and a human approval, then re-reads system:indexes and fails the run if any group still has more than one member.

This blueprint was created by zkasuran.

Safety

Dropping an index is not reversible, so the rules are in the query and the task graph rather than only in this text.

A primary index is never a candidate. The detect query filters is_primary out. A separate query re-reads system:indexes for primary indexes whose name appears in the plan. One hit fails the run before any drop.
The last index on a keyspace is never dropped. Keeping one member per group already guarantees it. min_indexes_retained makes the floor explicit: the run fails if the online index count minus the planned drops would fall under it.
Only indexes whose state is online are grouped. An index that is still deferred or building is a different problem and is left alone.
The index condition is part of the group key. Two indexes on the same keys with different WHERE clauses are partial indexes serving different queries, not duplicates. Dropping one of them would delete a real optimisation, so they are never grouped together. partition and using are in the group key for the same reason.
Read-only by default. dry_run defaults to true and auto_remediate to false, so a plain run only reports. The DROP INDEX runs only when auto_remediate is true, dry_run is false and a human answers the Pause with approved: true. An unanswered resume reads as not approved.
The drop loop runs with concurrencyLimit: 1, so index drops are serialised rather than racing each other on the same keyspace.

How it works

detect (io.kestra.plugin.couchbase.Query) groups system:indexes by keyspace, index_key, condition and partition, over online non-primary GSI indexes on the target keyspace. LETTING members = ARRAY_SORT(ARRAY_AGG(name)) plus HAVING ARRAY_LENGTH(members) > 1 leaves one row per duplicate group. members[0] is the index that is kept and members[1:] is the surplus, so the survivor is the same on every run.
count_online counts every online index on the keyspace, primary included. That number feeds the floor check.
plan (io.kestra.plugin.core.output.OutputValues) turns the group rows into the counts, the name lists and the drop targets the later tasks read.
guard_primary asks Couchbase whether any planned name is a primary index. The answer comes from system:indexes rather than from the plan, so it is a real check and not a restatement.
report (io.kestra.plugin.core.log.Log) logs the counts, the kept names, the surplus names and the full group rows.
on_findings (io.kestra.plugin.core.flow.If) posts a Slack findings alert through io.kestra.plugin.slack.notifications.SlackIncomingWebhook whenever there is surplus. It fires on a dry run too.
safety_guard fails the run with io.kestra.plugin.core.execution.Fail if a primary index is in the plan or if the keyspace would drop under min_indexes_retained. Nothing is dropped in that case.
remediate runs only when there is surplus and auto_remediate is true and dry_run is false. approval_hold (io.kestra.plugin.core.flow.Pause) waits for a human. On approval drop_surplus (io.kestra.plugin.core.flow.Loop, concurrencyLimit: 1) issues one DROP INDEX <name> ON <keyspace> per surplus index.
reverify runs the grouping query again. reverify_gate fails the run if any group still has more than one member, so a partial drop never passes as healthy. On a declined approval the flow logs it and leaves the indexes alone. The errors block posts a Slack alert on any failure.

What you get

A scheduled read-only check that names the redundant indexes on a keyspace.
A survivor chosen by a rule, not at random, so two runs agree on what to keep.
A DROP INDEX remediation gated on dry run, an explicit flag and a human approval.
A re-read of system:indexes that fails the run when the drop did not land.
Partial indexes left alone, because the WHERE clause is part of the index identity.
Slack alerts on detection and on failure.

Who it's for

Couchbase operators who want index RAM and write amplification back without guessing.
Platform teams whose index DDL is applied by migrations that can be re-run.
On-call engineers looking for the write-path cost that no error message reports.

Why orchestrate this with Kestra

Couchbase does not warn about a redundant index. It builds it, maintains it on every mutation and keeps it resident. Finding the overlap needs a grouped read of system:indexes. Acting on it needs a judgement call about which copy survives. Kestra runs the read on a schedule, keeps the DROP INDEX behind an approval, re-reads the cluster to confirm the result and routes findings and failures to Slack. Every run is an execution you can replay with the plan it used.

Tested end to end

Verified on Kestra 2.0.5 against a real Couchbase cluster (couchbase/server:community) with the data, index and query services enabled, confirmed before and after with direct N1QL queries against system:indexes.

Healthy keyspace: the detector returned 0 groups, no alert fired, nothing was dropped.
Degraded keyspace with two identical indexes on the same key: the detector returned exactly one group with two members. The index on different keys and the two same-key indexes with different WHERE clauses were not in it.
dry_run=true with auto_remediate=true: the findings alert posted, no DROP INDEX ran and system:indexes was unchanged afterward.
dry_run=false with auto_remediate=true, approved: the surplus index was dropped, the re-read returned 0 groups and the keyspace kept the differently keyed index, both partial indexes and the primary index.
Approval declined: the flow logged the decline and the surplus index was still there afterward.
Floor refused: with min_indexes_retained above what the keyspace would keep, the run failed at safety_guard and nothing was dropped.
Re-verify failure: a second identical index created during the Pause meant the drop left a duplicate group behind, the re-read saw it and the run failed instead of reporting success.
Failure path: a wrong password failed detect and the errors block posted a Slack alert.

Prerequisites

Couchbase needs the query and index services enabled, otherwise system:indexes and DROP INDEX both fail. The user needs privileges to read system:indexes and to drop indexes on the target keyspace. Use _default for both scope and collection on a bucket that has no named scopes.

Secrets

COUCHBASE_PASSWORD: password for the Couchbase user. It is the default for the password input.
SLACK_WEBHOOK_URL: incoming webhook used by the findings alert and the error alert.

Inputs

connection_string: Couchbase connection string, for example couchbase://host1,host2.
username: Couchbase user with query and index privileges.
bucket: bucket holding the keyspace to inspect.
scope: scope inside the bucket. Defaults to _default.
collection: collection inside the scope. Defaults to _default.
password: user password, defaulting to the COUCHBASE_PASSWORD secret.
dry_run: report only when true. Defaults to true.
auto_remediate: drop the surplus when true and dry_run is false. Defaults to false.
min_indexes_retained: floor on the online index count after the drops. Defaults to 1.

Quick start

Start Couchbase with the data, index and query services, then create a bucket.
Create two indexes on the same key with different names so there is something to find.
Set the COUCHBASE_PASSWORD and SLACK_WEBHOOK_URL secrets in Kestra.
Run with dry_run true to see the groups, the kept name and the surplus names.
Re-run with dry_run false and auto_remediate true, then answer the Pause with approved: true.
Enable the schedule for a weekly safe-mode check.

How to extend

Loop the flow over every keyspace in a bucket by reading the collections first and calling this flow per keyspace as a subflow.
Keep the index with the most recent definition instead of the first name by sorting on your own naming convention in the LETTING clause.
Treat a prefix of a composite index as covered by the longer one, which is a wider definition of redundant than exact key equality.
Add the index num_replica to the group key when you run replicated indexes and want replica counts to differ.
Raise min_indexes_retained on a keyspace that must always keep a known number of indexes.

Find and drop duplicate Couchbase GSI indexes that double every write

Safety

How it works

What you get

Who it's for

Why orchestrate this with Kestra

Tested end to end

Prerequisites

Secrets

Inputs

Quick start

How to extend

Links

Tasks

Orchestrate with Kestra

Share this Blueprint

Create a Blueprint

New to Kestra?

Find and drop duplicate Couchbase GSI indexes that double every write

Safety

How it works

What you get

Who it's for

Why orchestrate this with Kestra

Tested end to end

Prerequisites

Secrets

Inputs

Quick start

How to extend

Links

Tasks

Orchestrate with Kestra

Share this Blueprint

Create a Blueprint

More Infrastructure Blueprints

Renew TLS certificates through ACME, verify them, deploy them and roll back on failure

ADLS World-Writable ACL Sentinel

AI Incident Post-Mortem and Root Cause Generator

ACM Certificate Expiry Compliance Sentinel

Aerospike Namespace Device High Watermark and Eviction Sentinel

Environmental Disaster Early Warning System

More Data Blueprints

Invoice Math Discrepancy Gate with In-Memory DuckDB Reconciliation

Adanos Asset Sentiment Drift Sentinel

Aerospike Namespace Device High Watermark and Eviction Sentinel

Automate Customer Feedback Triage, Sentiment Analysis, and Escalation with Google Gemini and Slack

AI-Powered Inventory Demand Forecasting, Auto-Generate Purchase Orders with OpenAI

Read out an A/B test with a sample ratio check and a guardrail metric

New to Kestra?