ClickHouse Internals
Table definition, inserts, durability, merges, storage, query execution and updates, using one example table.
ClickHouse is an open-source, column-oriented database for real-time analytics. Written in C++ and ships as a single binary. Queries run on a vectorized execution engine, and the MergeTree family of table engines is the main storage format.
ClickHouse started in 2009 at Yandex as a filter and aggregation operator for web-scale log data, the click records of the Yandex.Metrica web analytics service. It was open-sourced in 2016.
1. Core Concepts
1.1. Overview
ClickHouse runs on a single server or on a cluster with sharding, replication, or both. Every server runs the same binary, with the same storage format and query engine. A cluster uses the Distributed table engine for sharding, the ReplicatedMergeTree engines for replication, and ClickHouse Keeper for coordination.
ClickHouse Cloud replaces ReplicatedMergeTree with SharedMergeTree, a table engine family built on shared object storage.
1.2. Server
A client sends a query to a protocol handler. The query engine plans and executes the query. It reads and writes table data using table engines, and external data using integration engines.
Background tasks include merging parts, applying updates and deletes, fetching parts from other replicas, moving parts between disks, removing old parts, and sending queued inserts of Distributed tables. They run on dedicated thread pools, separate from query execution.
| Component | Responsibility |
|---|---|
| Protocol handlers | Client sessions over native TCP (port 9000), HTTP (8123), MySQL (9004) and PostgreSQL (9005) |
| Query engine | Parsing, analysis, planning and execution of queries |
| Table engines | Storage of table data |
| Integration engines | Table engines and table functions for external systems, such as S3, MySQL and Kafka |
| Shared services | Thread pools, caches, role-based access control, backups and monitoring |
1.3. Table Engines
Every table has a table engine. The engine decides how and where the table's data is stored, which queries the table supports, and whether it is replicated.
| Family | Engines | Where the data lives |
|---|---|---|
MergeTree | MergeTree, ReplacingMergeTree, SummingMergeTree, AggregatingMergeTree, CollapsingMergeTree, VersionedCollapsingMergeTree, GraphiteMergeTree, CoalescingMergeTree, and their Replicated versions | Sorted parts on local disks or object storage, merged in the background. The example uses this family |
| Log | TinyLog, StripeLog, Log | Files on disk. Writes append to the end. No indexes, no mutations. Meant for many small tables of up to about 1 million rows each, read whole |
| Integration | S3, Kafka, MySQL, PostgreSQL and others | In an external system, such as S3, Kafka or MySQL, or locally in another storage engine, such as RocksDB |
| Special | Memory, Buffer, Distributed and others | Depends on the engine |
The special engines, by where their data lives:
| Engine | Data | Behavior |
|---|---|---|
Memory | RAM | Stores data uncompressed by default. The data is lost on restart |
Buffer | RAM, then another table | Buffers inserts and flushes them to another table when time, row or byte thresholds are reached. Reads see both |
MaterializedView | Its target table | Runs its SELECT on rows inserted into its source table and writes the result to the target table. A refreshable view reruns it on a schedule instead |
Set | RAM, also written to disk | A data set for the right side of IN |
Join | RAM, also written to disk | A prepared structure for the right side of JOIN |
Dictionary | The dictionary, in RAM for most layouts | Shows a dictionary's data as a table |
Distributed | None. By default, pending inserts are queued on local disk | Sends queries and inserts to tables on the servers of a cluster |
Merge | None | Reads from any number of other tables at once |
View | None | Stores a SELECT query and runs it on read |
Alias | None | Forwards reads and writes to another table |
QueryRunner | None | Runs each inserted row as a query, locally or on a remote cluster |
Null | None | Discards writes. Reads return nothing |
File | A local file | Stores data in a supported format, such as TabSeparated or Native |
FileLog | Log files | Reads application log files as a stream of records |
URL | A remote HTTP or HTTPS server | Reads and writes data there |
KeeperMap | Keeper | Key-value data with linearizable writes |
Executable | Generated | Rows come from a script that writes them to stdout |
GenerateRandom | Generated | Random data for a given schema |
1.4. Parts and Partitions
A MergeTree table stores its rows in parts. A part is a directory holding some of the table's rows, sorted by the table's sorting key, which is set by its ORDER BY clause. Parts are never modified once written: inserts and background merges write new parts, and the parts a merge combines are removed afterwards. The design is based on the log-structured merge tree (LSM tree).
A partition is the set of parts with the same value of the partition key, an expression set by the table's PARTITION BY clause. A part never holds rows of two partitions, and a merge combines parts of a single partition only. Partitions have no directory of their own: part directories sit side by side in the table's directory, and each part's name starts with its partition ID.
The partition ID comes from the partition-key value. For an integer, such as 202610 for October 2026, the ID is the number itself. For other types, such as a string, it is a hash of the value. A table without a partition key has a single partition, with the ID all.
Each part written by an insert gets a new block number, used as both its min and max block. Block numbers are handed out in increasing order. A merged part takes the lowest min block and the highest max block of its source parts, so it covers the block numbers of all its sources. The level of a part written by an insert is 0, or 1 when the engine has already combined its rows at insert, as ReplacingMergeTree does by default, and a merged part's level is one higher than the highest level of its sources.
1.5. Shards, Replicas and Keeper
In a sharded cluster, a table's rows are divided between shards, each holding a different subset. Each shard has one or more replicas: servers that store the same rows. Every server has its own local table. A Distributed table reads from the local tables of all shards and sends each inserted row to one shard, chosen by a sharding key. It stores no rows of its own. By default, inserts waiting to be sent are queued on local disk.
With a Replicated engine, such as ReplicatedMergeTree, the replicas of a shard keep the same parts of a table. Replication is per table, asynchronous, and has no primary: any replica accepts inserts, and the other replicas copy the new part. Each replica follows a shared log of changes, such as new parts and merges, stored in Keeper.
Keeper is a coordination service, compatible with ZooKeeper, used for replication and for ON CLUSTER statements, which run a statement on every server of a cluster. It uses the Raft consensus algorithm and runs as its own process or inside a ClickHouse server. Its state for a replicated table includes the replication log, each replica's list of parts, block numbers per partition, and hashes of recent inserts for deduplication. For ON CLUSTER statements, it stores a single queue that every server of the cluster reads.
2. The Example
2.1. Table Definition
Consider a table for page views, called events, with each row representing a view. Since storage depends on the table engine, some details change with the engine: all MergeTree engines write the same part format, but they differ in how rows with the same sorting-key are combined, and ReplacingMergeTree adds a column for it, ver.
Table engine
CREATE TABLE events
(
ts DateTime, -- time of the view
site_id UInt32, -- site
user_id UInt64, -- user
url String, -- page URL
duration UInt32 -- time spent on the page
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(ts)
ORDER BY (site_id, ts);
CREATE TABLE events
(
ts DateTime, -- time of the view
site_id UInt32, -- site
user_id UInt64, -- user
url String, -- page URL
duration UInt32, -- time spent on the page
ver UInt32 -- version: the highest is kept
)
ENGINE = ReplacingMergeTree(ver)
PARTITION BY toYYYYMM(ts)
ORDER BY (site_id, ts);
| Clause | In events | Effect |
|---|---|---|
ORDER BY | (site_id, ts) | Rows in each part are sorted by site, then by time within a site |
PRIMARY KEY | not set | Same as ORDER BY. If set, it must be a prefix of ORDER BY |
PARTITION BY | toYYYYMM(ts) | The rows of each month form one partition, such as 202610 |
2.2. Deployment
ClickHouse has three deployment models: a single server, a replicated cluster, and a sharded + replicated cluster.
Deployment
On a single server, events is a local table and Keeper is not used. The server reloads its definition from disk at startup.
ON CLUSTER queues the statement in Keeper. Every server of the cluster runs it and fills in {shard} and {replica} from its own configuration, which creates its local events and adds it to its shard's replicas in Keeper. Replicas of a shard share one replication log and copy each other's parts. All servers need and use the same definition of the main cluster.
CREATE TABLE events ON CLUSTER main
(
... -- columns as above
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/events', '{replica}')ReplicatedReplacingMergeTree('/clickhouse/tables/{shard}/events', '{replica}', ver)
PARTITION BY toYYYYMM(ts)
ORDER BY (site_id, ts);
Each shard has its own replication log, so shards never exchange parts. Inserts and queries go through events_distributed, a Distributed table that stores no rows and forwards them to the local events of each shard. It sends each row to the shard chosen by site_id. With internal_replication set in the cluster definition, the row goes to one replica and replication copies it to the other. Otherwise events_distributed writes it to every replica.
CREATE TABLE events_distributed ON CLUSTER main AS events
ENGINE = Distributed(main, default, events, site_id);
3. Insert
3.1. Client to Server
Consider an insert of three page views, two in October and one in November:
INSERT INTO eventseventsevents_distributed VALUES
('2026-10-14 09:00:00', 42, 1001, '/pricing', 30, 1),
('2026-10-14 09:05:00', 7, 1002, '/docs', 12, 1),
('2026-11-02 10:00:00', 42, 1003, '/blog', 45, 1);
Every block of rows the server writes becomes at least one part on disk, and every part is a directory of files that a merge has to rewrite later. A client that writes one row at a time would create a part per row. ClickHouse has two insert modes, and async mode avoids this.
Insert mode
In async mode, the default:
The client sends the
INSERTwith its rows. The server does not parse text rows yet. It adds them to an in-memory buffer shared by every insert with the same query text, user, roles and settings.The buffer is flushed when it holds 10 MiB of data or when a timeout of 50 to 200 milliseconds runs out. The timeout grows while inserts keep arriving, so a busy table collects more rows per flush.
On flush, a background thread parses all the buffered rows into one block, a batch of rows held in memory column by column, and writes it.
By default, each client is acknowledged only after the flush has written its rows.
The result: a thousand small inserts per second become a few parts per second. The tradeoff is latency, because an insert waits for the flush. An insert of more than 10 MiB skips the buffer, since it already makes a large part.
In sync mode, each insert is written on its own. Over the native protocol, the client gets the table's columns from the server, parses the rows itself and streams them as column blocks. Over HTTP, the server parses the rows from the request body. Either way, the server joins small blocks into larger ones before writing.
The result: no wait for a flush. The tradeoff: every small insert still costs a part.
wait_for_async_insert sets when an async insert is acknowledged.
| Mode | Parts written | Client acknowledged | Lost if the server crashes before the commit |
|---|---|---|---|
Sync, async_insert = 0 | One per insert | After its part is committed | Only unacknowledged rows |
Async, wait_for_async_insert = 1 (default) | One per flush | After the flush commits the part | Only unacknowledged rows |
Async, wait_for_async_insert = 0 | One per flush | When its rows are in the buffer | Acknowledged rows still in the buffer |
A graceful shutdown flushes the buffer before the server stops.
Relevant Settings
async_insert,
async_insert_max_data_size,
async_insert_busy_timeout_min_ms,
async_insert_busy_timeout_max_ms,
wait_for_async_insert,
async_insert_queue_flush_on_shutdown,
min_insert_block_size_rows
3.2. Shard Routing
Without shards, every row goes to the local events.
In a sharded cluster, each row belongs on one shard, and the server that receives the insert is a replica of only one shard. events_distributed routes the rows:
It computes the sharding key,
site_id, for each row and maps it to a shard. Each shard owns as many slots as its weight, and a row goes to the slot of its key modulo the total. With two shards of weight one, the total is 2: site 42 goes to slot 42 % 2 = 0, which is shard 1, and site 7 to slot 7 % 2 = 1, which is shard 2.By default, rows for the local shard, shard 1 on
s1-r1, go straight into the localevents.Rows for other shards are written to a file on local disk, in a directory for the destination shard, and the insert completes.
A background sender reads each file, sends it to one replica of its shard as an insert into
events, and deletes the file. After a failure it retries with growing delays.
In async mode, s1-r1 first buffers the insert as in 3.1, and s2-r1 buffers the rows from the sender again before writing them.
The result: the client waits only for local writes, and a shard that is down does not fail the insert. The tradeoff: rows for other shards become visible only when the sender delivers them, and they are lost if the local disk fails before that. In foreground mode, the insert sends the rows to the shards itself and waits for them.
With internal_replication, the file goes to one replica of the shard, and replication copies it to the other (3.7). Without it, events_distributed writes the rows to every replica itself: s1-r2 and each replica of shard 2 get a file of their own.
Relevant Settings
distributed_foreground_insert,
prefer_localhost_replica,
distributed_background_insert_sleep_time_ms,
distributed_background_insert_max_sleep_time_ms,
internal_replication (cluster definition)
3.3. Split by Partition
A part holds rows of one partition only, so one block can become several parts. The server evaluates the partition key, toYYYYMM(ts), for every row and splits the block into one block per month. The three rows become a block for 202610 with two rows and a block for 202611 with one. On s1-r1, the two rows for site 42 become a block for 202610 and a block for 202611. On shard 2, the row for site 7 forms a single block for 202610.
Each of these blocks becomes its own part, so an insert that spans many partitions creates many small parts. ClickHouse rejects an insert block that touches more than 100 partitions.
In async mode, the flushed block also holds the rows of other inserts, and they land in the same partition blocks.
Relevant Settings
max_partitions_per_insert_block,
throw_on_max_partitions_per_insert_block
3.4. Sorting and Combining
Every part is sorted by the sorting key, which is what lets the primary index and merges work without sorting again. Each partition block is sorted by (site_id, ts) before it is written, and the sort is skipped when the block is already in order. In 202610, the row for site 7 moves ahead of the row for site 42. On each shard, every partition block here holds a single row, so nothing moves.
MergeTree writes every row, and rows with the same key stay side by side.
By default, ReplacingMergeTree also applies its rule inside the block: of rows with the same key, only the one with the highest ver stays. This does the work of a merge early, for duplicates that arrive in the same insert. The three rows have different keys, so all of them stay. A part written this way starts at level 1 instead of 0, because it is already combined.
Relevant Settings
optimize_on_insert
3.5. Writing the Part
The writer creates a temporary directory and writes the part's files there, so a half-written part is never visible. Parts with less than 10 MiB of uncompressed data are Compact, with all columns in one data file. Larger parts are Wide, with a file per column, so a query reads only the columns it needs. The parts of the three rows are Compact, and merges turn the data into Wide parts as it grows.
checksums.txt is written last and records the size and hash of every data, mark and index file, which is how ClickHouse later recognizes a damaged part.
An index entry per row would make the index as large as the data. ClickHouse groups the rows of a part into granules of up to 8,192 rows and indexes only the first row of each, plus the last row of the part. A granule is the smallest unit a query reads. A 202610 part with 1,000,000 rows, as merges produce over time, is Wide and has 123 granules, since 1,000,000 / 8,192 = 122.07 rounds up to 123.
In a Wide part, each column is compressed in blocks of 64 KiB to 1 MiB. For every granule, a column's marks file records which compressed block holds the granule's first row and where in the decompressed block it starts. Granules and compressed blocks do not line up: duration takes four bytes per row, so a granule of 8,192 rows takes 8,192 × 4 = 32,768 bytes. A block closes at the first granule boundary at or past 64 KiB, which is 2 × 32,768 = 65,536 bytes, so granules 0 and 1 share the first block, and mark 1 points to offset 32,768 inside it.
The result: the primary index of a billion-row table has 1,000,000,000 / 8,192 ≈ 122,071 entries, plus one for the last row, and fits in memory. The tradeoff: a query that needs a single row still reads and decompresses a whole granule.
Relevant Settings
min_bytes_for_wide_part,
index_granularity,
index_granularity_bytes,
min_compress_block_size,
max_compress_block_size
3.6. Commit
A query must never see half of a part, so a part becomes visible in one step, under the table's parts lock:
The server takes the next block number from a counter shared by the whole table.
It renames the temporary directory to the final name:
202610_1_1_01, and202611_2_2_01for the second part.It marks the part Active, and from then on new queries read it.
Each part commits on its own, so a query running during the insert can see the 202610 part without the 202611 part. The counter lives in memory and is rebuilt at startup from the highest block number on disk. A single server does not deduplicate inserts by default, so a retry after a timeout writes the rows again.
Replicas accept inserts independently, so part names have to be agreed through Keeper, and a retried insert must not be written twice:
The replica asks Keeper for a block number in partition
202610. Numbers count per partition and start at 0. The same request checks that a hash of the block's data has not been seen before.It renames the temporary directory to
202610_0_0_01.In one atomic Keeper request, it adds the part to the table's log for the other replicas, records the data hash, and lists the part under its own replica.
Only when that request succeeds does the part become Active.
The result: a client that retries after a timeout does not create duplicates, because the hash is already in Keeper and the block is skipped. Keeper keeps only recent hashes, the last 10,000 within an hour. The tradeoff: every part costs Keeper round trips. If the connection to Keeper drops mid-commit, the replica reconnects and checks whether its part reached Keeper, and the insert fails with UNKNOWN_STATUS_OF_INSERT only if Keeper stays unreachable.
s1-r1 commits its two parts under /clickhouse/tables/1/events, and the shard 2 replica that receives the row for site 7 commits its part under /clickhouse/tables/2/events.
After each commit, the insert wakes the merge scheduler (4.1).
Relevant Settings
non_replicated_deduplication_window,
replicated_deduplication_window,
replicated_deduplication_window_seconds,
insert_deduplication_token
3.7. Replication
A single server has no replicas, and the insert is complete after the commit.
s1-r2 has to end up with the same parts as s1-r1, and s1-r1 never pushes anything to it. Replication runs from the log in Keeper:
s1-r2watches the table's log. Whens1-r1adds the entry for its new part, the watch fires.s1-r2copies the new entry into its own queue in Keeper and moves its log pointer past it.For a new part, it finds a replica that has the part, downloads the files over HTTP, and checks every file against the sender's hashes.
It lists the part under its own replica in Keeper and marks it Active. On shard 2,
s2-r2copies the parts ofs2-r1the same way.
The result: replicas converge from the log, and a replica that was offline catches up by reading the log from its pointer. The tradeoff: replication is asynchronous. The insert returns after s1-r1 commits, so s1-r2 can lack the part for a while. With insert_quorum, the insert waits until a given number of replicas have it.
Relevant Settings
insert_quorum,
insert_quorum_timeout,
interserver_http_port
3.8. Acknowledgment and Durability
An acknowledgment means the part is committed, not that it is on stable storage. ClickHouse does not fsync new parts by default. A committed part can still be in the operating system's page cache, so it survives a crash of the server process, but a power loss or kernel crash can lose or damage it. A replica that loses a part fetches it again from another replica.
With wait_for_async_insert disabled, the client is acknowledged as soon as its rows are buffered, and a crash before the flush loses them.
At startup, the server cleans up what a crash left behind:
It deletes leftover temporary directories. They were never committed, so no query has seen them.
When a merged part (4.4) and its source parts are both on disk, it keeps the merged part Active and schedules the sources for removal.
It moves a part with missing or wrongly sized files to
detached, and loads the parts that were merged into it instead.
A replica also compares its parts with its list in Keeper. It fetches the parts it is missing and detaches local parts that Keeper does not list.
The result: because commit is a rename, after a crash of the server process a part is either complete or a temporary directory that gets deleted. The tradeoff: inserts never wait for the disk, so after a power loss the latest parts can be lost, or damaged and detached, unless fsync_after_insert and fsync_part_directory are enabled.
Relevant Settings
fsync_after_insert,
fsync_part_directory,
wait_for_async_insert,
max_suspicious_broken_parts
4. Merges
4.1. Merge Selection
Every insert adds parts, and a query has to open every part of the partitions it reads. Background merges keep the number of parts low by combining small parts into larger ones. Two more inserts add parts to 202610 on shard 2: a view of the pricing page of site 7, and the 09:05 view of site 7 sent again with a corrected duration and ver 2.
INSERT INTO eventseventsevents_distributed VALUES ('2026-10-20 18:30:00', 7, 1004, '/pricing', 20, 1);
INSERT INTO eventseventsevents_distributed VALUES ('2026-10-14 09:05:00', 7, 1002, '/docs', 95, 2);
The merge scheduler decides what to merge:
It looks at the active parts of each partition in block order. A merge combines a run of adjacent parts, never parts of two partitions.
A run qualifies only when its total is at least five times its largest part, counting each part as at least 5 MiB, so a part is merged into something much larger than itself instead of being rewritten again and again.
Of the qualifying runs, it picks the one that removes the most parts for the least data.
The merge runs on the background pool. When nothing qualifies, the scheduler waits longer before each new attempt.
The result: a part is merged only into a total several times its own size, which limits how often each row is rewritten. The tradeoff: a busy servera replica with many merges in its replication queue limits the size of new merges, so small merges keep running while large ones wait, and parts stop growing through merges at 150 GiB.
For small parts, the required factor is 5 − 3 × (age − 10 s) / 3,590 s, where age is the age of the youngest part, so it falls from five at 10 seconds to two at one hour. It falls faster in a partition with more than ten parts. The three small parts of 202610 count as 5 MiB each, so their ratio is (3 × 5 MiB) / 5 MiB = 3. The factor reaches three at 10 + 3,590 × 2/3 ≈ 2,403 seconds, about 40 minutes after the last insert, and the parts merge into 202610_1_4_202610_0_2_202610_0_2_12.
Relevant Settings
max_bytes_to_merge_at_max_space_in_pool,
max_parts_to_merge_at_once,
merge_selector_base,
background_pool_size,
background_merges_mutations_concurrency_ratio,
max_replicated_merges_in_queue
4.2. Merge Assignment
A single server selects and runs its own merges.
Replicas must merge the same parts into the same result, or their sets of parts drift apart. Merges go through the log, like new parts:
Any replica can select a merge, from the parts it has listed in Keeper.
It writes the merge to the table's log. Keeper accepts the write only if no other merge or mutation entry was added to the log since the replica read it, so two replicas cannot add conflicting merges.
Every replica, the one that selected the merge included, reads the entry into its queue and runs the same merge on its own copy of the parts.
The result: replicas end up with identical parts, and each spends its own CPU instead of sending data over the network. The tradeoff: every replica does the merge work. A replica that misses a source part downloads the merged part instead, and so does one that finds an old, large merge already done elsewhere.
Relevant Settings
always_fetch_merged_part,
prefer_fetch_merged_part_time_threshold,
prefer_fetch_merged_part_size_threshold,
execute_merges_on_single_replica_time_threshold
4.3. Rows with the Same Key
A merge reads its source parts in sorting-key order and writes one sorted part. This is where the engines differ: the engine decides what happens to rows with equal keys. The two views of site 7 at 09:05 share the key (7, 2026-10-14 09:05:00).
MergeTree keeps both. Its sorting key orders the rows but does not make them unique, so a query sees both views.
ReplacingMergeTree keeps only the row with the highest ver, so the view with duration 95 replaces the one with 12. This happens only to rows that meet in the same merge. Duplicates stay until one merge covers all the parts that hold them, and rows in different partitions are never combined.
Large merges into Wide parts, with at least 131,072 rows and 11 columns outside the sorting key, sort the key columns first and then copy the other columns one at a time, to keep memory low. The merges of events are too small for that and read all columns together.
Relevant Settings
vertical_merge_algorithm_min_columns_to_activate,
vertical_merge_algorithm_min_rows_to_activate
4.4. Replacing Parts
A merge creates a new part, and the old ones have to disappear without breaking the queries that read them:
The merged part is written to a temporary directory and committed like an inserted part, under the parts lock.
In the same step, the source parts become Outdated, and new queries no longer see them.
A query that started earlier keeps reading the source parts, because it holds references to them.
A cleanup thread deletes an Outdated part when no query holds it and eight minutes have passed. The thread runs every 30 seconds to 5 minutes. The replica also removes it from its list in Keeper.
The result: queries never see a half-finished merge and never lose a part they are reading.
When inserts outpace merges, the number of active parts grows. ClickHouse slows inserts down when a partition reaches 1,000 active parts and rejects them at 3,000 with TOO_MANY_PARTS, unless the partition's parts average over 1 GiB. OPTIMIZE TABLE events FINAL forces each partition into one part, regardless of the 150 GiB limit.
Relevant Settings
old_parts_lifetime,
parts_to_delay_insert,
parts_to_throw_insert
5. Query
A query for the views and average time on page of site 7 in October, run before the parts of 202610 are merged:
SELECT site_id, count() AS views, avg(duration) AS avg_duration
FROM eventseventsevents_distributed
WHERE site_id = 7 AND ts >= '2026-10-01' AND ts < '2026-11-01'
GROUP BY site_id;
5.1. Parse, Analyze and Plan
A query goes through four stages: the parser builds a syntax tree, the analyzer resolves names and types into a query tree, the planner turns it into a plan of steps, and optimization passes rewrite the plan. Two rewrites matter here. The WHERE conditions are handed to the read step, which uses them to skip parts and granules (5.4), and they become a PREWHERE, which reads columns in steps (5.5).
The plan becomes a pipeline of processors, run by a pool of threads, one per CPU core by default. A large read splits into up to one stream per thread, and each stream aggregates its own rows before the results are combined. A small read, like this one, runs as a single stream.
Relevant Settings
max_threads,
optimize_move_to_prewhere
5.2. Sending to Shards
Without shards, the server runs the whole query on its local events. It reads its own replica's parts, which can lag behind the other replica.
events_distributed holds no data, so the query has to run on the shards:
It sends the query to every shard, even though only shard 2 has rows for site 7, because skipping shards is off by default.
For each shard it picks one replica: the local one when this server belongs to the shard, otherwise the replica with the fewest recent errors, and a random one when several are tied. Replicas that lag five minutes or more behind are skipped.
Each shard runs the query on its local
eventsup to an intermediate state of the aggregation and returns those states instead of rows.
The result: only small partial results cross the network. The tradeoff: every shard works on every query. With optimize_skip_unused_shards, the condition on the sharding key sends this query to shard 2 only.
Relevant Settings
optimize_skip_unused_shards,
load_balancing,
prefer_localhost_replica
5.3. Part Snapshot
Inserts and merges change the set of parts while a query runs. When the query opens events, it takes a snapshot of the Active parts and holds a reference to each. It reads only those parts, so parts committed later are invisible to it, and parts replaced during the query stay on disk at least until it ends.
The result: each query reads a consistent set of parts. The list of parts is locked only while it is copied, so inserts and merges continue during the read.
5.4. Partition Pruning and Primary Index
Most of a query's speed comes from data it does not read. Pruning works at two levels. First, whole parts: each part records its partition value and the earliest and latest ts it holds, so the October condition rules out the 202611 part without opening it.
Then granules inside each remaining part. site_id = 7 fixes the first key column and the October range bounds the second, so a binary search over the primary index finds the first and last granule that can hold matching rows.
On the 1,000,000-row part from 3.5, the search keeps granules 1 to 3. Granule 1 starts with site 3 and is still kept, because its last rows can be site 7: the index only knows where each granule starts. The tradeoff: a condition that skips the first key column, such as one on ts alone, cannot use a binary search. It can rule out only granules whose first and next keys have the same site_id, so it skips little when each site spans few granules.
5.5. Reading and Execution
A large read is split across threads in tasks of at least 24 granules, the larger of 163,840 rows / 8,192 = 20 granules and 240 MiB / 10 MiB = 24 granules, and a thread that finishes early takes work from another. The three granules here are a small read and run on one thread. For each granule, the reader uses the column's mark to find the compressed block, decompresses it, and reads from the granule's offset.
The PREWHERE from 5.1 runs as one step per condition column. The read takes ts and keeps the rows in October, then reads site_id for the granules that still have rows, then reads duration only for granules with a matching row. user_id and url are never read. This saves the most when a condition rejects every row of a granule, because the later columns of that granule are never read.
5.6. Rows Not Yet Combined and FINAL
MergeTree does not combine rows and does not support FINAL, so the query counts both views of 09:05, before and after merges: 3 views and an average of (12 + 95 + 20) / 3 = 42.33 seconds.
ReplacingMergeTree combines rows only when parts merge. Before the merge, the 09:05 view is in two parts, and the query counts it twice.
FINAL makes the read do the merge's work. Rows from parts whose key ranges overlap are combined on the fly with the same rule as a merge, within each partition, and ranges that overlap nothing are read as they are when their part has level 1 or higher, because such a part is already combined. The result: correct counts before merges finish, 2 views and an average of (95 + 20) / 2 = 57.5 seconds. The tradeoff: the cost grows with the amount of overlapping data, and without FINAL the result is exact only after the partition is fully merged. Rows with the same key are always on the same shard, because site_id is the sharding key, so FINAL on each shard is enough.
Relevant Settings
final,
do_not_merge_across_partitions_select_final,
enable_automatic_decision_for_merging_across_partitions_for_final
5.7. Combining Shard Results
The query runs on one server, so there are no shard results to combine.
The initiator merges the partial states it receives from the shards. avg travels as a sum and a count, so the average is computed only after the states of all shards are combined: shard 2 reports a sum of 12 + 95 + 20 = 127 seconds and a count of 3, shard 1 reports nothing, and the initiator computes 127 / 3 = 42.33.
6. Updates and Deletes
Changing a row means writing new data, because parts are immutable. ClickHouse can rewrite parts, mark rows as deleted, or drop whole parts, and each costs less than the one before. With ReplacingMergeTree, an update can also be a plain insert (6.2).
6.1. Mutations
ALTER TABLE ... UPDATE and ALTER TABLE ... DELETE change existing rows, and a mutation carries out the change in the background:
ALTER TABLE events ON CLUSTER main UPDATE url = '/docs/start' WHERE url = '/docs';
The mutation gets a version number from the same counter as block numbers. It is stored in a file in the table directory. It is stored in Keeper, with a number for each partition. It is stored in Keeper, with a number for each partition.
Every part whose data is older than that version is processed. Parts with matching rows are rewritten, and the others are hardlinked under the new name. Parts inserted later have higher block numbers and are left alone.
Each affected part is rewritten into a new part whose name ends with the mutation version. In a Wide part, only the changed columns are written and the other files are hardlinked. A Compact part, and any
ALTER TABLE ... DELETE, rewrites every column.The new part replaces the old one the same way a merged part does.
The result: the statement returns immediately, and the change appears part by part as the rewrites finish. The tradeoff: changing one row rewrites every part that can hold it, so mutations suit rare, bulk changes. The replica that runs the statement adds the mutation to Keeper. A replica that selects merges then writes a rewrite entry for each part to the log, and every replica runs them. events_distributed does not accept mutations, so they run on events with ON CLUSTER.
Relevant Settings
mutations_sync,
number_of_mutations_to_delay,
number_of_mutations_to_throw
6.2. Lightweight Deletes and Updates
DELETE FROM events WHERE ... marks rows instead of removing them. It runs as a mutation that writes one hidden column, _row_exists, with 0 for every deleted row. In a Wide part, every other file is hardlinked, so the delete writes one small column per part.
Queries skip marked rows right away, and the rows disappear for good the next time the part is merged. By default, the statement waits until the marks are written on every replica. On a sharded cluster, it runs on events with ON CLUSTER.
UPDATE events SET ... writes a small patch part that holds only the new values, and queries apply patches on read until a merge applies them for good. events as defined does not support it: the table needs enable_block_number_column and enable_block_offset_column, which add hidden columns for the position of each row, and both are off by default.
With ReplacingMergeTree, an update is an insert of the row with a higher ver, like the corrected 09:05 view in 4.1. A delete works the same way with an is_deleted column, which the table has to declare: a row inserted with is_deleted = 1 wins like any newer version, and queries with FINAL skip it.
Relevant Settings
lightweight_deletes_sync,
enable_block_number_column,
enable_block_offset_column
6.3. Dropping Partitions and TTL
The cheapest delete removes whole parts. ALTER TABLE events DROP PARTITION 202501 takes every part of a month out of the active set in one step, without reading any rows, and cleanup deletes the files later. The drop goes through the log, so every replica drops the same parts.
A delete TTL, such as TTL ts + INTERVAL 1 YEAR DELETE, removes rows by age during merges. A part whose rows have all expired is dropped whole. A part with some expired rows gets a merge that rewrites it without them, at most every four hours per partition.
The result: with a partition per month, removing a month of old data costs no more than a metadata change.
Relevant Settings
merge_with_ttl_timeout,
ttl_only_drop_parts
7. Storage Locations
7.1. Disks and Volumes
Fast disks are small and large disks are slow, so ClickHouse lets a table spread its parts over several. A storage policy lists volumes in order, and each volume is a group of disks. Without a storage configuration, there is one volume, default, with one local disk at /var/lib/clickhouse/, and every part so far lives there. A table can switch only to a policy that keeps the volumes of its current one, so a policy such as hot_cold keeps default as its first volume:
ALTER TABLE events ON CLUSTER main MODIFY SETTING storage_policy = 'hot_cold';
An insert writes its part to the first volume with room, and spreads parts across the disks of that volume in turn. A merge never writes to a volume before the volumes of its source parts.
A volume can cap the size of a part, so large merged parts go to the next volume.
When the policy has several volumes, a background task keeps 10% of each disk free, except on the last volume, by moving its largest parts to the next volume.
A move copies the part to the target disk and then swaps it in, so queries read the old copy until the switch.
Relevant Settings
storage_configuration (server config),
move_factor,
max_data_part_size_bytes,
background_move_pool_size
7.2. Object Storage
Object storage is cheap and durable, but an object cannot be renamed or appended to, and every commit renames a part directory. An s3 disk solves this by keeping the part's metadata on the local disk. Each file of a part is a small local file that lists the keys of its objects in the bucket, and the objects get random names.
Objects are never modified. Writing a file creates a new object, and renaming a part moves only local metadata files. Hardlinks, which mutations use for unchanged files, become reference counts in the metadata.
The tradeoff: the local metadata is the only map from parts to objects, so losing the local disk loses this server's copy of the table even though the data is still in the bucket. On a single server, that is the whole table. Each replica also writes its own copy of every object, because sharing objects between replicas, zero-copy replication, is experimental and off by default.
Relevant Settings
allow_remote_fs_zero_copy_replication,
metadata_type (disk config)
7.3. TTL Moves
Recent data is queried most, so older parts can live on cheaper storage:
ALTER TABLE events ON CLUSTER main MODIFY TTL ts + INTERVAL 30 DAY TO VOLUME 'cold';
A move rule applies to whole parts: a part moves when its newest row passes the TTL, so rows of one part never end up on two volumes. On 2026-11-20, the merged 202610 part, whose newest view is from 2026-10-20, moves to cold, because 2026-10-20 + 30 days = 2026-11-19 has passed. The 202611 part, whose newest view is from 2026-11-02, stays until 2026-11-02 + 30 days = 2026-12-02. On shard 1, the 202610 part holds only the 2026-10-14 view of site 42 and moves on 2026-10-14 + 30 days = 2026-11-13.
Parts are moved by the same background task that keeps disks free. A part that has already expired when it is inserted goes straight to the destination, and a merge writes its result there too.
7.4. Caches
Reading from disk, and even more from object storage, is slow compared to memory, so each server keeps several independent caches, from whole query results down to pieces of remote files:
Relevant Settings
use_query_cache,
use_query_condition_cache,
use_primary_key_cache,
mark_cache_size,
use_uncompressed_cache,
page_cache_max_size,
cache disk (server config)
8. End to End
s1-r1 routes the 09:05 view to shard 2. The view is buffered, split into the 202610 block, sorted, and written as a temporary part, then committed through Keeper and copied to the second replica. Two later inserts add parts, and a merge combines the three into one, where MergeTree keeps both versions of the viewReplacingMergeTree keeps only the version with ver 2. A query reads only the merged part, and cleanup removes the source parts when no query holds them.
9. References
[1] R. Schulze, T. Schreiber, I. Yatsishin, R. Dahimene, A. Milovidov, "ClickHouse - Lightning Fast Analytics for Everyone," Proc. VLDB Endow., vol. 17, no. 12, 2024. [Online]. Available: https://www.vldb.org/pvldb/vol17/p3731-schulze.pdf
[2] "ClickHouse - Lightning Fast Analytics for Everyone," VLDB 2024 talk, [Online]. Available: https://www.youtube.com/watch?v=7QXKBKDOkJE
[3] "ClickHouse source code," ClickHouse, Inc., [Online]. Available: https://github.com/ClickHouse/ClickHouse
[4] "Architecture overview," ClickHouse Docs, [Online]. Available: https://clickhouse.com/docs/development/architecture
[5] "MergeTree," ClickHouse Docs, [Online]. Available: https://clickhouse.com/docs/engines/table-engines/mergetree-family/mergetree
[6] "ReplacingMergeTree," ClickHouse Docs, [Online]. Available: https://clickhouse.com/docs/engines/table-engines/mergetree-family/replacingmergetree
[7] "Part merges," ClickHouse Docs, [Online]. Available: https://clickhouse.com/docs/merges
[8] "Asynchronous inserts," ClickHouse Docs, [Online]. Available: https://clickhouse.com/docs/optimize/asynchronous-inserts
[9] "Data replication," ClickHouse Docs, [Online]. Available: https://clickhouse.com/docs/engines/table-engines/mergetree-family/replication
[10] "Distributed table engine," ClickHouse Docs, [Online]. Available: https://clickhouse.com/docs/engines/table-engines/special/distributed
[11] "Mutations," ClickHouse Docs, [Online]. Available: https://clickhouse.com/docs/guides/developer/mutations
[12] "Lightweight delete," ClickHouse Docs, [Online]. Available: https://clickhouse.com/docs/guides/developer/lightweight-delete
Cite this article as: Adesh Nalpet Adimurthy. (Nov 4, 2026). ClickHouse Internals. PyBlog. https://www.pyblog.xyz/clickhouse-internals