Glossary

87 terms · ClickHouse

AggregatingMergeTree: a MergeTree engine that generalizes SummingMergeTree to arbitrary aggregate functions by storing, per matching key, a combination of aggregate function states rather than a finished value. Requires a -State combinator on insert (e.g. uniqState) and the matching -Merge combinator on read (e.g. uniqMerge) — real complexity in exchange for pre-reducing any aggregate, not just sum.
Apache Druid: a real-time OLAP database that splits ingestion, storage, and query serving into separate, independently-scaled process types (Coordinator, Overlord, Broker, Historical, MiddleManager) coordinated through a required ZooKeeper quorum, built around serving a large, fixed set of queries to a large, concurrent audience. Its own documentation recommends avoiding query-time joins in favor of pre-joining data before ingestion or using lookups.
Apache Pinot: a real-time OLAP database, originally built inside LinkedIn to power member-facing product features (e.g. "Who Viewed My Profile"), architected around Controller, Broker, and Server processes coordinated via Apache Helix on ZooKeeper. Distinguished from ClickHouse and Druid by native, primary-key-based upsert tables for same-instant row correction, and by routing SQL joins through a separate multi-stage query engine rather than its original single-stage design.
AST (Abstract Syntax Tree): the structured, tree-shaped representation of a query's grammar produced by parsing; captures what a query says without checking whether the tables, columns, or functions it references actually exist.
BigQuery: a serverless cloud data warehouse with an on-demand pricing model that charges per amount of data a query scans, with no compute unit to provision or size; also offers capacity-based "editions" pricing for teams that want predictable costs instead. Like Snowflake, its real strength beyond pricing is ecosystem and governance maturity, not real-time ingestion or interactive latency.
Block: the unit of data ClickHouse's execution engine operates on: a chunk of column values for many rows processed together, rather than one row at a time; the concrete mechanism behind vectorized execution.
bloom filter index: a data skipping index type that tests, per block, whether an exact value might be present, using a compact probabilistic structure; unlike minmax or set, it doesn't depend on the column's values clustering — it helps whenever the filtered value is rare enough to be genuinely absent from most blocks.
Cardinality: the number of distinct values that appear in a specific column, as opposed to a property of the dataset as a whole. Different columns on the same rows can have very different cardinality (e.g., status_code vs. transaction_id).
checksums.txt: a MergeTree part's manifest file, recording a checksum for every other file in the part; what makes a corrupted part detectable (CHECKSUM_DOESNT_MATCH) rather than silently wrong. *(Precise scope [VERIFY]'d in mergetree-on-disk-format — see that lesson's verify report.)*
ClickBench: a public OLAP benchmark created and hosted by ClickHouse itself (not a third party), testing a specific workload shape: roughly 100 million rows in a single flat table, ~43 queries, no JOINs. A useful, credible data point for that workload shape specifically — not a general verdict on which database is "better" for workloads shaped differently (concurrent, multi-table, or continuously ingesting).
ClickHouse Keeper: a ClickHouse-native, ZooKeeper-protocol-compatible coordination service, built on the RAFT consensus algorithm, that ReplicatedMergeTree tables use to agree on replica metadata and pending operations — not a copy of table data itself. Like any RAFT-based system it needs a quorum (a majority of its own nodes) to keep working; a 3-node Keeper cluster tolerates one node failing.
CollapsingMergeTree: a MergeTree engine that retracts one specific prior contribution to a row (rather than replacing the whole row) via a matched pair of rows sharing a sorting key: a state row (Sign = 1) and a cancel row (Sign = -1), identical in every other column. On merge, matched pairs are removed. Requires the cancel row to be inserted after its corresponding state row for the same key ("strictly consecutive insertion") — violating that ordering can leave the pair uncollapsed and produce a silently wrong aggregate, not just a stale duplicate.
Columnar storage: a physical storage layout that groups values by column rather than by row, so a query can read only the columns it needs.
Compact format: a MergeTree part's physical layout when it's small enough to fall under the wide-format thresholds: every column's data consolidated into a single shared file, trading column-selective read efficiency for better spatial locality on small, frequent inserts.
Compressed block (on-disk): the physical unit ClickHouse writes to a column's .bin file: a fixed header (16-byte CityHash128 checksum, 4-byte raw size, 4-byte uncompressed size, 1-byte codec/mode identifier) followed by the compressed bytes. The mode byte identifies the codec directly (0x02 none, 0x82 LZ4, 0x90 ZSTD) — readable by hand, independent of any system table.
Data lake: cheap, flexible storage for raw data in open file formats, with no schema required up front; structure is deferred until the data is actually queried.
Data skipping index: a secondary summary structure attached to groups of granules in a MergeTree table, letting a query rule out entire blocks of granules for columns outside the ORDER BY prefix, without changing the table's physical sort order. Can produce false positives (reading a block that turns out not to match) but never false negatives (skipping a block that would have matched).
Data warehouse: an analytics database built around a scheduled (batch) load pipeline: data is extracted, transformed into a defined structure, and loaded on a cadence, in exchange for deep, structured, ad-hoc query support over large historical datasets. Examples: Snowflake, BigQuery, Redshift.
Deep storage: the durable, off-node storage layer (e.g. S3, HDFS, or local disk configured for the role) that Druid and Pinot persist committed segments to; query-serving processes (Historicals in Druid, Servers in Pinot) download segments from deep storage rather than owning the durable copy themselves.
Dictionary (external dictionary): a key -> attributes structure ClickHouse loads into memory from an external or in-database source and refreshes on a schedule, queried with dictGet() instead of a JOIN. A third alternative alongside denormalization and JOINs for enriching a fact table from a dimension, best suited to dimensions small and slow-changing enough to tolerate its refresh staleness window.
Distributed table: the ClickHouse engine that shards a table across multiple nodes: it stores no data of its own, routing each INSERT's rows to a specific shard by a sharding key and fanning every SELECT out across all shards, merging partial results back together. A JOIN against a Distributed table without GLOBAL silently joins each shard only against its own local slice of the other side — a correctness bug, not a performance one.
DuckDB: an embedded, in-process OLAP database: it runs inside a host process (a Python script, a notebook, an application binary) rather than as a separate server, with no client-server network hop between a query and its data. Columnar and vectorized like ClickHouse, but architected for a single machine rather than a distributed cluster — strong for exploratory, embedded, or single-analyst workloads; not built for many concurrent writers or continuous high-volume ingestion against shared, live data.
Embedded (in-process) database: a database engine that runs inside the same process as the application using it, with no separate server to install or connect to over a network, as opposed to a client-server database like ClickHouse. Trades multi-user concurrency and distributed scale for zero operational overhead and fast local data access.
EXPLAIN: a diagnostic SQL command that shows the output of a stage of query interpretation (by default, the query plan) instead of executing the query.
FINAL: a query modifier that forces ClickHouse to fully merge the relevant data — running the same deduplication, collapsing, or summing logic a background merge would — before returning a result. Applies to ReplacingMergeTree, SummingMergeTree, AggregatingMergeTree, CollapsingMergeTree, and VersionedCollapsingMergeTree alike; gives a same-instant correctness guarantee at the cost of real, per-query compute.
Format (input/output format): the serialization scheme ClickHouse uses to read or write rows for a specific INSERT or SELECT (e.g. CSV, JSONEachRow, Native, Parquet), independent of how the table stores that data on disk.
Generic exclusion search: the algorithm a MergeTree primary index falls back to, in place of binary search, when a query filters on a column that is part of a compound sorting key but isn't its first column. Its effectiveness depends on the cardinality of the preceding key column: when that predecessor has cardinality similar to the filtered column's, consecutive index marks rarely share the predecessor's value, so the algorithm can't rule out most marks and ends up selecting nearly all of them — the primary key existing doesn't guarantee it prunes a given filter.
Granule: the smallest unit of data ClickHouse reads at a time, a fixed number of rows; the unit the sparse primary index and data-skipping indexes operate on.
Hash join: ClickHouse's default JOIN algorithm: builds an in-memory hash table from one side of the join (the smaller table, as of ClickHouse 24.12's automatic reordering), then streams the other side past it, probing the hash table row by row. The build side must fit comfortably in memory; non-memory-bound alternatives (full sorting merge, partial merge, grace hash) exist and spill to disk, trading join speed for a bounded memory footprint.
Ingestion: the process and rate of new data entering a system.
Lakehouse: a data lake with warehouse-like capabilities (schema enforcement, transactions, faster queries) added on top of its open, cheap storage.
Latency: how long a single operation takes, start to finish (e.g., how long one query took to return). Distinct from throughput, and often in tension with it — batching can raise throughput while worsening the latency of any individual operation.
LIFETIME: a CREATE DICTIONARY clause controlling how often a dictionary reloads from its source. LIFETIME(MIN a MAX b) reloads at a random time within that window (not a fixed schedule), specifically to avoid many servers reloading the same dictionary from its source simultaneously.
LowCardinality(T) type: a column-type wrapper that dictionary-encodes a column with a small, bounded set of distinct values (most effective under roughly 10,000 distinct values); idiomatic for enum-like string columns such as an event type.
Map(K, V) type: a column type storing an arbitrary number of key-value pairs per row, read back with column['key']; the idiomatic way to attach properties that vary per row (e.g. by event type) without a fixed column for every possible property.
Mark: one entry in a MergeTree part's primary index: the primary key column values belonging to the first row of the granule that mark corresponds to. The index is one flat, sorted array of marks, one per granule.
Mark (per-column): an entry in a column's .mrk/.mrk2/.mrk3 file storing, for one granule, two physical offsets: block_offset (which compressed block in that column's .bin file) and granule_offset (where within that block's decompressed bytes the granule starts). Distinct from the primary index's own marks (see Mark, Module 3) — the primary index decides *which* granules to read; a column's own marks locate *where* those granules physically are, once selected.
Materialized view: in ClickHouse, a view that runs its query incrementally on every INSERT into its source table, writing results into a target table — not a periodically refreshed snapshot like in most other databases.
Merge: the background process that combines multiple parts into one, applying engine-specific logic (deduplication, summation, etc., depending on the MergeTree variant).
Merge selector: the pluggable heuristic (IMergeSelector; SimpleMergeSelector is the default) ClickHouse's background merge process uses to choose which parts to merge next, balancing write amplification (total data rewritten by merges) against the number of live parts at any moment — deliberately merging older and larger parts less often than newer, smaller ones.
MergeTree: ClickHouse's core table engine family; stores data in immutable parts that are periodically merged in the background.
minmax index: a data skipping index type that records the minimum and maximum value of an expression per block; useful only when the column's values have locality — they cluster together within blocks rather than being scattered evenly across the whole table.
Multi-stage engine (MSE): the Pinot query execution path required for SQL joins, distinct from Pinot's original single-stage engine; its existence as a separate, later-added path is itself a signal that query-time joins were not central to Pinot's founding design.
OLAP (Online Analytical Processing): workloads dominated by large aggregate queries over many rows and few columns at a time; optimized for read throughput over individual-row latency.
OLTP (Online Transactional Processing): workloads dominated by small, frequent reads and writes of individual rows; optimized for low-latency single-row operations.
Part: an immutable unit of on-disk storage created by a single INSERT (or by merging smaller parts) in a MergeTree table.
Part level: the number in a part's name recording how many rounds of merging produced it; 0 means the part came straight from an INSERT, and each merge that folds parts together increments the level of the part it produces.
Partition: a named, physically separate grouping of a MergeTree table's parts, defined by a PARTITION BY expression evaluated once per row at insert time; independent of ORDER BY, and a hard boundary merges never cross.
Point lookup: a query that fetches one specific row (or a handful) by key, e.g. WHERE user_id = 12345, the defining access pattern of OLTP workloads. Expensive relative to a row-oriented database in ClickHouse, since reconstructing one row from columnar storage means reading from every relevant column file independently and reassembling it, and the sparse primary index resolves to a granule, not a single row.
Port: the connection point (InputPort or OutputPort) over which one processor passes blocks to another; a processor's prepare() call inspects and updates its ports' state (NeedData, PortFull, Ready, and others) to decide what work, if any, it can do next.
Primary key: the prefix of a MergeTree table's sorting key that its sparse index actually stores entries for. Defaults to the full sorting key unless a shorter one is declared explicitly with PRIMARY KEY.
Processor: the smallest building block of a query pipeline (IProcessor): a unit with zero or more input ports and zero or more output ports that pulls blocks from its inputs, does some work, and pushes blocks to its outputs. A MergeTreeSelect source, an ExpressionTransform, and an AggregatingTransform are all processors.
Projection: an alternate physical layout (different sort order, or a pre-aggregation) of a MergeTree table's own data, automatically selected by the query planner for whichever layout has the least data to scan, with no change to the query itself. A form of denormalization in the broad sense — it duplicates data on disk as a hidden table — but table-transparent, unlike a dictionary or a separately-modeled denormalized table.
Query engine: the part of a system responsible for turning a query into an answer by working through the data, as distinct from the parts responsible for ingesting or storing it.
Query pipeline: the physical graph of connected processors ClickHouse builds from a query plan (one plan step can produce many processors, run in parallel across several lanes); what actually runs, driven by the PipelineExecutor across worker threads, as distinct from the plan's logical, single-ordered tree of steps.
Query plan: the tree of logical steps (read, filter, aggregate, sort, limit, etc.) ClickHouse builds for a validated query before executing it, including optimizations like filter pushdown and column pruning; built without touching any table data.
Query tree: the Analyzer's semantically resolved representation of a query, built from the AST by buildQueryTree(): identifiers tied to real columns, wildcards expanded, aliases substituted, every expression typed. Distinct from the AST (grammar only, nothing validated) and the query plan (built from the query tree, describing physical read/filter/aggregate/sort steps).
Real-time OLAP: an analytics database architected for continuous ingestion (data queryable within seconds of arrival) and sub-second query serving, typically to live dashboards or applications rather than ad-hoc historical exploration. Examples: ClickHouse, Apache Druid, Apache Pinot.
Relevance ranking: sorting search results by estimated relevance to a query (e.g. via BM25 or TF-IDF scoring), typically the job of a dedicated search engine (Elasticsearch and similar). Distinct from token filtering (does this document contain this token, yes or no) — ClickHouse's text index does the latter, not the former.
ReplacingMergeTree: a MergeTree engine that deduplicates rows sharing the same sorting key, keeping the row with the highest value in a version column, during background merges rather than at insert time. Deduplication is eventual, not immediate — an unmerged table can show duplicate rows until the next merge, or a query can force resolution with FINAL at real per-query cost.
Replica: a copy of a shard's data on a different server, used for fault tolerance and read scaling.
ReplicatedMergeTree: a MergeTree engine that maintains multiple copies (replicas) of a table's data across nodes, coordinated through ClickHouse Keeper, trading extra infrastructure for durability and availability. Replication is asynchronous and multi-master by default — any replica can accept an INSERT — so there's a real, usually small, window where a replica can be behind the others.
Rollup: a Druid ingestion-time feature that pre-aggregates rows sharing identical dimension values and timestamp granularity into a single row, shrinking stored row counts by orders of magnitude at the cost of losing the ability to query individual raw events on rolled-up columns.
Row-oriented storage: a physical storage layout that groups all of a row's column values together, contiguously, so a query can fetch a full record in one read; the counterpart to columnar storage.
set index: a data skipping index type that records the distinct values seen per block, up to a configured cap (set(max_rows)); a block whose distinct-value count exceeds the cap can no longer be filtered by the index at all.
Shard: a horizontal partition of a table's data, hosted on a different server, used to scale writes and storage beyond a single node.
Sign column: the 1 (state) / -1 (cancel) column CollapsingMergeTree and VersionedCollapsingMergeTree use to mark whether a row represents a current contribution or a retraction of a prior one; queries aggregating over an unmerged collapsing table must multiply by Sign (e.g. SUM(value * Sign)) rather than summing the raw value.
Snowflake: a cloud data warehouse billed per-second for compute while a virtual warehouse is running, with zero cost while suspended. Known for maturity in governance tooling, semi-structured data support, and BI-tool integration; architected for latency-tolerant, ad hoc analytical queries rather than continuous ingestion or sub-second serving.
Sorting key: the ORDER BY expression of a MergeTree table; determines the physical sort order of rows within every part, independent of PARTITION BY.
Sparse primary index: an index that stores one entry per granule, not per row, pointing to the first row of that granule; used to skip granules, not to look up individual rows.
SummingMergeTree: a MergeTree engine that folds rows sharing the same sorting key together during a merge, summing every numeric, non-key column. Handles exactly one aggregate — sum — with no mechanism for a distinct count, an average, or a quantile.
system database: ClickHouse's built-in, read-only database exposing the server's own metadata and internal state (running queries, table definitions, query history) as ordinary, queryable SQL tables.
system.errors: the system table aggregating every named exception the server has thrown since startup, one row per error code that has fired at least once, with a running count (value), the timestamp and text of the most recent occurrence (last_error_time, last_error_message), and whether it came from a local or a remote/distributed query (remote); the fastest single read for what kind of failure a server has actually experienced, before committing to a specific hypothesis.
system.parts: the system table listing every data part in every MergeTree table, including its partition, name, row/byte counts, and whether it's still active or has been superseded by a merge.
system.processes: the system table listing queries currently executing on the server; a row exists only while its query is still running and disappears the instant the query ends.
system.processors_profile_log: the system table recording, per processor in a query's pipeline, how long it spent doing its own work (elapsed_us) versus waiting for input or blocked on a full output (input_wait_elapsed_us, output_wait_elapsed_us); the tool for finding which specific processor in a pipeline is the actual bottleneck, distinct from system.query_log's query-level totals. Populated only when log_processors_profiles = 1.
system.query_log: the system table recording the history of executed queries (timing, rows/bytes read, memory used, success or failure); written to disk in periodic, buffered flushes rather than the instant each query finishes.
system.replicas: the system table reporting live replication status for every ReplicatedMergeTree table, one row per local replica: whether it's in read-only mode and since when (is_readonly, readonly_start_time), how far behind in seconds (absolute_delay), how many operations are queued (queue_size), and how many parts have been permanently lost since the table's creation (lost_part_count).
system.replication_queue: the system table listing individual pending replication tasks (fetching a part, merging, mutating) for ReplicatedMergeTree tables, including how many times each has failed (num_tries) and the most recent failure's error text (last_exception) — the table to read once system.replicas shows a replica isn't caught up, to see specifically which operation is stuck and why.
system.tables: the system table listing metadata for every table ClickHouse knows about: engine, row and byte counts, and the original CREATE TABLE statement.
Text index: ClickHouse's inverted-index feature (general availability as of 2026) for fast token-based filtering over string columns — the mechanism behind functions like hasToken and hasAllTokens. Explicitly not a relevance engine: no BM25/TF-IDF scoring, no positional data for phrase ranking, by ClickHouse's own documentation.
Throughput: the total amount of work a system completes per unit of time, measured across the whole system (e.g., rows ingested per second, queries answered per second).
TTL (Time To Live): a clause on a column or table that marks data as expired once an expression (typically date/time-based) is satisfied. Expired rows are not deleted immediately — they're removed the next time a background merge processes the part they live in, the same mechanism that produces every other merge in this course.
Upsert table: a Pinot real-time table type where a primary key lets a newly-ingested row transparently replace an older row sharing that key, resolved by a configurable comparison column (the ingestion timestamp by default). Requires partitioning the source stream by primary key; full (non-partial) upsert semantics are available on real-time tables only.
Vectorized execution: a query-execution strategy that processes data in blocks of many values at once rather than row by row, enabling CPU-level optimizations like SIMD.
VersionedCollapsingMergeTree: the same sign-column collapsing pattern as CollapsingMergeTree, but with an added Version column that lets ClickHouse pair a state and cancel row correctly regardless of insertion order, tolerating concurrent, multi-threaded, uncoordinated writers.
Wide format: a MergeTree part's physical layout when it's large enough to cross min_bytes_for_wide_part/min_rows_for_wide_part: each column stored in its own file(s) on disk. The regular, default-at-scale format; the one most of this course's on-disk mechanics (marks, compressed blocks) describe.