ClickHouse Parts Explosion Optimize Guard with Kestra

id: clickhouse-parts-explosion-optimize-guard
namespace: company.team

inputs:
  - id: clickhouse_url
    type: STRING
    defaults: jdbc:clickhouse://localhost:8123/default
    description: ClickHouse JDBC URL for the server that holds the tables. Example
      jdbc:clickhouse://clickhouse:8123/default.

  - id: use_secret_auth
    type: BOOL
    defaults: true
    description: Leave true in production so the connection reads
      CLICKHOUSE_USERNAME and CLICKHOUSE_PASSWORD from secrets. Set false only
      to pass credentials inline in a disposable test environment.

  - id: clickhouse_username
    type: STRING
    defaults: default
    description: Username used only when use_secret_auth is false. Ignored in production.

  - id: clickhouse_password
    type: STRING
    defaults: ""
    description: Password used only when use_secret_auth is false. Ignored in production.

  - id: database
    type: STRING
    defaults: default
    description: Database whose partitions are counted.

  - id: table
    type: STRING
    defaults: ""
    description: Optional single table to restrict the check to. Leave empty to
      check every table in the database.

  - id: parts_threshold
    type: INT
    defaults: 150
    description: A partition is a merge target once its active part count reaches
      this number. Keep it well under the server parts_to_throw_insert ceiling
      that the flow reads on every run. ClickHouse 24.8 ships 3000 there. Older
      versions shipped 300 and a table often carries its own lower value.

  - id: max_partitions
    type: INT
    defaults: 5
    description: Cap on how many partitions one run merges, highest part count
      first. OPTIMIZE FINAL rewrites a whole partition, so this bounds the IO a
      single run can cause.

  - id: dry_run
    type: BOOL
    defaults: true
    description: When true, report the exploded partitions with their part counts
      and byte sizes without merging anything.

  - id: auto_remediate
    type: BOOL
    defaults: false
    description: When true and dry_run is false, run OPTIMIZE TABLE ... PARTITION
      ... FINAL behind an approval gate, then re-count active parts.

variables:
  group_sql: |
    SELECT
      database AS database,
      table AS table,
      partition AS partition,
      partition_id AS partition_id,
      count() AS part_count,
      sum(bytes_on_disk) AS bytes_on_disk,
      sum(rows) AS row_count
    FROM system.parts
    WHERE active = 1
      AND database = '{{ inputs.database }}'
      AND ('{{ inputs.table }}' = '' OR table = '{{ inputs.table }}')
    GROUP BY database, table, partition, partition_id
    HAVING count() >= {{ inputs.parts_threshold }}
    ORDER BY part_count DESC, table ASC, partition ASC
  verify_sql: |
    SELECT
      database AS database,
      table AS table,
      partition AS partition,
      partition_id AS partition_id,
      count() AS part_count,
      sum(bytes_on_disk) AS bytes_on_disk,
      sum(rows) AS row_count
    FROM system.parts
    WHERE active = 1
      AND database = '{{ inputs.database }}'
      AND ('{{ inputs.table }}' = '' OR table = '{{ inputs.table }}')
    GROUP BY database, table, partition, partition_id
    HAVING count() > 1
    ORDER BY part_count DESC, table ASC, partition ASC

triggers:
  - id: hourly_parts_explosion_check
    type: io.kestra.plugin.core.trigger.Schedule
    disabled: true
    cron: "17 * * * *"
    description: Hourly parts sweep. Disabled by default and pinned to safe mode so
      it reports without merging until an operator enables it.
    inputs:
      dry_run: true
      auto_remediate: false

tasks:
  - id: ceiling
    type: io.kestra.plugin.jdbc.clickhouse.Query
    description: Read the server parts_to_throw_insert and parts_to_delay_insert
      values from system.merge_tree_settings, so the report states the real
      ceiling an INSERT hits rather than assuming the documented default.
    url: "{{ inputs.clickhouse_url }}"
    username: "{{ inputs.use_secret_auth ? secret('CLICKHOUSE_USERNAME') :
      inputs.clickhouse_username }}"
    password: "{{ inputs.use_secret_auth ? secret('CLICKHOUSE_PASSWORD') :
      inputs.clickhouse_password }}"
    fetchType: FETCH
    sql: "SELECT name AS name, value AS value FROM system.merge_tree_settings WHERE
      name IN ('parts_to_throw_insert', 'parts_to_delay_insert') ORDER BY name"

  - id: census
    type: io.kestra.plugin.jdbc.clickhouse.Query
    description: Count every partition in scope once, so the log can say how many
      partitions are over the threshold even when max_partitions caps what this
      run merges.
    url: "{{ inputs.clickhouse_url }}"
    username: "{{ inputs.use_secret_auth ? secret('CLICKHOUSE_USERNAME') :
      inputs.clickhouse_username }}"
    password: "{{ inputs.use_secret_auth ? secret('CLICKHOUSE_PASSWORD') :
      inputs.clickhouse_password }}"
    fetchType: FETCH
    sql: |
      SELECT
        count() AS partitions_scanned,
        countIf(part_count >= {{ inputs.parts_threshold }}) AS partitions_over,
        max(part_count) AS max_part_count,
        sum(part_count) AS active_parts_total
      FROM
      (
        SELECT count() AS part_count
        FROM system.parts
        WHERE active = 1
          AND database = '{{ inputs.database }}'
          AND ('{{ inputs.table }}' = '' OR table = '{{ inputs.table }}')
        GROUP BY database, table, partition_id
      )

  - id: detect
    type: io.kestra.plugin.jdbc.clickhouse.Query
    description: Read active parts only from system.parts, grouped by table and
      partition. Keeps the partitions at or above the threshold. Highest part
      count first, capped by max_partitions.
    url: "{{ inputs.clickhouse_url }}"
    username: "{{ inputs.use_secret_auth ? secret('CLICKHOUSE_USERNAME') :
      inputs.clickhouse_username }}"
    password: "{{ inputs.use_secret_auth ? secret('CLICKHOUSE_PASSWORD') :
      inputs.clickhouse_password }}"
    fetchType: FETCH
    sql: "{{ render(vars.group_sql) }} LIMIT {{ inputs.max_partitions }}"

  - id: plan
    type: io.kestra.plugin.core.output.OutputValues
    description: Build the merge target list once, with each partition's active part
      count and byte size, so the log, the alert, the approval gate and the
      failure message all quote the same numbers.
    values:
      targets_json: >-
        {{ outputs.detect.rows | jq('map({database, table, partition,
        partition_id, part_count:(.part_count|tonumber),
        bytes:(.bytes_on_disk|tonumber), row_count:(.row_count|tonumber)})') |
        first | toJson }}
      target_count: >-
        {{ outputs.detect.rows | jq('length') | first }}
      targeted_names: >-
        {{ outputs.detect.rows | jq('map(.database+"."+.table+" partition
        "+(.partition|tostring)+" ["+(.partition_id|tostring)+"]:
        "+(.part_count|tostring)+" active parts, "+(.bytes_on_disk|tostring)+"
        bytes, "+(.row_count|tostring)+" rows") | join("; ")') | first }}
      targeted_bytes: >-
        {{ outputs.detect.rows | jq('map(.bytes_on_disk|tonumber) | add // 0') |
        first }}
      throw_ceiling: >-
        {{ outputs.ceiling.rows | jq('map(select(.name ==
        "parts_to_throw_insert") | .value) | first // "unknown"') | first }}
      delay_ceiling: >-
        {{ outputs.ceiling.rows | jq('map(select(.name ==
        "parts_to_delay_insert") | .value) | first // "unknown"') | first }}

  - id: log_detect
    type: io.kestra.plugin.core.log.Log
    message: |
      ClickHouse {{ inputs.database }}: {{ outputs.census.rows[0].partitions_scanned }} partition(s) in scope holding {{ outputs.census.rows[0].active_parts_total }} active part(s). Largest partition has {{ outputs.census.rows[0].max_part_count }} active part(s) against a parts_to_throw_insert ceiling of {{ outputs.plan.values.throw_ceiling }} and a parts_to_delay_insert ceiling of {{ outputs.plan.values.delay_ceiling }}.
      At or above the {{ inputs.parts_threshold }} part threshold: {{ outputs.census.rows[0].partitions_over }} partition(s). This run takes up to {{ inputs.max_partitions }}: {{ outputs.plan.values.target_count }} selected, {{ outputs.plan.values.targeted_bytes }} bytes to rewrite.
      Targets: {{ outputs.plan.values.targeted_names }}
      Mode: dry_run={{ inputs.dry_run }} auto_remediate={{ inputs.auto_remediate }}

  - id: guard
    type: io.kestra.plugin.core.flow.If
    condition: "{{ outputs.detect.size > 0 }}"
    then:
      - id: findings_alert
        type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
        description: Alert on every detection, in dry run and in remediate mode, so a
          human sees the climbing part count while there is still headroom under
          the insert ceiling.
        url: "{{ secret('SLACK_WEBHOOK_URL') }}"
        payload: |
          {"text": "ClickHouse parts explosion guard: {{ outputs.census.rows[0].partitions_over }} partition(s) in {{ inputs.database }} at or above {{ inputs.parts_threshold }} active parts, ceiling parts_to_throw_insert={{ outputs.plan.values.throw_ceiling }}. This run targets {{ outputs.plan.values.target_count }} of them ({{ outputs.plan.values.targeted_bytes }} bytes to rewrite): {{ outputs.plan.values.targeted_names }}. dry_run={{ inputs.dry_run }} auto_remediate={{ inputs.auto_remediate }}."}

      - id: mode
        type: io.kestra.plugin.core.flow.If
        condition: "{{ inputs.auto_remediate and not inputs.dry_run and
          (outputs.plan.values.target_count | number) > 0 }}"
        then:
          - id: approval_gate
            type: io.kestra.plugin.core.flow.Pause
            description: >-
              Hold the run until a human approves the merge. OPTIMIZE FINAL
              rewrites every active part of a partition into one, so on a large
              partition it is heavy disk read and write and it takes real time.
              The byte size of each target is in the log and the Slack alert
              above, so the cost is visible before anyone approves.
            pauseDuration: PT2H
            behavior: FAIL
            onResume:
              - id: approved
                type: BOOL
                defaults: false
                description: Set true to merge the listed partitions with OPTIMIZE FINAL.
                  Anything else leaves them alone.

          - id: apply
            type: io.kestra.plugin.core.flow.If
            condition: "{{ outputs.approval_gate.onResume.approved ?? false }}"
            then:
              - id: remediate_partitions
                type: io.kestra.plugin.core.flow.Loop
                description: Remediation loop. One OPTIMIZE per partition with concurrencyLimit
                  1, because two concurrent FINAL merges on one table contend
                  for the same merge pool and the same disk and both run slower
                  than they would in turn.
                concurrencyLimit: 1
                values: "{{ outputs.plan.values.targets_json }}"
                tasks:
                  - id: optimize_one
                    type: io.kestra.plugin.jdbc.clickhouse.Queries
                    description: Merge one partition with OPTIMIZE TABLE ... PARTITION ID ... FINAL.
                      PARTITION ID takes the raw partition id from system.parts,
                      so the statement works whatever the partition key
                      expression is. allowFailure so a rejected or skipped merge
                      does not stop the pass and the re-count stays the real
                      gate.
                    allowFailure: true
                    url: "{{ inputs.clickhouse_url }}"
                    username: "{{ inputs.use_secret_auth ? secret('CLICKHOUSE_USERNAME') :
                      inputs.clickhouse_username }}"
                    password: "{{ inputs.use_secret_auth ? secret('CLICKHOUSE_PASSWORD') :
                      inputs.clickhouse_password }}"
                    transaction: false
                    sql: "OPTIMIZE TABLE {{ fromJson(item.value).database }}.{{
                      fromJson(item.value).table }} PARTITION ID '{{
                      fromJson(item.value).partition_id }}' FINAL;"

                  - id: optimize_log
                    type: io.kestra.plugin.core.log.Log
                    message: "Issued OPTIMIZE TABLE {{ fromJson(item.value).database }}.{{
                      fromJson(item.value).table }} PARTITION ID '{{
                      fromJson(item.value).partition_id }}' FINAL over {{
                      fromJson(item.value).part_count }} active part(s), {{
                      fromJson(item.value).bytes }} bytes."

              - id: reverify
                type: io.kestra.plugin.jdbc.clickhouse.Query
                description: Re-count active parts per partition. OPTIMIZE is best effort, so it
                  can return without merging when a merge is already running on
                  the partition or when a setting blocks it. This second read is
                  the gate, not the state of the OPTIMIZE task.
                url: "{{ inputs.clickhouse_url }}"
                username: "{{ inputs.use_secret_auth ? secret('CLICKHOUSE_USERNAME') :
                  inputs.clickhouse_username }}"
                password: "{{ inputs.use_secret_auth ? secret('CLICKHOUSE_PASSWORD') :
                  inputs.clickhouse_password }}"
                fetchType: FETCH
                sql: "{{ render(vars.verify_sql) }}"

              - id: verify_plan
                type: io.kestra.plugin.core.output.OutputValues
                description: Compare the re-count against the merge targets. A target still at
                  or above the threshold is a failure. A partition that was
                  never targeted is listed separately as proof the pass left it
                  alone.
                values:
                  still_json: >-
                    {{ {'targets': outputs.plan.values.targets_json,
                    'remaining': outputs.reverify.rows} | toJson | jq('(.targets
                    | if type == "string" then fromjson else . end |
                    map(.table+"::"+(.partition_id|tostring))) as $tg |
                    (.remaining | map((.table+"::"+(.partition_id|tostring)) as
                    $k | select($tg | index($k))) | map(select(.part_count >= '
                    ~ inputs.parts_threshold ~ ')))') | first | toJson }}
                  still_names: >-
                    {{ {'targets': outputs.plan.values.targets_json,
                    'remaining': outputs.reverify.rows} | toJson | jq('(.targets
                    | if type == "string" then fromjson else . end |
                    map(.table+"::"+(.partition_id|tostring))) as $tg |
                    (.remaining | map((.table+"::"+(.partition_id|tostring)) as
                    $k | select($tg | index($k))) | map(select(.part_count >= '
                    ~ inputs.parts_threshold ~ ')) | map(.table+" partition
                    "+(.partition|tostring)+" still holds
                    "+(.part_count|tostring)+" active parts") | join("; "))') |
                    first }}
                  target_after_names: >-
                    {{ {'targets': outputs.plan.values.targets_json,
                    'remaining': outputs.reverify.rows} | toJson | jq('(.targets
                    | if type == "string" then fromjson else . end |
                    map(.table+"::"+(.partition_id|tostring))) as $tg |
                    (.remaining | map((.table+"::"+(.partition_id|tostring)) as
                    $k | select($tg | index($k))) | map(.table+" partition
                    "+(.partition|tostring)+": "+(.part_count|tostring)+" active
                    parts left") | join("; "))') | first }}
                  untouched_names: >-
                    {{ {'targets': outputs.plan.values.targets_json,
                    'remaining': outputs.reverify.rows} | toJson | jq('(.targets
                    | if type == "string" then fromjson else . end |
                    map(.table+"::"+(.partition_id|tostring))) as $tg |
                    (.remaining | map((.table+"::"+(.partition_id|tostring)) as
                    $k | select(($tg | index($k)) == null)) | map(.table+"
                    partition "+(.partition|tostring)+":
                    "+(.part_count|tostring)+" active parts") | join("; "))') |
                    first }}

              - id: verify_result
                type: io.kestra.plugin.core.flow.If
                condition: "{{ (outputs.verify_plan.values.still_json | jq('length') | first) >
                  0 }}"
                then:
                  - id: remediation_failed
                    type: io.kestra.plugin.core.execution.Fail
                    errorMessage: >-
                      {{ outputs.verify_plan.values.still_json | jq('length') |
                      first }} targeted partition(s) are still at or above {{
                      inputs.parts_threshold }} active parts after the merge
                      pass in {{ inputs.database }}: {{
                      outputs.verify_plan.values.still_names }}. The connecting
                      user may lack the OPTIMIZE privilege, a background merge
                      may already hold the partition, a part may be too large
                      for the merge pool or new inserts arrived faster than the
                      merge retired the old parts.
                else:
                  - id: remediated
                    type: io.kestra.plugin.core.log.Log
                    message: |
                      Merged and verified. {{ outputs.plan.values.target_count }} target partition(s) before: {{ outputs.plan.values.targeted_names }}.
                      After: none of them is at or above {{ inputs.parts_threshold }} active parts. Targets still holding more than one part: {{ outputs.verify_plan.values.target_after_names }}
                      Partitions this pass did not target, unchanged: {{ outputs.verify_plan.values.untouched_names }}
            else:
              - id: not_approved
                type: io.kestra.plugin.core.log.Log
                message: "Approval not granted, so nothing was merged. {{
                  outputs.plan.values.target_count }} partition(s) still hold {{
                  inputs.parts_threshold }} or more active parts in {{
                  inputs.database }}: {{ outputs.plan.values.targeted_names }}."
        else:
          - id: report_only
            type: io.kestra.plugin.core.log.Log
            message: "Report only (dry_run={{ inputs.dry_run }}, auto_remediate={{
              inputs.auto_remediate }}). {{ outputs.plan.values.target_count }}
              partition(s) would be merged with OPTIMIZE FINAL, rewriting {{
              outputs.plan.values.targeted_bytes }} bytes: {{
              outputs.plan.values.targeted_names }}. No change made."
    else:
      - id: clean
        type: io.kestra.plugin.core.log.Log
        message: "No partition in {{ inputs.database }} holds {{ inputs.parts_threshold
          }} or more active parts. Largest is {{
          outputs.census.rows[0].max_part_count }} against a
          parts_to_throw_insert ceiling of {{ outputs.plan.values.throw_ceiling
          }}. Nothing to merge."

errors:
  - id: alert_failure
    type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
    description: Post to Slack on any failure, including a connection error, an
      OPTIMIZE error or targeted partitions still over the threshold after a
      merge pass.
    url: "{{ secret('SLACK_WEBHOOK_URL') }}"
    payload: |
      {"text": "ClickHouse parts explosion guard FAILED for {{ inputs.database }} (execution {{ execution.id }}). A connection error, an OPTIMIZE error or targeted partitions still over the part threshold after a merge pass. Check the run."}

ClickHouse merges small parts in the background, but a table fed by many small inserts can gain parts faster than merges retire them. Once a single partition reaches parts_to_throw_insert every INSERT into it is rejected with TOO_MANY_PARTS and ingestion stops dead. That ceiling is a setting rather than a constant: ClickHouse 24.8 ships 3000, older versions shipped 300 and a table often carries a much lower value of its own, so this guard reads the live number out of system.merge_tree_settings instead of assuming one. The warning sign is a partition whose active part count is climbing toward that ceiling. It sits in system.parts long before the outage. This guard reads the active part count per partition, merges the partitions that are over a threshold, then counts again and fails the run if a target is still over.

This blueprint was created by zkasuran.

Safety

Read-only by default. dry_run is true and auto_remediate is false, so a plain run reports and never merges.
A merge happens only when auto_remediate is true and dry_run is false. It then waits for a human to resume the Pause with approved set true. An unanswered gate reads as not approved.
OPTIMIZE ... FINAL is not free. It rewrites every active part of the partition into one, so on a large partition it is heavy disk read and write and it takes real time. That is why it sits behind the Pause. It is also why every report lists each partition's byte size next to its part count, so the cost is visible before anyone approves.
max_partitions caps how many partitions one run rewrites, highest part count first, so a database with hundreds of exploded partitions cannot turn one approval into unbounded IO.
The remediation Loop runs with concurrencyLimit: 1. Two concurrent FINAL merges on one table contend for the same merge pool and the same disk, so the partitions are merged in turn.
OPTIMIZE is best effort. It can return success without merging anything when a merge already holds the partition or when a setting blocks it, so the task reaching success proves nothing. The second read of system.parts is the gate. A partition still over the threshold fails the run.

How it works

ceiling (io.kestra.plugin.jdbc.clickhouse.Query) reads parts_to_throw_insert and parts_to_delay_insert from system.merge_tree_settings, so the report quotes the real ceiling this server enforces.
census (io.kestra.plugin.jdbc.clickhouse.Query) counts every partition in scope in one aggregate: partitions scanned, partitions over the threshold, the largest part count and the total active parts.
detect (io.kestra.plugin.jdbc.clickhouse.Query) reads system.parts with active = 1, groups by table and partition, keeps the partitions whose part count is at or above parts_threshold and takes the worst max_partitions of them with their byte size and row count.
plan (io.kestra.plugin.core.output.OutputValues) builds the target list and the human readable summary once, so the log, the Slack alert, the approval gate and the failure message all quote the same numbers.
log_detect (io.kestra.plugin.core.log.Log) prints the headroom: the largest partition's part count against the server ceiling, how many partitions are over the threshold and how many bytes this run would rewrite.
guard (io.kestra.plugin.core.flow.If) branches on the detection. With nothing over the threshold, clean logs the largest part count and the run ends. Otherwise findings_alert posts to Slack on every detection, dry run included.
mode (io.kestra.plugin.core.flow.If) enters the remediation branch only when auto_remediate is true, dry_run is false and at least one partition is over the threshold. Otherwise report_only lists what would be merged and stops.
approval_gate (io.kestra.plugin.core.flow.Pause) holds the run for a human. remediate_partitions (io.kestra.plugin.core.flow.Loop, concurrencyLimit: 1) then runs OPTIMIZE TABLE ... PARTITION ID ... FINAL through io.kestra.plugin.jdbc.clickhouse.Queries, one partition at a time. PARTITION ID takes the raw id from system.parts, so the statement works whatever the partition key expression is.
reverify re-counts active parts per partition and verify_result fails the run through io.kestra.plugin.core.execution.Fail if any target is still at or above parts_threshold. On success remediated logs the before and after counts plus the partitions the pass did not target, unchanged.

What you get

A detector that reads the one number that decides whether ingestion keeps working: active parts per partition, from system.parts, next to the server's own parts_to_throw_insert ceiling.
A fix for the partition that is filling up, not a whole-table rewrite. One OPTIMIZE TABLE ... PARTITION ... FINAL per target, in turn.
The cost of the fix up front. Every report carries each partition's byte size and row count, because FINAL rewrites all of it.
A real verification step. The run passes only when the second count shows every target back under the threshold.
Slack alerts on the findings and on any failure.

Who it's for

Data platform engineers running ClickHouse behind a stream or a micro-batch loader, where small inserts are the normal shape of the write path.
On-call engineers who have seen TOO_MANY_PARTS take ingestion down and want the warning hours earlier.
DBAs who want targeted partition merges on a schedule, with an approval step, instead of an untracked OPTIMIZE run by hand at midnight.

Why orchestrate this with Kestra

ClickHouse will tell you the part count if you ask, but it has no scheduler for the question, no threshold, no approval step and no way to prove an OPTIMIZE actually merged anything. Kestra adds each piece. It runs the sweep on a schedule, compares the count against a threshold and the server's own ceiling, caps how many partitions one pass rewrites, holds the destructive statement behind dry-run plus human approval, merges one partition at a time, then re-counts system.parts so a merge that silently did nothing fails the run instead of passing. It posts a Slack alert the moment a step fails.

Tested end to end

Run on Kestra 2.0.5 against a ClickHouse 24.8 server. A MergeTree table was seeded by many single-row inserts into one partition until its active part count passed the threshold, with a second partition and a second table left far below it.

Detector can fail: the same flow and inputs returned 0 targets against the seeded-clean database and the exploded partition against the degraded one, both counts confirmed with clickhouse-client against system.parts.
Dry run reported the exploded partition with its part count and byte size, alerted and merged nothing.
Remediate with approval merged the partition. The re-count showed it back to a single active part. The second partition and the second table were untouched.
Payoff proven: with parts_to_throw_insert lowered on the table, an INSERT into the exploded partition was rejected with TOO_MANY_PARTS, then the same INSERT was accepted after the merge pass.
Declining the approval gate left every part in place.
A user without the OPTIMIZE privilege turned the merge into a no-op, the re-count found the target still over the threshold and the run failed and alerted.
An unreachable host failed the first query and fired the Slack alert.

Prerequisites

A ClickHouse server reachable over the JDBC HTTP interface. The connecting user must be able to read system.parts and system.merge_tree_settings and run OPTIMIZE TABLE on the target tables.

Secrets

CLICKHOUSE_USERNAME: ClickHouse user, read when use_secret_auth is true.
CLICKHOUSE_PASSWORD: password for that user.
SLACK_WEBHOOK_URL: used by findings_alert and the errors block.
In Kestra OSS, provide each secret as a base64-encoded environment variable prefixed SECRET_.

Inputs

Name	Type	Default	Description
`clickhouse_url`	STRING	`jdbc:clickhouse://localhost:8123/default`	ClickHouse JDBC URL.
`use_secret_auth`	BOOL	`true`	Read credentials from secrets. Set false only for inline test credentials.
`database`	STRING	`default`	Database whose partitions are counted.
`table`	STRING	`""`	Optional single table filter. Empty checks every table.
`parts_threshold`	INT	`150`	Active part count at which a partition becomes a merge target.
`max_partitions`	INT	`5`	Cap on partitions merged per run, worst first.
`dry_run`	BOOL	`true`	Report without merging.
`auto_remediate`	BOOL	`false`	Merge behind the approval gate when dry_run is false.

Quick start

Set CLICKHOUSE_USERNAME, CLICKHOUSE_PASSWORD and SLACK_WEBHOOK_URL as namespace secrets.
Import this flow.
Run it with database pointed at a ClickHouse database. The default dry run reports the partitions nearing the insert ceiling.
To merge, run again with dry_run false and auto_remediate true, then resume the paused execution with approved true.

How to extend

Set parts_threshold from the server value this flow already reads, for example two thirds of parts_to_throw_insert, so the threshold follows a tuned cluster instead of a fixed number.
Read a per-table override of parts_to_throw_insert out of system.tables.engine_full and compare each partition against its own table's ceiling rather than the server default.
Point the detection at clusterAllReplicas(...) to count parts across every node of a cluster in one pass.
Swap OPTIMIZE for an insert-side fix: raise the batch size on the loader. Or turn on async_insert with a wait, so the parts stop arriving one row at a time.
Fail the run as soon as detection finds anything, for a read-only drift check with no remediation.

ClickHouse Parts Explosion Optimize Guard

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?

ClickHouse Parts Explosion Optimize Guard

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 Data Blueprints

ChromaDB Vector Collection Snapshot and AWS S3 Disaster Recovery Pipeline

Score change requests on risk and build the CAB agenda, pre-approving the low-risk ones

Citus Colocation Violation Guard

Watch Cited Papers for Retractions, Expressions of Concern and Corrections via Crossref

Citus Reference Table Replica Guard

Build a Chat-With-Your-Data System Using Elasticsearch and OpenAI (RAG)

More Infrastructure Blueprints

Kafka ACL Overly Broad Grant Audit

Kafka Client Quota Guard

Kafka Consumer Lag Sentinel

Kafka ACL Baseline Restore Guard

Self-heal Kafka Connect connectors with a restart budget and escalation

Kafka Consumer Group Dead-Letter Rebalance and Offset Rewind Guard

New to Kestra?