clickhouse-io

Use the MergeTree engine with a time-based partition key like toYYYYMM(date) and an ORDER BY clause placing frequently filtered columns first. Choose the smallest appropriate data types and consider LowCardinality for repeated string values.

Filter on indexed columns first, avoid SELECT *, and use ClickHouse-specific functions like quantile and uniq instead of generic equivalents. Check system.query_log for queries exceeding duration thresholds and review partition pruning behavior.

MergeTree is the standard engine for append-only analytical data, while ReplacingMergeTree deduplicates rows with the same sorting key during background merges. Use ReplacingMergeTree when data may arrive from multiple sources with potential duplicates.

Yes, use an ETL pattern that extracts rows from PostgreSQL, transforms them into columnar-friendly structures, and batch inserts into ClickHouse. For ongoing sync, implement change data capture using PostgreSQL LISTEN/NOTIFY to stream updates.

ClickHouse is optimized for batch inserts because each insert creates data parts that require background merges. Insert rows in large batches or use streaming inserts rather than looping single-row INSERT statements.

Use materialized views with AggregatingMergeTree to maintain pre-aggregated metrics like hourly sums and unique counts in real time. Query them with merge functions such as sumMerge and uniqMerge instead of recomputing aggregates from raw events.