ClickHouse Certified flashcards

32 cards. Read the term and the definition here. The deck at the top of this page is optional.

Column-oriented storage

ClickHouse stores each column contiguously, so scans read only referenced columns and compress better than row stores, which is ideal for wide-table analytical aggregation.

Sparse primary index

One index mark is stored per index_granularity rows (default 8192). Lookups are approximate and range scans over ORDER BY prefixes are fast.

Immutable parts

MergeTree data is written as immutable sorted parts; background merges combine them. Updates and deletes become asynchronous mutations that rewrite parts.

Vectorized execution

Queries process data in column blocks (default max_block_size 6554) using SIMD-friendly arrays rather than per-row interpretation, raising throughput on large scans.

Data skipping index

A secondary index (min_max, set, bloom_filter, ngrambf_v1) that prunes granules. It helps only when predicates correlate with ordering or low-cardinality sets.

ORDER BY sorting key

Defines the sparse index and physical sort order of parts. Filtering on a prefix of these columns lets ClickHouse skip granules and read far less data.

PARTITION BY guidance

Partitioning splits parts by expression (commonly month or day). Keep partition counts in the hundreds; partitioning by high-cardinality IDs creates too many parts.

ReplacingMergeTree dedup

Rows with identical sorting keys are deduplicated only during merges. Use FINAL or argMax for guaranteed dedup at query time.

AggregatingMergeTree states

Stores AggregateFunction states such as uniqState or sumState. Read them with -Merge combinators (uniqMerge, sumMerge) to combine pre-aggregated partial results.

LowCardinality(String)

Dictionary-encodes low-uniqueness strings, cutting storage and memory. Avoid it for high-cardinality columns, where the dictionary overhead outweighs the benefit.

Nullable cost

Nullable wraps values with a separate null map and adds overhead. Prefer a default value or sentinel when nulls are rare in a column.

CollapsingMergeTree sign

A sign column (+1/-1) cancels rows during merges. VersionedCollapsingMergeTree adds a version so collapsing stays correct under out-of-order inserts.

PREWHERE clause

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

FINAL modifier

Forces merge-time dedup or collapsing at query time. It is expensive on large tables, so prefer pre-aggregation or argMax patterns instead.

Aggregate combinators

Suffixes extend aggregate functions: -If for conditions, -Array for arrays, -State/-Merge for states, -OrNull/-OrDefault for null handling, and -Distinct.

LIMIT BY clause

Returns N rows per group, for example SELECT ... ORDER BY ts DESC LIMIT 3 BY user_id, giving top-N-per-key without window functions.

ARRAY JOIN

Flattens array columns into rows, turning one row with an array into many rows so array elements can be aggregated or filtered in SQL.

GLOBAL JOIN

In distributed queries, sends the right-hand table to every shard once instead of per-shard subqueries, avoiding duplicated network transfer and memory use.

EXPLAIN indexes=1

Shows which granules the primary index prunes for a query. Use it to confirm that filters hit the ORDER BY prefix and actually reduce reads.

Native format

A columnar, typed binary format used by clickhouse-client and clickhouse-local. It is the fastest bulk load and export path because no parsing is needed.

Kafka engine table

Acts as a queue consumer with broker, topic, group, and format settings. Pair it with a Materialized View writing into a MergeTree for durable storage.

Materialized View trigger

A Materialized View transforms rows at insert time only. It does not process historical data unless POPULATE is used or a manual backfill is run.

Async inserts

With async_insert=1, small client inserts are buffered server-side and flushed as batches, reducing part creation for high-frequency writers.

S3 table function

Reads external object storage with globs and partitioning, enabling backfills such as INSERT INTO ... SELECT FROM s3(...) without a staging cluster.

Batch insert sizing

Aim for inserts of roughly 10k-100k rows or 1-10 MB. Too-small inserts create many parts; too-large inserts raise memory pressure.

system.query_log

The primary profiling source, recording elapsed time, read_rows, read_bytes, memory_usage, and ProfileEvents per query when log_queries is enabled.

External aggregation spill

max_bytes_before_external_group_by and max_bytes_before_external_sort spill GROUP BY and ORDER BY state to disk when memory limits would otherwise be exceeded.

Dictionaries

In-memory key-value lookups created with CREATE DICTIONARY. They enrich dimension data without JOINs on high-cardinality keys, cutting query memory and time.

system.parts

Lists active parts and their sizes. Rising part counts and merge backlog signal insert pressure that degrades both ingestion and query latency.

Codec selection

LZ4 is the default; ZSTD balances ratio and CPU at levels 3-7; Delta and DoubleDelta suit monotonic timestamps, Gorilla suits floats.

RBAC in ClickHouse

Access control uses CREATE USER and CREATE ROLE with GRANT SELECT ON db.table. Row policies and column masks add fine-grained filtering.

BACKUP and RESTORE

Server commands that write or read backups to S3, local disk, or another ClickHouse. ALTER TABLE ... FREEZE creates a hardlink snapshot without stopping writes.