gavinmroy/clickhouse-optimizer repository overview

⁠ClickHouse Optimizer

A robust CLI tool for incrementally optimizing all partitions of ClickHouse tables. Optimize large tables efficiently by processing partitions sequentially with comprehensive progress tracking and merge monitoring.

⁠🚀 Why ClickHouse Optimizer?

ClickHouse tables can accumulate many small parts over time, degrading query performance. The OPTIMIZE TABLE command typically processes all partitions at once, which can:

• Overwhelm system resources on large tables
• Block other operations during optimization
• Fail on timeout without completing any work
• Provide no visibility into progress

If you generate a file with a list of partitions to optimize, you can run the OPTIMIZE TABLE command manually. However, this approach is error-prone, failing when the OPTIMIZE TABLE command times out, and can be time-consuming.

This tool solves these problems by:

• ✅ Processing partitions sequentially to manage resource usage
• ✅ Monitoring merge completion with real-time progress bars
• ✅ Graceful timeout handling - continues with next partition if one times out
• ✅ Rich console output with ETA calculations

⁠📦 Installation

⁠pip

pip install clickhouse-optimizer

⁠uvx (no install needed)

uvx clickhouse-optimizer --help

⁠Docker

docker pull gavinmroy/clickhouse-optimizer

⁠🔧 Quick Start

⁠Basic Usage

# Optimize all partitions of a table
clickhouse-optimizer --host ch.example.com --user admin --password secret --database mydb mytable

# Run with verbose logging to see detailed progress
clickhouse-optimizer --verbose --host ch.example.com --user admin --password secret --database mydb mytable

⁠Environment Variables

Set connection parameters via environment variables:

export CLICKHOUSE_HOST=ch.example.com
export CLICKHOUSE_USER=admin
export CLICKHOUSE_PASSWORD=secret
export CLICKHOUSE_DATABASE=mydb

clickhouse-optimizer mytable

⁠Docker

# Using CLI arguments
docker run --rm gavinmroy/clickhouse-optimizer \
  --host ch.example.com --user admin --password secret \
  --database mydb mytable

# Using environment variables
docker run --rm \
  -e CLICKHOUSE_HOST=ch.example.com \
  -e CLICKHOUSE_USER=admin \
  -e CLICKHOUSE_PASSWORD=secret \
  -e CLICKHOUSE_DATABASE=mydb \
  gavinmroy/clickhouse-optimizer mytable

⁠Advanced Options

# Custom timeouts and polling intervals
clickhouse-optimizer \
  --optimize-timeout 7200 \
  --poll-interval 10 \
  mytable

⁠📋 Command Reference

⁠🏗️ How It Works

1. Discovery: Queries system.parts to find all active partitions
2. Sequential Processing: Optimizes one partition at a time
3. Merge Monitoring: Tracks merge progress via system.merges
4. Progress Display: Shows completion status with Rich progress bars
5. Fault Tolerance: Continues with next partition if one times out

⁠🎯 Use Cases

⁠Large Production Tables

Perfect for tables with hundreds of partitions where full optimization would be too resource-intensive.

⁠Maintenance Windows

Ideal for scheduled optimization during low-traffic periods with predictable progress tracking.

⁠Performance Recovery

Quickly improve query performance on tables with many small parts without system overload.

⁠Safe Operations

Monitor optimization progress with detailed logging and progress tracking.

⁠🛠️ Development

⁠Setup Development Environment

git clone https://github.com/gmr/clickhouse-optimizer
cd clickhouse-optimizer
uv sync

# Install pre-commit hooks
uv run pre-commit install

⁠Code Quality

# Run linting
uv run ruff check

# Format code
uv run ruff format

# Run all pre-commit hooks
uv run pre-commit run --all-files

⁠Testing

# Run tests with coverage
uv run coverage run -m pytest

# Show coverage report
uv run coverage report

# Generate HTML coverage report
uv run coverage html

⁠Build

# Build package distributions
uv build

⁠📄 License

BSD 3-Clause License - see LICENSE⁠ file for details.

⁠🤝 Contributing

Contributions are welcome! Please:

1. Fork the repository
2. Create a feature branch
3. Make your changes with tests
4. Run the pre-commit hooks
5. Submit a pull request

⁠📊 Architecture

This tool follows a clean, modular architecture:

• Settings: Pydantic-based configuration with automatic CLI parsing
• Optimizer: Core business logic with progress tracking and error handling
• CLI: Minimal entry point with Rich console formatting

The optimizer discovers partitions, processes them sequentially, and monitors merge completion through ClickHouse system tables.