ClickHouse Certified cheat sheet

ClickHouse is a column-oriented DBMS: each column is stored contiguously, so scans read only referenced columns and compress far better than row stores.

MergeTree-family tables store data as immutable sorted parts; background merges combine parts, so write amplification and part count are core operational concerns.

A sparse primary index stores one mark per index_granularity rows (default 8192), so point lookups are approximate and range scans dominate.

Vectorized execution processes data in blocks (default max_block_size 6554 rows) using SIMD-friendly column arrays rather than per-row interpretation.

Data skipping indexes (min_max, set, bloom_filter, ngrambf_v1, tokenbf_v1) prune granules; they help only when predicates correlate with granule ordering or low-cardinality sets.

ClickHouse favors append-heavy batch inserts; many small inserts create many parts and trigger 'too many parts' throttling.

OLAP workloads assume large scans over few columns with aggregation; OLTP-style single-row updates are expensive and implemented as mutations or ReplacingMergeTree merges.

Compression codecs (LZ4 default, ZSTD, Delta, DoubleDelta, Gorilla, T64) trade CPU for I/O; Delta/DoubleDelta suit monotonic timestamps, Gorilla suits floats.

MergeTree ORDER BY defines the sparse index and part sort order; choose columns used in the most selective frequent filters, low cardinality first is not required but prefix order matters.

PARTITION BY should target a manageable number of partitions (roughly hundreds, not thousands); partitioning by day is common, by high-cardinality IDs is an anti-pattern.

ReplacingMergeTree deduplicates rows with identical sorting key during merges only; use FINAL or argMax for guaranteed dedup at query time.

SummingMergeTree and AggregatingMergeTree pre-aggregate; AggregatingMergeTree stores AggregateFunction states (e.g., uniqState, sumState) read with -Merge combinators.

CollapsingMergeTree uses a sign column (+1/-1) to cancel rows; VersionedCollapsingMergeTree adds a version to keep collapsing correct under out-of-order inserts.

LowCardinality(String) dictionary-encodes low-uniqueness strings and reduces storage and memory; avoid it for high-cardinality columns.

Nullable wraps values with a separate null map and costs performance; prefer a default value or a sentinel when nulls are rare.

Distributed tables are query routing shards; ReplicatedMergeTree uses Keeper for replication metadata and requires a ZooKeeper/Keeper cluster.

ClickHouse SQL is case-sensitive for identifiers and supports non-standard extensions: ARRAY JOIN, LIMIT BY, PREWHERE, FINAL, SAMPLE, WITH TOTALS.

PREWHERE moves a filter before reading all columns; ClickHouse auto-moves predicates, but explicit PREWHERE on the most selective column reduces I/O.

FINAL forces merge-time dedup/collapse at query time; it is expensive on large tables, so prefer pre-aggregation or argMax patterns.

Aggregate combinators: -If (conditional), -Array, -State/-Merge (states), -OrNull/-OrDefault, -Distinct; e.g., countIf(status='error').

Window functions use OVER (PARTITION BY ... ORDER BY ...) and support frame clauses; they run after WHERE and before ORDER BY/LIMIT.

JOIN defaults to ALL (no dedup); use GLOBAL JOIN for distributed queries to avoid sending the right table to every shard.

LIMIT BY returns N rows per group, e.g., SELECT ... ORDER BY ts DESC LIMIT 3 BY user_id.

EXPLAIN and EXPLAIN PIPELINE reveal index usage, PREWHERE pushdown, and whether a filter is applied at the part level.

The fastest bulk path is INSERT INTO ... SELECT FROM file()/s3()/url() or clickhouse-client --query with a large batch; avoid row-by-row inserts.

clickhouse-client and clickhouse-local support --format CSV/JSONEachRow/Parquet/Native; Native format is fastest because it is columnar and typed.

Kafka engine tables act as a queue consumer; pair with a Materialized View writing into a MergeTree for durable storage.

S3 and GCS table functions read external object storage; s3() supports globs and partitioning, and INSERT INTO ... SELECT FROM s3() backfills.

Materialized Views are insert triggers: they transform rows at insert time; they do not read historical data unless POPULATE or a manual backfill is used.

Kafka/MySQL/PostgreSQL engines require named collections or DDL with connection settings; the MySQL engine supports both reads and writes.

Batch size targets: aim for inserts of 10k-100k rows or ~1-10 MB per insert; too-large inserts increase memory, too-small increase part count.

Async inserts (async_insert=1) buffer small client inserts server-side and flush as batches, reducing part creation for high-frequency writers.

Read query_log (system.query_log) for elapsed, read_rows, read_bytes, memory_usage, and ProfileEvents to find slow queries.

Filter on ORDER BY prefix columns to use the primary index; use EXPLAIN indexes=1 to confirm granule pruning.

Use projection or a Materialized View to pre-aggregate hot queries rather than relying on FINAL or large JOINs at query time.

max_threads and max_memory_usage control parallelism and per-query memory; max_bytes_before_external_group_by spills aggregation to disk.

Join order and join_algorithm matter: hash join builds the right table; for large joins consider join_algorithm='partial_merge' or pre-join via dictionaries.

Dictionaries (CREATE DICTIONARY) provide in-memory lookups for dimension enrichment, avoiding JOINs on high-cardinality keys.

Part health: system.parts shows active parts and part sizes; merge pressure from too many parts degrades inserts and queries.

Compression and codec choice affects I/O; ZSTD level 3-7 balances ratio and CPU; Delta codecs help monotonic columns.

Access control uses RBAC: CREATE USER/ROLE, GRANT SELECT ON db.table TO role, and row policies / column masks for fine-grained control.

Authentication: password, LDAP, Kerberos, or SSH keys; TLS is configured at the server level with tcp_port_secure and https_port.

system.replicas and system.replication_queue expose replication lag and stuck queue entries; Keeper (or ZooKeeper) is the coordination layer.

Backups use BACKUP/RESTORE commands to S3, local disk, or another ClickHouse; clickhouse-backup is a common external tool.

Monitoring: system.metrics, system.events, system.asynchronous_metrics, and Prometheus endpoint /metrics for scrapes.

ClickHouse Cloud separates compute and storage; compute scales via replicas and vertical size, storage is object-store backed and shared.

Cloud features: automatic backups, private networking (AWS PrivateLink/GCP PSC), and per-service users; idle services suspend and resume.

Security hardening: restrict default user, disable remote access unless needed, set max_memory_usage and quotas, and audit via system.query_log.

MergeTree parts are immutable: updates/deletes are mutations that rewrite parts asynchronously and can be expensive on large tables.

TTL rules (TTL ts + INTERVAL 30 DAY) move or delete old data by partition, controlling storage growth.

Replication is asynchronous in ReplicatedMergeTree; reads may see stale data on a lagging replica.

ClickHouse supports approximate algorithms: uniq() (HyperLogLog), quantile() (t-digest), and topK() for memory-bounded analytics.

Dictionary engine tables expose external dictionaries as tables for JOIN-free lookups.

Buffer tables batch inserts in memory before flushing to the destination table; they do not survive restart.

File and URL engines read external data for ad-hoc queries but are not durable storage.

Log engine family (Log, TinyLog, StripeLog) is for small local data; it lacks indexes and is not for production analytics.

Array functions: arrayJoin, arrayMap, arrayFilter, groupArray; ARRAY JOIN flattens arrays into rows.

JSON functions: JSONExtractString, JSONExtractInt, or the JSON type for semi-structured data.

SETTINGS clause per query overrides server defaults, e.g., SETTINGS max_threads=4.

FORMAT clause controls output: JSONEachRow, CSVWithNames, Parquet, Native.

clickhouse-local runs SQL on local files without a server, useful for ETL prototyping.

Kafka engine settings: kafka_broker_list, kafka_topic_list, kafka_group_name, kafka_format, kafka_num_consumers.

MySQL/PostgreSQL table functions allow federated queries: SELECT * FROM mysql('host:3306', 'db', 'table', 'user', 'pass').

INSERT ... FORMAT JSONEachRow streams newline-delimited JSON for log ingestion.

system.query_log and system.query_thread_log are the primary profiling sources; enable log_queries=1.

EXPLAIN PIPELINE shows the execution graph; EXPLAIN indexes=1 shows which granules are pruned.

max_bytes_before_external_sort spills ORDER BY to disk; useful for large sorts under memory limits.

Sampling (SAMPLE 0.1) works only on tables with a sampling key in the ORDER BY.

system.clusters and system.distributed_ddl_queue show cluster topology and DDL propagation.

Quotas (CREATE QUOTA) limit queries, rows read, and execution time per user.

Keeper is a lightweight ZooKeeper replacement; embedded Keeper runs inside clickhouse-server for small clusters.

Cloud billing is compute-hours plus storage; scaling replicas increases cost linearly, vertical scaling changes per-replica size.

The primary key (ORDER BY) is not unique; duplicates are allowed and deduplicated only by engine semantics.

Index granularity (index_granularity) sets rows per mark; smaller values improve pruning at the cost of index size.

Column compression is per-column and per-part; codecs are chosen per column in DDL.

ClickHouse is not a full ACID transactional store; it favors high-throughput append and analytical reads.

ORDER BY columns must be a prefix of the sorting key; choose the most selective low-cardinality filter first.

PARTITION BY tuple() disables partitioning; use it when partition count would be excessive.

ReplicatedReplacingMergeTree combines replication and dedup; use it for exactly-once-ish ingestion with retries.

Projections are alternative sort orders or pre-aggregations stored inside a table and selected automatically by the optimizer.

WITH TOTALS adds a totals row to GROUP BY results; WITH ROLLUP and WITH CUBE add subtotals.

HAVING filters after aggregation; WHERE filters before aggregation and can use the primary index.

DISTINCT ON is not supported; use argMax or GROUP BY with any()/max() to pick one row per key.

Subqueries in IN are supported; correlated subqueries have limited support, so use JOINs or dictionaries instead.

Materialized Views with TO target table decouple storage from the view; without TO, the view stores its own data.

Kafka exactly-once is not guaranteed; design idempotent consumers using ReplacingMergeTree or dedup keys.

S3Queue engine ingests new objects from S3 automatically with exactly-once semantics via Keeper.

clickhouse-client --query with stdin piping is the standard bulk load path for CSV/TSV files.

Avoid SELECT *; project only needed columns to reduce read_bytes.

Push filters into PREWHERE; use WHERE on ORDER BY prefix columns.

For high-cardinality GROUP BY, use max_bytes_before_external_group_by to spill.

Use uniq() instead of COUNT(DISTINCT) for large cardinalities to bound memory.

system.parts and system.merges show merge activity; monitor part count and merge backlog.

ALTER TABLE ... FREEZE creates a hardlink snapshot for backup without stopping writes.

Cloud services expose connection details (host, port 9440 TLS) and per-service credentials in the console.

Row-level security uses CREATE ROW POLICY ... FOR SELECT USING (expression) attached to a role.