At ClickHouse, we are constantly thinking about our getting started experience. ClickHouse is a complex and powerful piece of software that introduces new concepts for many, which can lead to mistakes. In this post, we highlight the most common 13 mistakes we see our new users encounter, why they occur and the correct approach.

For users encountering challenges managing ClickHouse at scale, [ClickHouse Cloud](https://clickhouse.cloud/signUp) automatically handles many of the common getting-started and subsequent scaling challenges.

## 1. Too many parts

An often-seen ClickHouse error:

```
DB::Exception: Too many parts
```

This error will often be experienced when inserting data and will be present in ClickHouse logs or in a response to an INSERT request.  To understand this error, users need to understand the concept of a part in ClickHouse.

A table in ClickHouse consists of data parts sorted by the user's specified primary key (by default, the ORDER BY clause on table creation but see [Index Design](https://clickhouse.com/docs/en/guides/improving-query-performance/sparse-primary-indexes/sparse-primary-indexes-design) for the details). When data is inserted in a table, separate [data parts](https://clickhouse.com/docs/parts) are created, and each of them is lexicographically sorted by primary key. For example, if the primary key is `(CounterID, Date)`, the data in the part is sorted first by `CounterID`, and within each `CounterID` value by `Date`. In the background, ClickHouse merges data parts for more efficient storage, similar to a [Log-structured merge tree](https://en.wikipedia.org/wiki/Log-structured_merge-tree). Each part has its own primary index to allow efficient scanning and identification of where values lie within the parts. When parts are merged, then the merged part's primary indexes are also merged.

![sins-01-parts.png](https://clickhouse.com/uploads/sins_01_parts_db7108e636.png)

As the number of parts increases, queries invariably will slow as a result of the need to evaluate more indices and read more files. Users may also experience slow startup times in cases where the part count is high. The creation of too many parts thus results in more internal merges and "pressure" to keep the number of parts low and query performance high. While merges are concurrent, in cases of misuse or misconfiguration, the number of parts can exceed internal configurable limits ([parts_to_throw_insert](https://clickhouse.com/docs/en/operations/settings/merge-tree-settings/#parts_to_throw_insert), [max_parts_in_total](https://clickhouse.com/docs/en/operations/settings/merge-tree-settings/#max_parts_in_total)). While these limits can be adjusted, at the expense of query performance, the need to do so will more often point to issues with your usage patterns. As well as causing query performance to degrade, high part counts can also place greater pressure on ClickHouse Keeper in replicated configurations.

So, how is it possible to have too many of these parts?

### Poorly chosen partitioning key

A common reason is using a partition key with excessive [cardinality](https://en.wikipedia.org/wiki/Cardinality). On creating a table, users can optionally specify a column as a partition key by which data will be separated. A new file system directory will be created for every key value. This is typically a data management technique, allowing users to cleanly separate data logically in a table. Operations such as DROP PARTITION subsequently allow fast deletion of data subsets. This powerful feature can, however, easily be misused, with users interpreting it as a simple optimization technique for queries. 

Importantly, parts belonging to different partitions are never merged. If a key of high cardinality, e.g., `date_time_ms`, is chosen as a partition key then parts spread across thousands of folders will never be merge candidates - exceeding preconfigured limits and causing the "Too many inactive parts (N). Parts cleaning are processing significantly slower than inserts" error on subsequent INSERTs. Addressing this problem is simple: choose a [partition key](https://clickhouse.com/docs/optimize/partitioning-key) with cardinality < 1000.

![sins-02-partitioning.png](https://clickhouse.com/uploads/sins_02_partitioning_14b984c2bf.png)

### Many small inserts

As well as the poor selection of a partition key, this issue can manifest itself as a result of many small inserts. Each INSERT into ClickHouse results in an insert block being converted to a part. To keep the number of parts manageable, users should therefore buffer data client-side and insert data as [batches](https://clickhouse.com/docs/optimize/bulk-inserts) - at a minimum 1,000 rows per insert, although batch sizes of 10,000 to 100,000 rows are optimal. If client-side buffering is not possible, users can defer this task to ClickHouse through [async inserts](https://clickhouse.com/docs/optimize/asynchronous-inserts). In this case, ClickHouse will buffer inserts in memory before flushing them as a single batched part into the underlying table. The flush is triggered when a configurable threshold is met: a buffer size limit (`async_insert_max_data_size`, default 1MB), a time threshold (`async_insert_busy_timeout_ms`, default 1 second), or a maximum number of queued queries (`async_insert_max_query_number`, default 100). Since data is held in memory until flush, it is important to set `wait_for_async_insert=1` (the default) so that the client receives acknowledgement only after data has been safely written to disk, avoiding silent data loss in the event of a server crash before a flush.

![sins-03-async_inserts.png](https://clickhouse.com/uploads/sins_03_async_inserts_f51b9d0035.png)

[Buffer tables](https://clickhouse.com/docs/en/engines/table-engines/special/buffer/) exist as a legacy alternative. Their unique advantage is that data is queryable while still in the buffer, before flush to the destination table. However, Buffer tables have significant drawbacks: they are not replicated, are not compatible with FINAL or SAMPLE, and can lose data on abnormal server restarts. In ClickHouse Cloud with SharedMergeTree, each node maintains independent buffer state, adding further complexity. For nearly all use cases, async inserts are the recommended approach. Buffer tables should only be considered in rare scenarios where querying in-buffer data before flush is a hard requirement.

### Excessive materialized views

Other possible causes of this error are excessive materialized views. Materialized views are, in effect, a trigger that runs when a block is inserted into a table. They transform the data e.g., through a GROUP BY, before inserting the result into a different table. This technique is often used to accelerate certain queries by precomputing aggregations at INSERT time. Users can create these materialized views, potentially resulting in many parts. Generally, we recommended that users create views while being aware of the costs and consolidate them where possible.

![sins-04-mvs.png](https://clickhouse.com/uploads/sins_04_mvs_1f872af2fa.png)

The above list is not an exhaustive cause of this error. For example, mutations (as discussed below) can also cause merge pressure and an accumulation of parts. Finally, we should note that this error, while the most common, is only one manifestation of the above misconfigurations. For example, users can experience other issues as a result of a poor partitioning key. These include, but are not limited to, "no free inodes on the filesystem", backups taking a long time, and delays on replication (and high load on ClickHouse Keeper).

## 2. Going horizontal too early

We often have new self-managed users asking us to provide recommendations around orchestration and how to scale to dozens, if not hundreds, of nodes. While technologies such as Kubernetes have made the deployment of multiple instances of stateless applications relatively simple, this pattern should, in nearly all cases, not be required for ClickHouse. Unlike other databases, which may be restricted to a machine size due to inherent limits, e.g., JVM heap size, ClickHouse was designed from the ground up to utilize the full resources of a machine. We commonly find successful deployments with ClickHouse deployed on servers with hundreds of cores, terabytes of RAM, and petabytes of disk space. Most analytical queries have a sort, filter, and aggregation stage. Each of these can be parallelized independently and will, by default, use as many [threads as cores](https://clickhouse.com/docs/operations/settings/settings#max_threads), thus utilizing the full machine resources for a query.

![sins-05-vertical_scale.png](https://clickhouse.com/uploads/sins_05_vertical_scale_d36926028c.png)

Scaling vertically first has a number of benefits, principally cost efficiency, lower cost of ownership (with respect to operations), and better query performance due to the minimization of data on the network for operations such as JOINs. Of course, users need redundancy in their infrastructure, but two machines should be sufficient for all but the largest use cases.

ClickHouse Cloud supports both vertical scaling (increasing replica size) and horizontal scaling (adding more replicas), with its compute-storage separation architecture ([SharedMergeTree](https://clickhouse.com/docs/cloud/reference/shared-merge-tree)) making both approaches seamless. For details on scaling options, see the [Cloud scaling documentation](https://clickhouse.com/docs/manage/scaling). We strongly recommend considering vertical scaling first, before horizontal. In summary, go vertical before going horizontal!

For users encountering challenges managing ClickHouse at scale, ClickHouse Cloud automatically handles many of the common getting-started and subsequent scaling challenges.

Get started today

## 3. Mutation Pain

While rare in OLAP use cases, the need to modify data is sometimes unavoidable. ClickHouse [performs best on immutable data](https://clickhouse.com/docs/optimize/avoid-mutations), and any design pattern which requires data to be updated post-insert should be reviewed carefully. That said, ClickHouse provides two mechanisms for modifying data in place:

* **Classic mutations** via [ALTER TABLE ... UPDATE](https://clickhouse.com/docs/en/sql-reference/statements/alter/update/) — these rewrite entire data parts and are suited to infrequent, bulk changes.
* **Lightweight updates** via [UPDATE](https://clickhouse.com/docs/sql-reference/statements/update) powered by **patch parts** — these write only the changed column values as compact delta parts, making them far more efficient for frequent or targeted updates.

### Classic mutations

Classic mutations work by rewriting whole data parts containing the affected columns. This process relies on the same thread pool as merges. In self-managed replicated environments, each replica must apply the mutation independently. For this reason, mutations are both CPU and IO-intensive and should be scheduled cautiously with permission to run limited to administrators. Resource pressure as a result of mutations manifests itself in several ways. Typically, normally scheduled merges accumulate, which in turn causes our earlier "too many parts" issue. Furthermore, users may experience replication delays. The [system.mutations](https://clickhouse.com/docs/en/operations/system-tables/mutations#system_tables-mutations) table should give administrators an indication of currently scheduled mutations. Note that mutations can be cancelled, but not rolled back, with the [KILL MUTATION ](https://clickhouse.com/docs/en/sql-reference/statements/kill#kill-mutation)query.

![sins-06-mutations.png](https://clickhouse.com/uploads/sins_06_mutations_647f7d67d9.png)

### Lightweight updates with patch parts

[Lightweight updates](https://clickhouse.com/docs/sql-reference/statements/update) take a fundamentally different approach. Instead of rewriting entire data parts, a lightweight update creates a small, compact "patch part" containing only the changed column values and metadata to locate the affected rows. These patches are applied on-the-fly during reads (so changes are visible immediately) and materialized efficiently during regular background merges — piggybacking on work ClickHouse is already doing. This can be [up to 1,000× faster](https://clickhouse.com/blog/updates-in-clickhouse-2-sql-style-updates) than classic mutations for many workloads. The same mechanism can be used for deletes, which set a `_row_exists = 0` mask via a patch part rather than rewriting columns. For a deep dive into how patch parts work, see our series on fast UPDATEs in ClickHouse: [Part 1 — purpose-built engines](https://clickhouse.com/blog/updates-in-clickhouse-1-purpose-built-engines) and [Part 2 — SQL-style updates](https://clickhouse.com/blog/updates-in-clickhouse-2-sql-style-updates).

### Mutations in ClickHouse Cloud

In [ClickHouse Cloud](https://clickhouse.cloud/signUp), tables use the [SharedMergeTree](https://clickhouse.com/docs/cloud/reference/shared-merge-tree) engine, which stores data in shared object storage with metadata coordinated through ClickHouse Keeper. This architecture changes the mutation picture: since replicas don't communicate directly with each other and all data lives in shared storage, mutations don't need to be independently applied on each replica — the rewritten parts are written to shared storage and become visible to all replicas through metadata updates. This leads to faster mutation execution and eliminates the replication delays that self-managed clusters can experience during heavy mutation workloads. Both classic mutations and lightweight updates with patch parts are available in ClickHouse Cloud.

### Deduplication

We often see users needing to schedule merges as a result of duplicate data. Typically we suggest users address this issue upstream and deduplicate prior to insertion into ClickHouse. If this is not possible, users have a number of options: deduplicate at query time or utilize a [ReplacingMergeTree](https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/replacingmergetree/).

Deduplicating at query time can be achieved by grouping the data on the fields, which uniquely identify a row, and using the [argMax](https://clickhouse.com/docs/en/sql-reference/aggregate-functions/reference/argmax/) function with a date field to identify the last value for other fields. [ReplacingMergeTree](https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/replacingmergetree/) allows rows with the same sorting key (ORDER BY key) to be deduplicated on merges. Note this is "best effort" only: sometimes parts will not be merged with the merge process scheduled at non-deterministic intervals. It, therefore, does not guarantee the absence of duplicates. Users can also utilize the [FINAL](https://clickhouse.com/docs/engines/table-engines/mergetree-family/replacingmergetree#query-time-de-duplication--final) modifier to force this deduplication at `SELECT` time. While FINAL does add overhead, it has received significant performance improvements in recent versions including multi-threaded processing, and is suitable for many production workloads.

In the case where data needs to be deleted from ClickHouse e.g., for compliance or deduplication reasons, users can also utilize lightweight deletes instead of mutations. These take the form of a [DELETE statement](https://clickhouse.com/docs/en/sql-reference/statements/delete/) which accepts WHERE clause to filter rows. This marks rows as deleted only. These marks will be used to filter rows out at query time and will be removed when parts are merged.

![sins-07-lightweight_deletes.png](https://clickhouse.com/uploads/sins_07_lightweight_deletes_e11f6a6988.png)

Lightweight deletes are a production-ready feature and are more efficient than using a mutation in most cases, with the exception of if you are doing a large-scale bulk delete. Note that lightweight deletes are not currently compatible with tables that have projections.

## 4. Working with complex and semi-structured data

As well as supporting the usual primitive types, ClickHouse has rich support for complex types such as [Nested](https://clickhouse.com/docs/en/sql-reference/data-types/nested-data-structures/nested/#nestedname1-type1-name2-type2-), [Tuple](https://clickhouse.com/docs/en/sql-reference/data-types/tuple), [Map](https://clickhouse.com/docs/en/sql-reference/data-types/map), and [JSON](https://clickhouse.com/docs/en/sql-reference/data-types/json). The general principle is simple: if you know your data structure, explicitly defined columns will always give you the best compression, insert performance, and query speed. But when your data is genuinely semi-structured or schema-evolving, ClickHouse's native JSON type is purpose-built for the job.

### The JSON type for semi-structured data

ClickHouse provides a [native JSON data type](https://clickhouse.com/docs/en/sql-reference/data-types/json) built to [optimise storage and processing of JSON data](https://clickhouse.com/blog/json-bench-clickhouse-vs-mongodb-elasticsearch-duckdb-postgresql). Under the hood, ClickHouse stores JSON paths as dedicated subcolumns — transparently flattening your JSON into real columns with full compression and vectorized processing.

[By default, up to 1,024 unique paths are stored as dedicated subcolumns](https://clickhouse.com/docs/sql-reference/data-types/newjson#reaching-the-limit-of-dynamic-paths-inside-json). Paths beyond this limit are [stored in an efficient shared data structure](https://clickhouse.com/blog/json-data-type-gets-even-better).

JSON subcolumns can be used in sorting key expressions and data-skipping indexes, enabling the same query optimizations available to regular columns.

That said, the JSON type is not a substitute for schema design. For columns with a known, stable structure, explicit column definitions will always provide the best performance. The right approach is to use explicit columns for the parts of your data you understand, and the JSON type for the parts that are genuinely dynamic.

## 5. Overuse of Nullable

We often see users reaching for the [Nullable](https://clickhouse.com/docs/sql-reference/data-types/nullable) type, which allows columns to store a Null value. Under the hood, this creates a separate column of UInt8 type. This additional column has to be processed every time a user works with a Nullable column. This leads to additional storage space used and almost always negatively affects performance.

In most cases, we recommend to [avoid using Nullable columns](https://clickhouse.com/docs/optimize/avoid-nullable-columns) entirely. Instead, use a default value that represents a Null, e.g., an empty string for String columns. 

## 6. Deduplication at insert time

New users to ClickHouse are often surprised by ClickHouse's [deduplication strategy](https://clickhouse.com/docs/guides/developer/deduplication). This usually occurs when identical inserts appear to not have any effect. For example, consider the following:

<pre><code type='click-ui' language='sql'>
CREATE TABLE temp
(
   `timestamp` DateTime,
   `value` UInt64
)
ENGINE = MergeTree
ORDER BY tuple()

INSERT INTO temp VALUES ('2022-10-21', 10), ('2022-10-22', 20), ('2022-10-23', 15), ('2022-10-24', 18)
INSERT INTO temp VALUES ('2022-10-21', 10), ('2022-10-22', 20), ('2022-10-23', 15), ('2022-10-24', 18)

clickhouse-cloud :) SELECT * FROM temp

SELECT *
FROM temp

┌───────────timestamp─┬─value─┐
│ 2022-10-21 00:00:00 │    10 │
│ 2022-10-22 00:00:00 │    20 │
│ 2022-10-23 00:00:00 │    15 │
│ 2022-10-24 00:00:00 │    18 │
└─────────────────────┴───────┘
</pre></code>

Notice that, in the example above, 8 rows were inserted, but only 4 rows are shown by the SELECT. A new user might be surprised by the result here. This behavior is the result of the [`replicated_deduplication_window`](https://clickhouse.com/docs/operations/settings/merge-tree-settings#replicated_deduplication_window) setting.

When data is inserted into ClickHouse, it creates [one or more](https://clickhouse.com/docs/operations/settings/settings#max_insert_block_size) blocks (parts). In replicated environments, such as [ClickHouse Cloud](https://clickhouse.cloud/signUp), a hash is also written in ClickHouse Keeper. Subsequent inserted blocks are compared against these hashes and ignored if a match is present. This is useful since it allows clients to safely retry inserts in the event of no acknowledgement from ClickHouse e.g., because of a network interruption. This requires blocks to be identical i.e., the same size with the same rows in the same order. These hashes are stored for only the most recent 100 blocks, although this can be [modified](https://clickhouse.com/docs/operations/settings/merge-tree-settings#replicated_deduplication_window). Note higher values will slow down inserts due to the need for more comparisons.

![sins-08-deduplication.png](https://clickhouse.com/uploads/sins_08_deduplication_c9f39896d5.png)

This same behavior can be enabled for non-replicated instances via the setting [`non_replicated_deduplication_window`](https://clickhouse.com/docs/operations/settings/merge-tree-settings#non_replicated_deduplication_window). In this case, the hashes are stored on a local disk.

## 7. Poor Primary Key Selection

Users new to ClickHouse often struggle to fully understand its primary key concepts. Unlike [B(+)-Tree]((https://en.wikipedia.org/wiki/B%2B_tree))-based OLTP databases, which are optimized for fast location of specific rows, [ClickHouse utilizes a sparse index](https://clickhouse.com/docs/optimize/sparse-primary-indexes) designed for millions of inserted rows per second and petabyte-scale datasets.  In contrast to OLTP databases, this index relies on the data on disk being sorted for fast identification of groups of rows that could possibly match a query - a common requirement in analytical queries. The index, in effect, allows the matching sections of part files to be rapidly identified before they are streamed into the processing engine. For more detail on the layout of the data on disk, we highly [recommend this guide](https://clickhouse.com/docs/en/guides/improving-query-performance/sparse-primary-indexes/sparse-primary-indexes-design/#data-is-stored-on-disk-ordered-by-primary-key-columns).

![sins-09-primary_index.png](https://clickhouse.com/uploads/sins_09_primary_index_d3f166fa7d.png)

The effectiveness of this approach, for both query performance and compression, relies on the user [selecting good primary key](https://clickhouse.com/docs/best-practices/choosing-a-primary-key) columns via the ORDER BY clause when creating a table. In general, users should select columns for which they will often filter tables with more than 2 to 3 columns rarely required. The order of these columns is critical and can affect the compression and filtering by columns other than the first entry. For both the efficient filtering of secondary key columns in queries and the compression ratio of a table's column files, it is optimal to order the columns in a primary key by their cardinality in ascending order. A full explanation of the reasoning can be found [here](https://clickhouse.com/docs/en/guides/improving-query-performance/sparse-primary-indexes/sparse-primary-indexes-cardinality).

In this post, we highlight the most common 13 mistakes we see our new users encounter, why they occur and the correct approach.

Geoff Genz

Blog posts by Geoff Genz

Getting started with ClickHouse? 13 mistakes and how to avoid them