ClickHouse Best Practices

Core Principles

• Plan ORDER BY (primary key) before table creation — it is immutable
• Use native data types and minimize storage footprint
• Batch inserts to avoid "too many parts" errors
• Avoid mutations (ALTER UPDATE/DELETE); use engine-level patterns instead
• Use partitioning for data lifecycle, not as a primary query optimization
• Prefer materialized views and projections for recurring aggregations

Schema Design — Primary Key (CRITICAL)

schema-pk-plan-before-creation

Plan ORDER BY before table creation — it is immutable.

ORDER BY defines the primary key and physical sort order of data on disk. Changing it after the fact requires a full data migration (create new table, reindex, swap alias). Wrong choices are expensive to fix.