ClickHouse Analytics skill for Codex

Done well means: the sorting key matches the filters dashboards actually use, so typical queries read a small fraction of granules; inserts arrive in batches; rollups are computed by materialized views with correct aggregate states; and every number a dashboard shows has been checked against a raw-table query.

Inspect first

1. Read AGENTS.md; find the client (clickhouse-client, @clickhouse/client, clickhouse-connect, Go driver), whether this is ClickHouse Cloud (SharedMergeTree, managed replication) or self-hosted (Replicated* engines, clusters), and the server version (SELECT version()).
2. Get the existing DDL: SHOW CREATE TABLE db.events.
3. Collect the top queries by cost:

SELECT normalized_query_hash, any(query) AS sample, count() AS runs,
       quantile(0.95)(query_duration_ms) AS p95_ms,
       sum(read_rows) AS rows_read, formatReadableSize(sum(read_bytes)) AS bytes_read
FROM system.query_log
WHERE type = 'QueryFinish' AND event_time > now() - INTERVAL 1 DAY AND query_kind = 'Select'
GROUP BY normalized_query_hash ORDER BY sum(query_duration_ms) DESC LIMIT 20;

Engine choice

Data	Engine
Append-only events, logs, metrics	MergeTree
Rows updated by re-inserting a newer version (CDC, entity snapshots)	ReplacingMergeTree(version)
Pre-aggregated rollups with uniques, quantiles, averages	AggregatingMergeTree fed by a materialized view
Rollups of plain sums and counts only	SummingMergeTree (or AggregatingMergeTree for flexibility)
Small dimension lookups	Dictionary (CREATE DICTIONARY) over a table or external source

On self-hosted clusters use the Replicated variants; on Cloud the engine name maps to SharedMergeTree automatically.

Table design

CREATE TABLE analytics.events
(
    tenant_id   UInt32,
    event_date  Date DEFAULT toDate(event_time),
    event_time  DateTime64(3, 'UTC'),
    event_type  LowCardinality(String),
    user_id     UInt64,
    session_id  UUID,
    country     LowCardinality(String),
    revenue     Decimal(18, 4) DEFAULT 0,
    props       Map(LowCardinality(String), String) CODEC(ZSTD(3))
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_date)
ORDER BY (tenant_id, event_type, event_date, user_id)
TTL event_date + INTERVAL 13 MONTH DELETE
SETTINGS index_granularity = 8192;

Rules:

• ORDER BY is the most important decision. Put the columns most queries filter on by equality first, ordered from low to high cardinality, then the time column, then a column that groups related rows (user, session). The primary key is a sparse index over this order: one mark per 8 192 rows.
• PARTITION BY is for data management (dropping old months, moving to cold storage), not for query speed. Monthly or daily partitions; never by high-cardinality columns. Thousands of partitions mean too many parts and slow merges.
• LowCardinality(String) for columns with up to roughly tens of thousands of distinct values.
• Avoid Nullable unless null is meaningful; use defaults. Nullable adds a column and slows filters.
• Codecs: Delta or DoubleDelta + ZSTD for monotonic timestamps and counters, Gorilla for float gauges, ZSTD(3) for large strings. Measure with system.columns compressed sizes.
• Use the smallest integer type that fits; DateTime (seconds) unless milliseconds matter.

Inserts

• Insert in batches: thousands to hundreds of thousands of rows per insert, at most around one insert per second per table. Each insert creates a part; tiny frequent inserts cause “Too many parts” errors.
• Many small producers: enable async inserts so the server batches them: SETTINGS async_insert = 1, wait_for_async_insert = 1 (keep wait on so errors surface).
• Make inserts idempotent for retries: same batch, same content; insert deduplication works on identical blocks for replicated/Cloud tables within the deduplication window.

Updates and deduplication

• ReplacingMergeTree deduplicates only during background merges, at an unknown time. Queries must handle duplicates: SELECT ... FROM t FINAL (correct, costlier; recent versions parallelize it well) or argMax(col, version) grouped by key.
• The dedup key is the ORDER BY tuple, so it must uniquely identify the entity.
• Use an is_deleted column with ReplacingMergeTree(version, is_deleted) for CDC deletes.
• Lightweight DELETE FROM t WHERE ... marks rows hidden; ALTER TABLE ... UPDATE/DELETE rewrites parts (mutations) and is heavy. Both on large production tables need explicit user confirmation; check system.mutations for progress.
• Dropping data by partition (ALTER TABLE t DROP PARTITION 202401) is instant and the right retention tool; it is irreversible, so confirm with the user.

Materialized views

An incremental materialized view is an insert trigger: it transforms each inserted block and writes to a target table. It does not see existing rows, and it does not react to updates or deletes in the source.

CREATE TABLE analytics.daily_stats
(
    tenant_id  UInt32,
    day        Date,
    event_type LowCardinality(String),
    events     AggregateFunction(count),
    users      AggregateFunction(uniq, UInt64),
    revenue    AggregateFunction(sum, Decimal(18, 4))
)
ENGINE = AggregatingMergeTree
ORDER BY (tenant_id, day, event_type);

CREATE MATERIALIZED VIEW analytics.daily_stats_mv TO analytics.daily_stats AS
SELECT tenant_id, event_date AS day, event_type,
       countState() AS events, uniqState(user_id) AS users, sumState(revenue) AS revenue
FROM analytics.events
GROUP BY tenant_id, day, event_type;

-- Query with -Merge combinators and GROUP BY (rows are partially aggregated)
SELECT day, countMerge(events) AS events, uniqMerge(users) AS users, sumMerge(revenue) AS revenue
FROM analytics.daily_stats
WHERE tenant_id = 42 AND day >= today() - 30
GROUP BY day ORDER BY day;

• Always GROUP BY when reading Aggregating/Summing tables; rows merge eventually, not immediately.
• Backfill historical data into the target with INSERT INTO daily_stats SELECT ...State() from the source over bounded date ranges, after creating the MV, avoiding double counting the overlap window.
• Refreshable materialized views (REFRESH EVERY 1 HOUR) re-run a full query on a schedule; use them for joins or logic that incremental views cannot express, on moderate data sizes.
• Every MV adds work to every insert into the source. Keep the number per table small.

Speeding up reads

1. Check what the query reads: EXPLAIN indexes = 1 SELECT ... shows parts and granules selected by the primary key, partition pruning and skip indexes.
2. If the filter does not match the sorting key prefix, options in order:
   • Rewrite the query to filter on the leading key columns.
   • Add a projection with a different ORDER BY (ALTER TABLE ... ADD PROJECTION p (SELECT * ORDER BY user_id) then MATERIALIZE PROJECTION p); costs storage and insert time.
   • Add a skip index for selective needle-in-haystack filters (INDEX idx_session session_id TYPE bloom_filter GRANULARITY 4), then materialize it.
   • Build a rollup MV for dashboard queries.
3. JOINs: put the smaller table on the right side (it is loaded into memory), filter both sides before joining, or use a dictionary with dictGet for dimension lookups.
4. Select only needed columns; ClickHouse is columnar and SELECT * reads everything.
5. Use approximate functions where acceptable: uniq instead of uniqExact, quantileTDigest.

Gotchas

• Changing ORDER BY on an existing table is not possible beyond appending new columns; plan for create-new-table plus INSERT ... SELECT and a swap with EXCHANGE TABLES.
• count() on ReplacingMergeTree without FINAL over-counts until merges finish.
• Time zones: store UTC; convert in queries with toTimeZone. Date functions use the server zone unless told otherwise.
• OPTIMIZE TABLE ... FINAL forces merges and is expensive; never schedule it as the dedup strategy.
• The ClickHouse SQL dialect differs from Postgres: GROUP BY aliases are allowed, integer division with / returns Float64, intDiv for integers.

Verification

• EXPLAIN indexes = 1 before and after: granules read drop for the target queries.
• system.query_log p95 duration and read_rows for the query hash drop.
• Rollup totals equal raw-table totals for a sample tenant and day range.
• SELECT table, count() FROM system.parts WHERE active GROUP BY table stays stable (no part explosion) under production insert rates.

Output

**Tables:** <DDL summary: engine, ORDER BY, PARTITION BY, TTL>
**Why this ORDER BY:** <top query filters it serves>
**Rollups:** <MV -> target, aggregate states>
**Insert path:** <batch size / async_insert settings>
**Measured:** granules read and p95 before/after for <queries>; rollup vs raw check result