clickhouse-io

Use the MergeTree engine with PARTITION BY on a date expression like toYYYYMM(date) and ORDER BY your most-filtered columns. Place high-cardinality indexed fields first in the sort key and choose the smallest appropriate data types for compression.

Filter on indexed columns first, avoid SELECT *, and use ClickHouse-specific functions like quantile() instead of percentile calculations. Check system.query_log for queries exceeding duration thresholds and review read_rows and memory_usage metrics.

ReplacingMergeTree deduplicates rows with the same sorting key during background merges, making it suitable for data arriving from multiple sources. Standard MergeTree keeps all inserted rows and is the default choice for append-only analytics data.

Use batch inserts that combine many rows into a single INSERT statement rather than individual row inserts in a loop. For continuous ingestion, use streaming inserts through the ClickHouse client library to pipeline data batches.

Use materialized views with AggregatingMergeTree when you need real-time pre-aggregated metrics like hourly statistics. They maintain aggregate states automatically as raw data arrives, and you query them with merge functions like sumMerge and uniqMerge.