Optimize ClickHouse Analytics and Data Engineering

ClickHouse Analytics Patterns provides patterns for column-oriented analytical workloads and data engineering. It covers table engines, filtering, aggregations, window functions, bulk ingestion, materialized views, and operational monitoring. The emphasis is designing tables around query patterns and measuring work performed rather than assuming a query is efficient from its appearance.

Table examples use MergeTree for general analytics, ReplacingMergeTree for eventual deduplication, and AggregatingMergeTree for stored aggregate states. Correctness depends on matching column types, state types, units, partitions, and ordering keys. ReplacingMergeTree deduplicates equal full sorting keys within compatible partitions eventually; a timestamp in the key does not deduplicate arbitrary repeated event identifiers, and background merging does not guarantee correct reads.

Use these patterns for analytical tables, time-series summaries, pre-aggregation, or ingestion from another database. They also help investigate slow queries, table sizes, disk consumption, and merge behavior. Predicate order alone is not evidence of index efficiency: inspect plans, read_rows, and read_bytes.

The schemas and queries are independent sketches rather than one runnable application. Validate retention cohorts against a verified signup source or a windowed user minimum; the illustrated per-activity-day minimum is not a true signup cohort. Likewise, event counts do not establish an ordered unique-user or session conversion funnel. Protect query logs and scope ingestion and DDL permissions before applying examples.

Inputs include analytical events or trades, target schemas, expected query filters, and the chosen client version. Query patterns summarize daily activity, totals, distinct users, percentiles, and running totals. Cohort examples group activity relative to signup periods, subject to the correctness checks above. A compact percentile example is:

SELECT
    quantile(0.50)(trade_size) AS median,
    quantile(0.95)(trade_size) AS p95,
    quantile(0.99)(trade_size) AS p99
FROM trades
WHERE created_at >= now() - INTERVAL 1 HOUR;

For ingestion, prefer structured batch inserts over concatenating untrusted values into SQL. Materialized views compute aggregate states for subsequent merge aggregation. Monitoring queries inspect duration, rows and bytes read, memory, active table parts, and modification times. Partition design, ordering, appropriate numeric types, LowCardinality strings, and categorical enums are additional tuning considerations. Avoid unnecessary columns, excessive joins, excessive partitions, and small frequent inserts.

The JavaScript client example uses @clickhouse/client with JSONEachRow. PostgreSQL extraction and notification-driven CDC illustrate pipeline shapes. Older streaming and CDC fragments require adaptation to the same pinned client API and are not interchangeable with the structured client example. Time-based ETL demonstrates extraction, transformation, and loading into ClickHouse.