Set up docs MCPA glossary of database concepts and ClickHouse terminology, including how familiar database terms differ in ClickHouse.
No glossary terms match “”.
Atomicity
Atomicity means an operation is observed either in full or not at all. In ClickHouse, an insert into one partition of one MergeTree-family table is atomic when its rows are written as a single block. An insert spanning partitions is atomic separately for each partition, and an insert into a distributed table is atomic separately for each shard. Multi-statement transactions remain experimental and restricted.
Block
A block is a self-describing columnar batch of rows used for query processing and data transfer. Blocks are runtime and wire units; data parts and granules are separate storage and indexing concepts. Processing column values in blocks enables vectorized execution.
Cluster
A collection of nodes (servers) that work together to store and process data.
CMEK
In ClickHouse Cloud, customer-managed encryption keys (CMEK) allow a customer’s key-management service (KMS) key to protect the data encryption key (DEK) used for data at rest.
Delete
For MergeTree-family tables, deleting rows can mean marking them as deleted with DELETE FROM, rewriting affected data parts with ALTER TABLE ... DELETE, or efficiently removing an entire partition. Lightweight deletes hide rows from subsequent queries before the data is physically removed during background merges.
Deduplication
Deduplication can refer to different mechanisms in ClickHouse. For row-version deduplication, engines such as ReplacingMergeTree identify duplicate versions by the sorting key and resolve them during background merges within a partition. Replicated table engines can separately deduplicate retried insert blocks by their block identifiers.
Dictionary
A dictionary provides key-value access to reference data from an in-memory or external source. For compatible key-based lookups, dictionary functions or a direct dictionary JOIN can avoid repeatedly scanning a reference table.
Distributed table
A distributed table in ClickHouse is a special type of table that doesn’t store data itself but provides a unified view for distributed query processing across multiple servers in a cluster.
FINAL
FINAL is a query modifier that applies an engine’s merge-time transformations while reading data, without physically merging the stored parts. It can return reconciled results from engines such as ReplacingMergeTree before background merges finish, at the cost of additional query-time compute and memory.
Granule
A granule is the smallest logical group of rows ClickHouse reads for primary-index pruning. It contains up to 8,192 rows by default, but adaptive index granularity can create smaller granules. The primary index normally stores one entry per granule.
Incremental materialized view
An incremental materialized view runs its query as data is inserted into a source table and writes the result to a target table. It processes only the newly inserted blocks, not the source table’s complete current state, and changes to joined right-side tables don’t retrigger it.
JSON
The JSON type stores semi-structured documents whose paths and types may vary between rows. ClickHouse stores discovered paths as subcolumns so queries can read individual fields efficiently. Use typed columns or structural types such as Tuple when the schema is stable.
Mark file
A mark file stores offsets that locate granules in compressed column data. Each mark records an offset in the compressed file and an offset within the corresponding decompressed block, allowing ClickHouse to seek to a granule without reading the entire column.
Materialized view
ClickHouse has two materialized-view models. An incremental materialized view acts like an insert-time trigger that processes newly inserted blocks, while a refreshable materialized view periodically reruns its query over the full dataset. Features with similar names in other databases may combine these behaviors, so there isn’t always a one-to-one mapping.
Merge
A merge in ClickHouse is a background storage operation that combines smaller immutable data parts into larger parts within the same partition. Depending on the table engine, merges can also aggregate, collapse, or replace rows; they aren’t the same as a transactional SQL MERGE statement.
MergeTree
A MergeTree in ClickHouse is a table engine designed for high data ingest rates and large data volumes. It is the core storage engine in ClickHouse, providing features such as columnar storage, custom partitioning, sparse primary indexes, and support for background data merges.
Mutation
For MergeTree-family tables, a mutation modifies or deletes existing data with commands such as ALTER TABLE ... UPDATE or ALTER TABLE ... DELETE. Unlike an OLTP row update, it rewrites affected data parts and normally proceeds asynchronously; parts are replaced as they become ready, so the operation isn’t an atomic table-wide transaction.
Nullable column
A column must use Nullable(T) to distinguish NULL from ordinary values of type T, including values such as 0 or an empty string. ClickHouse stores a separate null mask, which adds storage and processing overhead, so use nullable columns when missing values have meaningful semantics rather than as a default choice.
On-the-fly mutation
When apply_mutations_on_fly is enabled for both a mutation and subsequent reads, ClickHouse applies pending updates or deletes during SELECT queries so their results are visible before the stored parts are rewritten. The mutation is still materialized asynchronously in the background.
Parts
A data part is an immutable collection of files on storage containing a portion of a table’s rows. Parts are created by inserts and combined by background merges within a partition. Unlike a partition, which is a logical grouping of data, a part is a physical storage unit managed by ClickHouse.
Partition
A partition is a logical grouping of data parts in a MergeTree-family table. Partitioning is primarily for data-management operations such as dropping, moving, and applying retention policies to groups of data. Partition pruning can help queries that select only a few partitions, but the sorting and primary keys are usually more important for query performance.
Partitioning key
A partitioning key is the expression in a table’s PARTITION BY clause. Rows that produce the same partition ID belong to the same logical partition, while separate inserts can create separate data parts inside that partition. The grouping enables operations such as dropping, moving, or archiving an entire partition.
Primary key
Unlike a primary key in many transactional databases, a ClickHouse primary key isn’t a row-level uniqueness constraint. It defines the columns in a sparse primary index that helps ClickHouse skip granules while reading. By default it matches the sorting key defined by ORDER BY; if defined separately, it must be a prefix of the sorting key.
Projection
A projection is an automatically maintained representation of a table’s data with an alternate ordering, a subset of columns, or a precomputed aggregation. ClickHouse can choose it automatically while querying the original table. Projections may duplicate stored data and add write overhead, although _part_offset projections can trade storage for additional reads from the base table.
Refreshable materialized view
A refreshable materialized view periodically reruns its query over the full dataset and replaces or appends the stored result on a schedule. Unlike an incremental materialized view, it isn’t triggered by each inserted block and can use complex queries. It can replace a scheduled query that materializes a SELECT result, but it isn’t a general-purpose scheduler for arbitrary DDL or DML statements.
ReplacingMergeTree
ReplacingMergeTree models updates and upserts by accepting multiple versions of rows with the same sorting key and retaining one version during background merges. Deduplication is eventual rather than an insert-time uniqueness guarantee, so queries may see multiple versions until they use FINAL, equivalent query logic, or the relevant parts merge.
Replica
A replica is a server or compute instance that maintains or accesses the same logical table data as other replicas for availability and query capacity. With ReplicatedMergeTree, replicas maintain independent copies of data; ClickHouse Cloud replicas using SharedMergeTree share object storage instead.
Secondary index
In ClickHouse, the closest analogue to a conventional secondary index is usually a data skipping index. Instead of locating individual rows through a B-tree, it stores metadata for groups of granules so ClickHouse can avoid reading blocks that can’t contain matching values.
Shard
A shard is a logical subset of table data assigned to one server or replica group in a distributed deployment. Sharding divides data and query work across servers; replicas provide redundant or parallel access to the data within each shard.
Skipping index
A data skipping index stores compact metadata for one or more consecutive granules so ClickHouse can avoid reading blocks that cannot match a query. It is most effective when indexed values correlate with the table’s ordering and may provide little benefit when matching values occur in most indexed blocks.
Sorting key
For a MergeTree-family table, the ORDER BY clause defines the sorting key: the physical row order within each data part. It serves a similar purpose to clustering columns or clustering keys in other analytical databases, but ClickHouse uses it to maintain a defined lexicographic row order. If no separate primary key is specified, the sorting key also becomes the primary key; the two keys are related but aren’t required to be identical.
Sparse index
A sparse primary index stores key values for each granule rather than one entry per row. ClickHouse uses these entries to identify candidate granules and then reads their rows. Because its size scales with granules rather than rows, the index is usually small enough to keep in memory.
Table engine
Table engines in ClickHouse determine how data is written, stored and accessed. MergeTree is the most common table engine, and allows quick insertion of large amounts of data which get processed in the background.
Transaction
In ClickHouse, transactional guarantees are scoped differently from those in a typical OLTP database. Qualifying inserts are atomic at the block or partition level, while conventional multi-statement transactions with COMMIT and ROLLBACK remain experimental and have significant restrictions.
TTL
TTL rules move, delete, or roll up data after an expression becomes eligible. Expiration isn’t immediate: ClickHouse normally applies expired-data actions during background merges, so expired rows can remain on disk and be returned by queries until a merge processes the relevant parts.
Update
ClickHouse is optimized for immutable, append-heavy data rather than frequent in-place row updates. Updates are commonly modeled by inserting new versions with specialized table engines or performed as mutations that rewrite affected data parts.
Upsert
MergeTree-family tables don’t perform a transactional INSERT ... ON CONFLICT upsert. Upserts are commonly modeled by inserting a newer row version into an engine such as ReplacingMergeTree. Older versions are resolved during background merges, so queries may need FINAL or equivalent logic until merging occurs.
Warehouse
In ClickHouse Cloud, a warehouse is a set of services that share the same data but have independent compute resources and endpoints. In systems where a warehouse represents one compute cluster, an individual ClickHouse service is the closer analogue; a ClickHouse warehouse groups multiple services.