Optimize ClickHouse Analytics and Data Engineering

Covers ClickHouse-specific patterns for high-performance analytics and data engineering on the column-oriented OLAP database, spanning table design, query optimization, data ingestion, materialized views, monitoring, and common analytics query shapes.

Table design covers three MergeTree family engines: plain MergeTree for standard analytics tables (partitioned by month via toYYYYMM(date), ordered by date and a dimension key); ReplacingMergeTree for deduplicating data that may arrive more than once from multiple sources, ordered by a natural key including event ID and timestamp; and AggregatingMergeTree for pre-aggregated metrics, storing AggregateFunction columns (sum, count, uniq) that are queried back with sumMerge/countMerge/uniqMerge rather than plain aggregate functions.

Query optimization patterns include filtering on indexed columns first rather than starting with a non-indexed LIKE clause; using ClickHouse-native aggregation functions like uniq() for approximate distinct counts and quantile(0.95)(...) for percentiles (more efficient than a generic percentile function); and window functions for running totals via sum(volume) OVER (PARTITION BY market_id ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW).

Data insertion patterns contrast batched bulk inserts (building one multi-row INSERT statement from an array of records) against individual per-row inserts in a loop, explicitly flagged as slow and to be avoided, plus a streaming insert pattern for continuous ingestion using the client's .insert().stream() API. Materialized views are shown wired to an AggregatingMergeTree target table, computing hourly stats (sumState, countState, uniqState) automatically as new rows land in the source table, queried the same way as the manually maintained aggregate table.

Performance monitoring covers querying system.query_log for queries over 1000ms in the last hour (with duration, rows/bytes read, and memory usage) and system.parts for per-table disk size and row counts. Common analytics queries are given as working SQL: daily active users via uniq(user_id) grouped by date; retention analysis counting users active at day 0/1/7/30 since signup; a conversion funnel counting view-to-click and click-to-completion rates from staged events; and cohort analysis grouping users by signup month against activity month using dateDiff.

Data pipeline patterns cover a periodic ETL job (extract from Postgres, transform field names/types, bulk-load into ClickHouse on an hourly interval) and a change-data-capture pattern using PostgreSQL's LISTEN/NOTIFY to stream row-level INSERT/UPDATE/DELETE events into ClickHouse as they happen.

Five best-practice areas close it out: partitioning strategy (partition by time, usually month or day, avoid too many partitions); ordering key (most frequently filtered, highest-cardinality columns first, since order affects compression); data types (smallest appropriate integer width, LowCardinality for repeated strings, Enum for categorical data); things to avoid (SELECT *, the FINAL modifier, excessive JOINs, small frequent inserts); and monitoring (query performance, disk usage, merge operations, the slow query log).

Use this skill when designing ClickHouse tables, optimizing analytical queries, setting up bulk or streaming data ingestion, building materialized views for real-time aggregation, or writing common analytics queries (DAU, retention, funnels, cohorts) against event data. It is ClickHouse-specific - the engine choices, SQL functions, and insertion patterns don't transfer directly to row-oriented OLTP databases.

Input is a description of the analytics or data-engineering need (e.g. "deduplicated event table," "hourly rollup with materialized view," "retention query"). Output is ClickHouse DDL/SQL or TypeScript ingestion code following the engine, query, and pipeline patterns above.

Built around ClickHouse's SQL dialect and MergeTree engine family, the Node.js clickhouse client library for TypeScript ingestion code, and PostgreSQL's LISTEN/NOTIFY for the CDC pattern connecting an OLTP source to ClickHouse.

Data engineers and backend developers building analytics pipelines on ClickHouse who need table design, query optimization, ingestion, and materialized-view patterns for high-volume event or trading data.