ClickHouse Cold Storage S3 Partition Tiering

In high-throughput analytical databases like ClickHouse, keeping multi-year historical datasets on high-speed NVMe block storage creates prohibitive infrastructure costs. ClickHouse supports multi-volume and tiered storage policies, allowing older partitions to be moved to cost-effective cloud object storage (such as Amazon S3, Google Cloud Storage, or MinIO) without changing query syntax.

However, tables without automatic storage policies or manual migration procedures frequently allow historical partitions to linger on expensive primary disks, exhausting local SSD capacity and risking disk-full cluster halts.

This blueprint establishes an automated ClickHouse data lifecycle manager. Operating on a weekly schedule or on-demand, it inspects system.parts to identify partitions on the default local disk that have not been modified within your configured retention window. When cold partitions are detected, it dispatches an actionable Slack digest detailing total gigabytes, part counts, and the precise ALTER TABLE ... MOVE PARTITION statement needed to migrate data to cold storage.

How it works

1. Scheduled Lifecycle Audit: The weekly_storage_tiering trigger (io.kestra.plugin.core.trigger.Schedule) executes every Monday at 03:00 UTC or on-demand via the Kestra UI.
2. Parts Inspection: The audit_tiering_candidates task (io.kestra.plugin.jdbc.clickhouse.Query) queries system.parts, aggregating active parts on the default disk and computing days elapsed since last partition modification.
3. Evaluation Gate: The evaluate_tiering_violations flowable task (io.kestra.plugin.core.flow.If) branches based on whether qualifying cold partitions are discovered.
4. Slack Alert Notification: If candidate partitions exist, notify_tiering_slack (io.kestra.plugin.slack.notifications.SlackIncomingWebhook) posts a structured notification with table name, partition ID, size in gigabytes, and the recommended migration command.
5. Balanced Logging: If all active partitions are within retention limits, log_healthy_tiering records compliant status in execution logs.
6. Audit Manifest: The export_tiering_manifest task records telemetry outputs for capacity planning dashboards.

What you get

• Automated discovery of historical ClickHouse partitions consuming expensive NVMe disk space.
• Visibility into partition sizes, row counts, and modification ages across all MergeTree tables.
• Pre-formatted ALTER TABLE MOVE PARTITION SQL commands in Slack for seamless operator remediation.
• Zero human overhead for continuous ClickHouse disk capacity governance.

Who it is for

• Data Platform Engineers and Database Administrators governing petabyte-scale ClickHouse clusters.
• FinOps teams optimizing cloud infrastructure spend across compute and storage tiers.
• Analytics Engineers designing data retention policies for high-volume event streams.

Why orchestrate this with Kestra

Managing tiered storage manually requires ad-hoc shell scripts and brittle cron configurations that lack observability and access control. Kestra provides declarative, secure orchestration: it executes analytical queries via JDBC, schedules regular audits, conditionally triggers alerting workflows, securely manages credentials, and integrates directly with Slack.

Inputs

Name	Type	Default	Description
clickhouse_url	STRING	jdbc:clickhouse://localhost:8123/default	JDBC connection string targeting the ClickHouse cluster.
retention_days	INT	90	Age threshold in days beyond which unmodified partitions should be tiered to object storage.
target_disk	STRING	s3_cold	Name of the configured cold object storage disk defined in ClickHouse storage_configuration.
slack_channel	STRING	#data-platform	Slack channel destination for storage tiering and capacity alerts.

Expected outputs

• {{ outputs.audit_tiering_candidates.rows }}: Array of partition records containing database, table, partition, size_mb, size_gb, total_rows, active_parts, and days_since_modification.
• {{ outputs.audit_tiering_candidates.size }}: Total number of candidate partitions qualifying for cold storage migration.
• {{ outputs.evaluate_tiering_violations }}: Result of conditional branch evaluation.
• {{ outputs.export_tiering_manifest.value }}: Structured JSON audit manifest recording execution timestamp and qualifying partition count.

Prerequisites

• A ClickHouse instance (version 21.8+) with a configured tiered storage policy or S3 disk defined in config.xml.
• A ClickHouse database user with read privileges on system.parts.
• A Slack Incoming Webhook URL targeting your data platform channel.

Secrets

• CLICKHOUSE_USERNAME: Database username with read access to the system database.
• CLICKHOUSE_PASSWORD: Password for the ClickHouse user.
• SLACK_WEBHOOK_URL: Slack Incoming Webhook endpoint URL used for sending alert notifications.

Quick start

1. Configure CLICKHOUSE_USERNAME, CLICKHOUSE_PASSWORD, and SLACK_WEBHOOK_URL in your Kestra namespace secrets.
2. Import this flow YAML into your Kestra instance.
3. Click Execute in the UI to run an audit of your ClickHouse storage distribution.
4. Review execution outputs to inspect partition ages and cold storage migration recommendations.

Common pitfalls and troubleshooting

• Disk Name Configuration: ClickHouse disk names must match the disk identifiers defined in /etc/clickhouse-server/config.d/storage.xml. Ensure inputs.target_disk corresponds to a configured disk or volume.
• In-Progress Mutations: Tables currently executing heavy mutations (system.mutations) may temporarily prevent partition moves until the mutation finishes.
• Active vs Inactive Parts: The query filters WHERE active = 1 to ensure only current consolidated data parts are evaluated, ignoring older parts pending background garbage collection.

How to extend

• Add an automated migration task executing ALTER TABLE ... MOVE PARTITION ... TO DISK directly from Kestra for trusted environments.
• Add io.kestra.plugin.notifications.mail.MailSend inside the then: block to deliver weekly capacity reports to infrastructure directors.
• Implement tiered retention by automatically dropping partitions older than 365 days using DROP PARTITION.

Links

• Kestra ClickHouse Plugin Documentation: https://kestra.io/plugins/plugin-jdbc-clickhouse
• Kestra Slack Plugin Documentation: https://kestra.io/plugins/plugin-notifications
• ClickHouse Managing Partitions Documentation: https://clickhouse.com/docs/en/sql-reference/statements/alter/partition