AI doesn’t always generate perfect ClickHouse schemas (yet)

Summary

LLMs can generate syntactically valid ClickHouse schemas whose advanced choices are poorly matched to a workload. The post examines recurring pitfalls including query-driven partitioning, per-column codecs, premature projections, materialized-view sprawl, JSON configurations, and forced deduplication with OPTIMIZE TABLE ... FINAL. It explains that partitions primarily support data management, incremental materialized views add work to every insert, and ReplacingMergeTree deduplicates during background merges rather than at insertion. The recommended method is to begin with a simple MergeTree or ReplacingMergeTree, a well-chosen ORDER BY, default compression, and no optional structures, then measure production behavior before adding complexity or seeking human expertise for high-scale, interconnected designs.

Takeaways

1. 1

   ClickHouse partitioning is primarily a data-management feature for lifecycle and retention operations; using a high-cardinality field can cause part explosion and affect inserts, merges, TTLs, and monitoring.

2. 2

   Each incremental materialized view runs on every insert to its source table, so adding views to solve isolated slow queries can eventually reduce ingestion performance and create dependency sprawl.

3. 3

   ReplacingMergeTree performs deduplication during background merges. OPTIMIZE TABLE ... FINAL can create massively oversized, irreversible parts; FINAL in SELECT queries provides deduplicated reads before merges complete.