AI-assisted ClickHouse optimization with MCP

01 The problem

With petabytes spread across many clusters, finding waste by hand (oversized columns, unused tables, poorly compressed data, unindexed query patterns) took days per cluster and rarely happened.

02 What I built

1. 1MCP servers that expose cluster metrics, query logs, and system tables to AI agents in a read-only, safe way.
2. 2Agent skills encoding ClickHouse best practices (schema, codecs, sort keys, projections, LowCardinality) so the agent cites specific rules in its recommendations.
3. 3A fleet-wide storage audit that ranks the top columns per cluster by compressed bytes, measures cardinality with HLL sketches (~1% error) so billion-row partitions don’t hit memory limits, and maps every hot column to the SQL model that produces it.
4. 4A coverage audit that lists tables and columns no dashboard or metrics view uses, turning dead data into concrete drop and TTL candidates.
5. 5Written audit reports with per-cluster remediation plans: type changes, codecs, TTLs, projections, and drops.

03 Impact

A multi-day manual review became a repeatable, agent-driven audit. One pass surfaced single columns holding multiple terabytes, the starting point for cost-cutting work.