clickhouse

Follows universal SQL style from sql-skill (lowercase, snake_case, trailing commas, CTEs). Exception: ClickHouse built-in functions are case-sensitive camelCase (countIf, toStartOfHour, arrayJoin) — never lowercase them. Keywords and identifiers stay lowercase/snake_case.

Philosophy

ClickHouse is a columnar OLAP engine. Different rules than OLTP:

• Denormalization is correct — pre-join at insert time, avoid runtime JOINs
• Batch everything — never single-row inserts (10K+ rows per batch)
• Select only needed columns — each column is a separate file, select * reads everything
• Approximate is often good enough — uniq() over uniqExact(), quantile over exact percentiles
• Sort key is everything — data is physically ordered by order by, choose it for your query patterns
• Partitioning is for lifecycle — partition by month for TTL/deletion, not for query performance
• Immutable data model — design for append-only, use ReplacingMergeTree for mutations

Table Engines

MergeTree (Default)

create table events (
  event_id uuid default generateUUIDv4(),
  event_type LowCardinality(String),
  user_id UInt64,
  properties JSON,  -- 25.3+; keep String when the payload is opaque (never field-queried)
  created_at DateTime64(3) default now64(3)
)
engine = MergeTree()
partition by toYYYYMM(created_at)
order by (event_type, user_id, created_at)
ttl created_at + interval 1 year;

Engine Selection

ReplacingMergeTree (Mutable State)

create table users (
  user_id UInt64,
  email String,
  name String,
  updated_at DateTime64(3)
)
engine = ReplacingMergeTree(updated_at)
order by user_id;

-- read latest version (deduplicates at query time)
select * from users final where user_id = 123;

-- admin-only full rewrite; never routine — rely on final at query time or background merges
optimize table users final;

Data Types

Nullable is expensive. Every Nullable(T) column stores a separate bitmask file. Use defaults:

-- good: empty string instead of null
email String default '',

-- bad: separate null bitmap, slower scans
email Nullable(String)

Sort Key Design

The order by clause defines physical data order AND primary index. The single most important design decision.

-- query: "events for user X in time range"
order by (user_id, created_at)

-- query: "events by type in time range"
order by (event_type, created_at)

-- query: "events in time range across all users"
order by (created_at)

Rules:

1. Low-cardinality columns first — better compression
2. Columns from your where clause, in order of selectivity
3. Time column usually last — for range queries within the filtered set

Materialized Views (Real-Time Aggregation)

ClickHouse materialized views are insert triggers — they transform data as it arrives.

-- source: raw events
create table raw_events (
  event_type LowCardinality(String),
  user_id UInt64,
  amount Decimal(18, 2),
  created_at DateTime64(3)
) engine = MergeTree()
order by (event_type, created_at);

-- target: pre-aggregated hourly stats
create table hourly_stats (
  hour DateTime,
  event_type LowCardinality(String),
  event_count AggregateFunction(count),
  total_amount AggregateFunction(sum, Decimal(18, 2)),
  unique_users AggregateFunction(uniq, UInt64)
) engine = AggregatingMergeTree()
order by (event_type, hour);

-- materialized view: transforms on insert
create materialized view hourly_stats_mv to hourly_stats as
select
  toStartOfHour(created_at) as hour,
  event_type,
  countState() as event_count,
  sumState(amount) as total_amount,
  uniqState(user_id) as unique_users
from raw_events
group by hour, event_type;

-- query: merge partial aggregates
select
  hour,
  event_type,
  countMerge(event_count) as events,
  sumMerge(total_amount) as total,
  uniqMerge(unique_users) as users
from hourly_stats
group by hour, event_type
order by hour;

Approximate Functions

Faster than exact. Often good enough for analytics.

-- approximate distinct count (adaptive sampling; recommended over uniqExact/uniqCombined in almost all cases)
select uniq(user_id) as approx_users from events

-- exact count (slower)
select uniqExact(user_id) as exact_users from events

-- quantiles (reservoir sampling, 8192 reservoir — non-deterministic)
-- use quantileTDigest for accuracy on large sets, quantileExact when determinism matters
select
  quantile(0.5)(response_time) as p50,
  quantile(0.95)(response_time) as p95,
  quantile(0.99)(response_time) as p99
from requests

-- multiple quantiles in one pass over one shared state
select quantiles(0.5, 0.95, 0.99)(response_time) as percentiles
from requests

Conditional Aggregation (-If Combinators)

select
  toStartOfDay(created_at) as day,
  count() as total,
  countIf(status = 'completed') as completed,
  sumIf(amount, status = 'completed') as completed_revenue,
  uniqIf(user_id, status = 'completed') as paying_users
from orders
group by day
order by day

Any aggregate function supports the -If suffix: countIf, sumIf, avgIf, uniqIf, quantileIf, etc.

Array Functions

First-class array support — powerful for analytics.

-- aggregate into array
select user_id, groupArray(product_id) as products
from purchases group by user_id

-- expand array to rows
select user_id, arrayJoin(tags) as tag from users

-- transform arrays
select arrayMap(x -> x * 2, [1, 2, 3]) as doubled
select arrayFilter(x -> x > 2, [1, 2, 3, 4, 5]) as filtered

-- set operations
select arrayIntersect([1,2,3], [2,3,4]) as common

-- has / hasAll / hasAny
select * from articles where hasAny(tags, ['rust', 'performance'])

argMax / argMin

Get a column's value at the row where another column is max/min. Elegant alternative to subqueries.

-- latest status per user (no subquery, no window function)
select
  user_id,
  argMax(status, updated_at) as latest_status,
  argMax(email, updated_at) as latest_email,
  max(updated_at) as last_updated
from user_events
group by user_id

-- cheapest product per category
select
  category,
  argMin(product_name, price) as cheapest_product,
  min(price) as lowest_price
from products
group by category

JOINs in ClickHouse

JOINs work but are expensive. Design to minimize them.

-- right table loaded into memory by default
-- put the SMALLER table on the RIGHT side
select e.event_type, u.name, count()
from events as e
inner join users as u on u.id = e.user_id
group by e.event_type, u.name

Prefer (in order):

1. Denormalize at insert time — best performance
2. Dictionaries for dimension lookups — loaded in memory
3. Subqueries with in for filtering — where user_id in (select id from users where ...)
4. JOINs — last resort for large-large joins

Dictionaries

Fast key-value lookups for dimension tables. Loaded into memory.

create dictionary user_dict (
  id UInt64,
  name String,
  email String
)
primary key id
source(clickhouse(table 'users'))
layout(hashed())
lifetime(min 300 max 600);

-- avoids JOIN entirely
select
  event_type,
  dictGet('user_dict', 'name', user_id) as user_name,
  count() as events
from events
group by event_type, user_name

Insert Patterns

Always batch: 10K+ rows per insert. Never single-row inserts in a loop.

-- insert from select (ETL pattern)
insert into daily_stats
select
  toDate(created_at) as day,
  event_type,
  count() as event_count
from events
where created_at >= today() - 1
group by day, event_type;

Async Inserts (High-Frequency Small Inserts)

When clients can't batch, let the server buffer:

insert into events settings async_insert = 1, wait_for_async_insert = 1
values ('click', 1, now());

wait_for_async_insert = 0 (fire-and-forget) risks silent data loss — errors only surface at flush and nothing is acknowledged durable. Buffer tables are the legacy alternative, recommended only in rare cases (no dedup on replicated targets, data loss on crash).

TTL (Data Lifecycle)

-- auto-delete old data
create table logs (
  message String,
  created_at DateTime
) engine = MergeTree()
order by created_at
ttl created_at + interval 90 day;

-- tiered storage before deletion
ttl created_at + interval 30 day to volume 'cold',
    created_at + interval 365 day delete;

-- column-level TTL (null out PII after retention period)
alter table users modify column email String
  ttl created_at + interval 30 day;

Deletes & Mutations

-- occasional row deletes (GDPR, corrections): lightweight delete, *MergeTree family
delete from events where user_id = 123;

-- heavy last-resort mutation: rewrites affected parts
alter table events update status = 'void' where order_id = 42;

-- bulk lifecycle: drop whole partitions or use TTL — never bulk lightweight deletes
alter table events drop partition '202605';

Lightweight-deleted rows are only masked (hidden _row_exists column) until a merge removes them. On tables with projections it throws by default — set lightweight_mutation_projection_mode = 'drop' or 'rebuild'.

Projections

Alternative sort orders stored alongside the table. Query optimizer picks the best one automatically.

create table events (
  event_type LowCardinality(String),
  user_id UInt64,
  created_at DateTime64(3)
) engine = MergeTree()
order by (event_type, created_at);

-- projection: same data, different sort order
alter table events add projection events_by_user (
  select * order by (user_id, created_at)
);

alter table events materialize projection events_by_user;

-- queries filtering by user_id automatically use the projection
select * from events where user_id = 123;

Data Skipping Indexes

Lightweight secondary access paths for filters off the sort key — try before a projection (which copies the data).

alter table events add index idx_trace trace_id type bloom_filter granularity 4;
alter table events materialize index idx_trace;  -- required for pre-existing data

Verify granule pruning with explain indexes = 1 select ....

explain pipeline

explain pipeline
select event_type, count()
from events
where created_at > today() - 7
group by event_type
order by count() desc;

Per-Query Performance Settings

select ...
settings
  max_threads = 8,
  max_memory_usage = 10000000000,
  max_bytes_before_external_group_by = 5000000000;

Comment Doctrine

Comments are for the person reading and reviewing the SQL. Say why a filter, join, or CTE exists, and anything that is not obvious. Write the reason as a plain sentence — no WHY: label.

Keep the work plan out of comments: no phases, plan steps, ticket numbers, or words like "for now", "temporary", or "later". Migrations attract this rot the most. Those notes go stale and someone has to find and delete them. Unfinished work belongs in plan.md.

Non-trivial queries, views, migrations, and SQL functions get a header comment above them: one line saying what it is for, plus the cost driver. Do not write big-O on SQL — the query planner picks the plan, so an O(...) bound would be a lie. Name the real driver instead: memory limit, group by cardinality, join build-side size, or parts/partitions scanned. This header is the SQL analogue of the big-O header in the code skills.

-- daily revenue rollup feeding the finance dashboard.
-- cost driver: group by cardinality (day x event_type, ~30 x small) and parts scanned for the date range.
insert into daily_stats
select
  toDate(created_at) as day,
  event_type,
  sumIf(amount, status = 'completed') as revenue  -- only completed orders count toward revenue
from events
where created_at >= today() - 30
group by day, event_type;

Editing counts too. When you change a query, migration, or function, keep the comments already there, fix any your change made wrong, and add the missing header, cost note, or reason. Do this only for SQL you add or change, not for nearby SQL you did not touch.

Before Committing (Self-Review)

Schema

• Sort key matches primary query pattern
• Low-cardinality columns first in sort key
• LowCardinality(String) for columns with < ~10K distinct values
• Nullable avoided — use defaults instead
• Partition by month (lifecycle), not for query performance
• TTL set for data retention policy
• Engine chosen intentionally (MergeTree, Replacing, Aggregating)

Queries

• Only needed columns selected (never select *)
• final used with ReplacingMergeTree when deduplication needed
• Approximate functions where exact isn't required
• -If combinators for conditional aggregation
• JOINs minimized — dictionaries or denormalization preferred
• argMax/argMin for latest-value patterns instead of subqueries

Inserts

• Batch size >= 10K rows per insert
• async_insert = 1, wait_for_async_insert = 1 for high-frequency small inserts
• No single-row insert loops

Materialized Views

• -State / -Merge combinators used correctly
• Target table uses AggregatingMergeTree
• Source → MV → target pipeline tested end-to-end: failing assertion first — insert fixture rows, assert countMerge/uniqMerge against hand-computed values (red), wire the MV (green), then mutate a fixture row to confirm the assertion can fail (vacuous check)

Performance

• explain indexes = 1 (granule pruning) and explain pipeline reviewed for non-trivial queries
• Skipping index, then projection, considered for queries off the sort key
• LowCardinality on string columns with low distinct count