Tables and Parts
ClickHouse® stores every MergeTree table as a set of files on disk called parts. Understanding parts explains a lot of ClickHouse® behavior that otherwise looks arbitrary, including why you should batch inserts and why a table can suddenly refuse writes.
Open it from Overview > Tables & Parts in the sidebar. The page gives you two views on one screen: a health check on the parts themselves, and a breakdown of how much space each table uses.
Contents
- What a part is
- The parts summary
- The four part states
- Broken parts
- The Table Details table
- Reading the compression numbers
- What the part counts tell you
- When something does not work
1. What a part is
Every insert into a MergeTree table creates a new part: a directory of files that holds those rows, sorted by the table's sort key.
Parts are immutable: ClickHouse® never writes into an existing part, so new data always becomes a new part.
In the background, ClickHouse® merges small parts into larger ones. That is where the engine name comes from, and it is why the number of parts goes down on its own, without anybody doing anything.
Why this matters in practice
Many small inserts create many small parts. A thousand single-row inserts create a thousand parts, and ClickHouse® then has to merge them all. This is why "batch your inserts" is the most repeated piece of ClickHouse® advice.
Too many parts finally stops writes. ClickHouse® has a limit, and when a table goes above it, inserts fail with a "too many parts" error. It is not gradual: things work, then they do not.
Queries read every part that might match. Fewer, larger parts mean less to open. This is why merges improve read performance as well as tidiness.
The parts summary is how you see this happen before it becomes a problem.
The parts summary
The page opens with four cards that count your parts: Active Parts, Inactive Parts, Detached Parts, and Broken Parts. CHOps reads them from system.parts and system.detached_parts.
If there are any broken parts, the section Tables with Broken Parts (disk issues) appears. Its chart, Broken Parts by Table, shows how many broken parts each table has.
If there are any detached parts, the chart Detached Parts by Table shows how many detached parts each table has.
Each chart shows at most 20 tables, with the most affected first.
3. The four part states
| State | Meaning | Normal? |
|---|---|---|
| Active | In use, serving queries | Yes, this is most of them |
| Inactive | Merged away, awaiting cleanup | Yes, routine |
| Detached | Set aside, not in use | Usually |
| Broken | Set aside because something is wrong | No |
Active
The parts that make up your table right now. Every query reads from these.
Inactive
When ClickHouse® merges several parts into one, the originals do not disappear at once. It marks them inactive and removes them a little later.
A number of inactive parts is normal and not a problem. Seeing them means merging has been happening, which is what you want, and ClickHouse® cleans them up on its own.
A count that keeps climbing and never falls is worth a look, because it suggests cleanup is not completing.
Detached
Parts moved out of the table's active set but kept on disk. This can happen because someone ran DETACH PARTITION, or because ClickHouse® set a part aside during a restore or a consistency check.
Detached parts still use disk, but queries do not read them, so they can be a quiet source of "the table is small but the disk is full".
Broken
The ones to care about. See below.
4. Broken parts
CHOps flags a detached part as broken when its name begins with "broken".
That is ClickHouse®'s own label, not a judgement CHOps makes. The server detached the part because it failed a check.
What it usually means
Data corruption, most often from disk problems. A checksum that did not match, a file that could not be read, a part that failed validation on startup.
Investigate, do not wait. A single broken part often means the disk that holds it is failing, and the next symptom is worse than one detached part.
What to do
- Check the disk. System logs and SMART data for the device that holds that table's data.
- Check whether the data is elsewhere. On a replicated table, another replica almost certainly holds a good copy, and ClickHouse® can fetch it.
- Look at the Error Log and Text Log around the time the server detached it. The server usually says why.
- Do not simply delete it until you know the data exists elsewhere. A broken part is still evidence.
On a replicated table, the usual fix is to drop the broken part and let replication fetch a clean copy. On a non-replicated table, the data in that part may be gone, which is when your backups matter.
The Table Details table
The Table Details table lists every table that has parts in system.parts, largest first by compressed size.
| Column | Meaning |
|---|---|
| database, table | The full name |
| compressed | Space used on disk |
| uncompressed | Space it would take without compression |
| compression_pct | Compressed as a percentage of uncompressed |
| ratio | How many times smaller compression made it |
| engine | MergeTree, ReplicatedMergeTree, and so on |
| active_parts, inactive_parts | Counts of each |
| total_rows | Rows across all parts |
Reading the table to answer a question
Largest first answers "what is using my disk", which is the usual question.
Look for a low ratio to find tables that compress badly, which is where schema improvements pay off most.
Look for a high active_parts count to find tables that accumulate parts, which is where insert patterns need attention.
6. Reading the compression numbers
This is often the most interesting part of the page.
A high ratio, say 10x or more, means the data compresses well: repetitive values, sensible types, good sort order.
A low ratio, near 1x, means it barely compresses. This is worth investigating, because it is usually fixable and the fix is often large.
Common causes of poor compression
Types wider than the data needs. A String that always holds one of five values should be LowCardinality(String) or an Enum. An Int64 that holds numbers below a thousand should be UInt16.
A sort order that does not group similar values. Compression works on adjacent data. If your ORDER BY scatters similar rows apart, they cannot compress against each other. Sorting by a low-cardinality column before a high-cardinality one usually helps.
Genuinely random data. Hashes, UUIDs, encrypted blobs, and compressed images do not compress, and nothing will make them. A ratio near 1x on a column of UUIDs is correct, not a problem.
Timestamps stored as strings. This is a very common one. DateTime compresses far better than the same value as text.
Using it
Pick your largest table, look at its ratio, and if it is poor, look at its schema. On a table of any size, the saving from a type change can be large, and it costs nothing at query time.
7. What the part counts tell you
The relationship between active and inactive parts is a diagnostic.
Few active, some inactive. Healthy. Merging keeps up and cleans up.
Many active, few inactive. Merges are not keeping up with inserts, which usually means inserts are too small and too frequent. Check Merges & Mutations to see whether merges run or are stuck.
Many inactive, not falling. Merging happens but cleanup does not complete, which is less common. Check disk space, because cleanup needs somewhere to work.
Active climbing steadily. This is the direction that ends in a "too many parts" error. Act before it arrives: batch your inserts, or check whether merges are blocked.
A rough guide
A few dozen active parts per table is unremarkable. Several hundred is worth a look. Over a thousand and you are heading for trouble.
Those are rough figures. The number that matters is whether it is stable or climbing.
8. When something does not work
The broken parts chart appeared
Do not ignore it. See section 4. Check the disk first, because one broken part is often the first sign of a failing device.
A table shows a huge number of inactive parts
Merging runs but cleanup is behind. Check free disk space, and check Merges & Mutations for whether merges complete.
Compressed size is larger than I expect from total_rows
There are three usual causes: poor compression (see section 6), many inactive parts that still use space, and detached parts, which use disk but appear in no query.
A table is missing from Table Details
The list covers tables that have parts in system.parts, which means MergeTree family tables. Log engines, Memory tables, dictionaries, and views are not part-based and do not appear.
total_rows looks wrong
It counts rows across parts, which includes rows superseded but not yet merged away. On a ReplacingMergeTree or CollapsingMergeTree, that is legitimately higher than the logical row count, because the old versions still exist until ClickHouse® merges them.
Disk is full but the tables look small
Check detached parts, which use space and appear in no query.
Also check inactive parts, and whether merges complete at all.
Related pages
- Merges & Mutations for the background operations that turn many parts into fewer
- Cluster Overview for disk usage across the cluster
- Logs for what the server said when a part was detached
- Indexes and Projections when the answer is to read less rather than store less
- Backups for when a broken part holds data that exists nowhere else