---
title: "AI doesn’t always generate perfect ClickHouse schemas (yet)"
description: "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."
---

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

[Clickhouse](https://yomu.fyi/company/clickhouse) · Al Brown · Mar 13, 2026

**Type:** Explainer

## 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.

## Context

LLMs often apply broadly useful ClickHouse advice to specific workloads without understanding their operational trade-offs or scale. The resulting schemas may introduce unnecessary partitions, codecs, projections, materialized views, or other complexity, while advanced cases such as multi-terabyte systems, ingestion joins, and complex deduplication require scenario-specific judgment.

## Approach / What changed

Start with a basic table, a sensible ORDER BY, default compression, and no partitions, projections, or materialized views unless they are needed. Run and profile the actual workload, identify a specific bottleneck, add one optimization at a time, and measure its impact on queries and ingestion. Question the LLM's design choices and consult an experienced engineer for complex or high-scale architectures.

## Takeaways

- 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.
- 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.
- 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.

**Tags:** [Architecture](https://yomu.fyi/topic/architecture), [LLMs](https://yomu.fyi/topic/llm), [Performance](https://yomu.fyi/topic/performance), [Scalability](https://yomu.fyi/topic/scalability)

- Source: [Clickhouse](https://clickhouse.com/blog/ai-generated-clickhouse-schemas-mistakes-and-advice)
- Source URL: https://clickhouse.com/blog/ai-generated-clickhouse-schemas-mistakes-and-advice
- Ingested by Yomu: 2026-08-28T01:22:12.244Z

[Read original post](https://clickhouse.com/blog/ai-generated-clickhouse-schemas-mistakes-and-advice)