ClickHouse/clickhouse-docs-embeddings huggingface dataset:
title_wip

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/best-practices/index.md)

This section provides the best practices you will want to follow to get the most out of ClickHouse.

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Page Description|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | [Choosing a Primary Key](/docs/best-practices/choosing-a-primary-key) How to select primary keys that maximize query performance and minimize storage overhead.|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | [Select Data Types](/docs/best-practices/select-data-types) Choose optimal data types to reduce memory usage, improve compression, and accelerate queries.|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | [Use Materialized Views](/docs/best-practices/use-materialized-views) Leverage materialized views to pre-aggregate data and dramatically speed up analytical queries.|  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | [Minimize and Optimize JOINs](/docs/best-practices/minimize-optimize-joins) Best practices for using ClickHouse's `JOIN` capabilities efficiently.| [Choosing a Partitioning Key](/docs/best-practices/choosing-a-partitioning-key) Select partitioning strategies that enable efficient data pruning and faster query execution.|  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | [Selecting an Insert Strategy](/docs/best-practices/selecting-an-insert-strategy) Optimize data ingestion throughput and reduce resource consumption with proper insert patterns.|  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | | [Data Skipping Indices](/docs/best-practices/use-data-skipping-indices-where-appropriate) Apply secondary indices strategically to skip irrelevant data blocks and accelerate filtered queries.|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | [Avoid Mutations](/docs/best-practices/avoid-mutations) Design schemas and workflows that eliminate costly `UPDATE`/`DELETE` operations for better performance.| [Avoid OPTIMIZE FINAL](/docs/best-practices/avoid-optimize-final) Prevent performance bottlenecks by understanding when `OPTIMIZE FINAL` hurts more than it helps.| [Use JSON where appropriate](/docs/best-practices/use-json-where-appropriate) Balance flexibility and performance when working with semi-structured JSON data in ClickHouse. | | | | | | | | | | | | | | | | | | | | | |

Was this page helpful?
Choosing a Primary Key

13 rows in set. Elapsed: 0.004 sec.
```

Additionally, we visualize how the sparse index prunes all row blocks that can't possibly contain matches for our example query:

![Primary key](/docs/assets/images/primary_key-2ec054f1a9f4de7dcdaea8f2c584e854.gif)

Note

All columns in a table will be sorted based on the value of the specified ordering key, regardless of whether they're included in the key itself. For instance, if `CreationDate` is used as the key, the order of values in all other columns will correspond to the order of values in the `CreationDate` column. Multiple ordering keys can be specified - this will order with the same semantics as an `ORDER BY` clause in a `SELECT` query.

A complete advanced guide on choosing primary keys can be found [here](/docs/guides/best-practices/sparse-primary-indexes).

For deeper insights into how ordering keys improve compression and further optimize storage, explore the official guides on [Compression in ClickHouse](/docs/data-compression/compression-in-clickhouse) and [Column Compression Codecs](/docs/data-compression/compression-in-clickhouse#choosing-the-right-column-compression-codec).

- [Example](#example)

Was this page helpful?
Selecting data types

--- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `ParentId` No - 20696028 Yes Consider Null to be an empty string String|  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `CommunityOwnedDate` No 2008-08-12 04:59:35.017000000, 2024-04-01 05:36:41.380000000 - Yes Consider default 1970-01-01 for Nulls. Millisecond granularity isn't required, use DateTime DateTime|  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | | `ClosedDate` No 2008-09-04 20:56:44, 2024-04-06 18:49:25.393000000 - Yes Consider default 1970-01-01 for Nulls. Millisecond granularity isn't required, use DateTime DateTime | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
Selecting data types

Tip

Identifying the type for a column relies on understanding its numeric range and number of unique values. To find the range of all columns, and the number of distinct values, you can use the simple query `SELECT * APPLY min, * APPLY max, * APPLY uniq FROM table FORMAT Vertical`. We recommend performing this over a smaller subset of the data as this can be expensive.

This results in the following optimized schema (with respect to types):

```
CREATE TABLE posts
(
   Id Int32,
   PostTypeId Enum('Question' = 1, 'Answer' = 2, 'Wiki' = 3, 'TagWikiExcerpt' = 4, 'TagWiki' = 5, 
   'ModeratorNomination' = 6, 'WikiPlaceholder' = 7, 'PrivilegeWiki' = 8),
   AcceptedAnswerId UInt32,
   CreationDate DateTime,
   Score Int32,
   ViewCount UInt32,
   Body String,
   OwnerUserId Int32,
   OwnerDisplayName String,
   LastEditorUserId Int32,
   LastEditorDisplayName String,
   LastEditDate DateTime,
   LastActivityDate DateTime,
   Title String,
   Tags String,
   AnswerCount UInt16,
   CommentCount UInt8,
   FavoriteCount UInt8,
   ContentLicense LowCardinality(String),
   ParentId String,
   CommunityOwnedDate DateTime,
   ClosedDate DateTime
)
ENGINE = MergeTree
ORDER BY tuple()
```

## Avoid nullable columns[​](#avoid-nullable-columns "Direct link to Avoid nullable columns")

[`Nullable` column](/docs/sql-reference/data-types/nullable) (e.g. `Nullable(String)`) 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.

To avoid `Nullable` columns, consider setting a default value for that column. For example, instead of:

```
CREATE TABLE default.sample
(
    `x` Int8,
    -- highlight-next-line
    `y` Nullable(Int8)
)
ENGINE = MergeTree
ORDER BY x
```

use

```
CREATE TABLE default.sample2
(
    `x` Int8,
    -- highlight-next-line
    `y` Int8 DEFAULT 0
)
ENGINE = MergeTree
ORDER BY x
```

Consider your use case; a default value may be inappropriate.

- [Example](#example)- [Avoid nullable columns](#avoid-nullable-columns)

Was this page helpful?
Use data skipping indices where appropriate

29 rows in set. Elapsed: 0.211 sec.
```

We also show an animation how the minmax skipping index prunes all row blocks that can't possibly contain matches for the `ViewCount` > 10,000,000 predicate in our example query:

![Using skipping indices](/docs/assets/images/using_skipping_indices-bb0da0780cb9a1590b323e198a336b10.gif)

## Related docs[​](#related-docs "Direct link to Related docs")

- [Data skipping indices guide](/docs/optimize/skipping-indexes)
- [Data skipping index examples](/docs/optimize/skipping-indexes/examples)
- [Manipulating data skipping indices](/docs/sql-reference/statements/alter/skipping-index)
- [System table information](/docs/operations/system-tables/data_skipping_indices)

- [Example](#example)- [Related docs](#related-docs)

Was this page helpful?
Use JSON where appropriate

We can now infer the types of the subcolumn `tags`.

```
SELECT JSONAllPathsWithTypes(tags)
FROM arxiv
FORMAT PrettyJSONEachRow

{
  "JSONAllPathsWithTypes(tags)": {
    "28_03_2025.comment": "String",
    "28_03_2025.name": "String",
    "28_03_2025.score": "Int64",
    "28_03_2025.updates": "Array(JSON(max_dynamic_types=16, max_dynamic_paths=256))",
    "tag_1.comment": "String",
    "tag_1.name": "String",
    "tag_1.score": "String"
  }
}

1 row in set. Elapsed: 0.002 sec.
```

- [When to use the `JSON` Type](#when-to-use-json-type)
  - [Use the `JSON` type when:](#use-json-type)- [Use a `String` column (or structured types) when:](#use-string-type)- [Considerations and tips for using JSON](#considerations-and-tips-for-using-json)- [Advanced features](#advanced-features)- [Examples](#examples)

Was this page helpful?
title_wip

[## Cloud

Learn about Cloud Changelog](/docs/whats-new/changelog/cloud)

[## 2026

Changelog for 2026](/docs/whats-new/changelog)

[## 2025

Changelog for 2025](/docs/whats-new/changelog/2025)

[## 2024

Changelog for 2024](/docs/whats-new/changelog/2024)

[## 2023

Changelog for 2023](/docs/whats-new/changelog/2023)

[## 2022

Changelog for 2022](/docs/whats-new/changelog/2022)

[## 2021

Changelog for 2021](/docs/whats-new/changelog/2021)

[## 2020

Changelog for 2020](/docs/whats-new/changelog/2020)

[## 2019

Changelog for 2019](/docs/whats-new/changelog/2019)

[## 2018

Changelog for 2018](/docs/whats-new/changelog/2018)

[## 2017

Changelog for 2017](/docs/whats-new/changelog/2017)
title_wip

- Adds argument `scale` (default: `true`) to function `arrayAUC` which allows to skip the normalization step (issue [#69609](https://github.com/ClickHouse/ClickHouse/issues/69609)). [#69717](https://github.com/ClickHouse/ClickHouse/pull/69717) ([gabrielmcg44](https://github.com/gabrielmcg44)).
- Re-added `RIPEMD160` function, which computes the RIPEMD-160 cryptographic hash of a string. Example: `SELECT HEX(RIPEMD160('The quick brown fox jumps over the lazy dog'))` returns `37F332F68DB77BD9D7EDD4969571AD671CF9DD3B`. [#70087](https://github.com/ClickHouse/ClickHouse/pull/70087) ([Dergousov Maxim](https://github.com/m7kss1)).
- Allow to cache read files for object storage table engines and data lakes using hash from ETag + file path as cache key. [#70135](https://github.com/ClickHouse/ClickHouse/pull/70135) ([Kseniia Sumarokova](https://github.com/kssenii)).
- Support reading Iceberg tables on HDFS. [#70268](https://github.com/ClickHouse/ClickHouse/pull/70268) ([flynn](https://github.com/ucasfl)).
- Allow to read/write JSON type as binary string in RowBinary format under settings `input_format_binary_read_json_as_string/output_format_binary_write_json_as_string`. [#70288](https://github.com/ClickHouse/ClickHouse/pull/70288) ([Pavel Kruglov](https://github.com/Avogar)).
- Allow to serialize/deserialize JSON column as single String column in Native format. For output use setting `output_format_native_write_json_as_string`. For input, use serialization version `1` before the column data. [#70312](https://github.com/ClickHouse/ClickHouse/pull/70312) ([Pavel Kruglov](https://github.com/Avogar)).
- Supports standard CTE, `with insert`, as previously only supports `insert ... with ...`. [#70593](https://github.com/ClickHouse/ClickHouse/pull/70593) ([Shichao Jin](https://github.com/jsc0218)).
title_wip

- Add indexOfAssumeSorted function for array types. Optimizes the search in the case of a sorted in non-decreasing order array. [#72517](https://github.com/ClickHouse/ClickHouse/pull/72517) ([Eric Kurbanov](https://github.com/erickurbanov)).
- Allows to use a delimiter as a optional second argument for aggregate function `groupConcat`. [#72540](https://github.com/ClickHouse/ClickHouse/pull/72540) ([Yarik Briukhovetskyi](https://github.com/yariks5s)).
- A new setting, `http_response_headers` which allows you to customize the HTTP response headers. For example, you can tell the browser to render a picture that is stored in the database. This closes [#59620](https://github.com/ClickHouse/ClickHouse/issues/59620). [#72656](https://github.com/ClickHouse/ClickHouse/pull/72656) ([Alexey Milovidov](https://github.com/alexey-milovidov)).
- Add function `fromUnixTimestamp64Second` which converts a Int64 Unix timestamp value to a DateTime64. [#73146](https://github.com/ClickHouse/ClickHouse/pull/73146) ([Robert Schulze](https://github.com/rschu1ze)).
title_wip

- Support string search operator(eg. like) for Enum data type, fix [#72661](https://github.com/ClickHouse/ClickHouse/issues/72661). [#72732](https://github.com/ClickHouse/ClickHouse/pull/72732) ([zhanglistar](https://github.com/zhanglistar)).
- Support JSON type in notEmpty function. [#72741](https://github.com/ClickHouse/ClickHouse/pull/72741) ([Pavel Kruglov](https://github.com/Avogar)).
- Support parsing GCS S3 error `AuthenticationRequired`. [#72753](https://github.com/ClickHouse/ClickHouse/pull/72753) ([Vitaly Baranov](https://github.com/vitlibar)).
- Support Dynamic type in functions ifNull and coalesce. [#72772](https://github.com/ClickHouse/ClickHouse/pull/72772) ([Pavel Kruglov](https://github.com/Avogar)).
- Added `JoinBuildTableRowCount/JoinProbeTableRowCount/JoinResultRowCount` profile events. [#72842](https://github.com/ClickHouse/ClickHouse/pull/72842) ([Vladimir Cherkasov](https://github.com/vdimir)).
- Support Dynamic in functions toFloat64/touInt32/etc. [#72989](https://github.com/ClickHouse/ClickHouse/pull/72989) ([Pavel Kruglov](https://github.com/Avogar)).
title_wip

- [Backward incompatible changes](#backward-incompatible-changes)
  - [Data format and schema changes](#data-format-and-schema-changes)- [Query and function changes](#query-and-function-changes)- [Data type changes](#data-type-changes)- [Storage and index changes](#storage-and-index-changes)- [Settings and configuration changes](#settings-and-configuration-changes)- [Keeper changes](#keeper-changes)- [New features](#new-feature)
    - [Functions](#functions)- [System tables](#system-tables)- [Table engines and storage](#table-engines-and-storage)- [Iceberg and data lakes](#iceberg-and-data-lakes)- [Indexes and statistics](#indexes-and-statistics)- [SQL and query features](#sql-and-query-features)- [Client and CLI features](#client-and-cli-features)- [Server configuration and workload management](#server-configuration-and-workload-management)- [System commands](#system-commands)- [Keeper](#keeper)- [Experimental features](#experimental-features)- [Performance improvements](#performance-improvements)
        - [Query execution and optimization](#query-execution-and-optimization)- [JOIN optimizations](#join-optimizations)- [String and function optimizations](#string-and-function-optimizations)- [MergeTree and storage optimizations](#mergetree-and-storage-optimizations)- [Aggregation and GROUP BY optimizations](#aggregation-and-group-by-optimizations)- [Index and text search optimizations](#index-and-text-search-optimizations)- [Data lake optimizations](#data-lake-optimizations)- [Internal optimizations](#internal-optimizations)- [Improvements](#improvements)
          - [Query optimization and execution](#query-optimization-and-execution)

Was this page helpful?
v25.12 Changelog for Cloud

- [Backward Incompatible Changes](#backward-incompatible-changes)
  - [Data type changes](#data-type-changes)- [Query and function changes](#query-and-function-changes)- [Storage and index changes](#storage-and-index-changes)- [Settings and configuration changes](#settings-and-configuration-changes)- [Client changes](#client-changes)- [Statistics format changes](#statistics-format-changes)- [Other breaking changes](#other-breaking-changes)- [New Features](#new-features)
    - [Functions](#functions)- [System tables](#system-tables)- [Table engines and storage](#table-engines-and-storage)- [Iceberg and data lakes](#iceberg-and-data-lakes)- [SQL and query features](#sql-and-query-features)- [Client and CLI features](#client-and-cli-features)- [Server configuration and monitoring](#server-configuration-and-monitoring)- [Keeper](#keeper)- [Input/Output formats](#input-output-formats)- [Other new features](#other-new-features)- [Experimental Features](#experimental-features)- [Performance Improvements](#performance-improvements)
        - [Query execution and optimization](#query-execution-and-optimization)- [JOIN optimizations](#join-optimizations)- [MergeTree and storage optimizations](#mergetree-and-storage-optimizations)- [Index and text search optimizations](#index-and-text-search-optimizations)- [Aggregation optimizations](#aggregation-optimizations)- [S3 and backup optimizations](#s3-and-backup-optimizations)- [Data lake optimizations](#data-lake-optimizations)- [Improvements](#improvements)
          - [Query optimization and execution](#query-optimization-and-execution)- [Storage and MergeTree improvements](#storage-and-mergetree-improvements)- [System tables and monitoring](#system-tables-and-monitoring)- [Client and UI improvements](#client-and-ui-improvements)- [S3 and cloud storage improvements](#s3-and-cloud-storage-improvements)- [Data lakes improvements](#data-lakes-improvements)- [Kafka improvements](#kafka-improvements)- [Keeper improvements](#keeper-improvements)- [Input/Output format improvements](#input-output-format-improvements)- [Text index improvements](#text-index-improvements)- [Other improvements](#other-improvements)- [Bug Fixes](#bug-fixes)
            - [Query execution fixes](#query-execution-fixes)- [Data type and JSON fixes](#data-type-and-json-fixes)- [MergeTree and storage fixes](#mergetree-and-storage-fixes)- [Parquet and format fixes](#parquet-and-format-fixes)- [Iceberg and data lake fixes](#iceberg-and-data-lake-fixes)- [Function fixes](#function-fixes)- [Memory and crash fixes](#memory-and-crash-fixes)- [Security and access fixes](#security-and-access-fixes)- [Replicated database fixes](#replicated-database-fixes)- [Keeper fixes](#keeper-fixes)- [Other notable fixes](#other-notable-fixes)

Was this page helpful?
v25.4 Changelog for Cloud

## Experimental features[​](#experimental-features "Direct link to Experimental features")

- Allow automatic cleanup merges of entire partitions after a configurable timeout with a new setting `enable_replacing_merge_with_cleanup_for_min_age_to_force_merge`. [#76440](https://github.com/ClickHouse/ClickHouse/pull/76440) ([Christoph Wurm](https://github.com/cwurm)).
- Add support [for Unity Catalog](https://www.databricks.com/product/unity-catalog) for DeltaLake tables on top of AWS S3 and local filesystem. [#76988](https://github.com/ClickHouse/ClickHouse/pull/76988) ([alesapin](https://github.com/alesapin)).
- Introduce experimental integration with AWS Glue service catalog for Iceberg tables. [#77257](https://github.com/ClickHouse/ClickHouse/pull/77257) ([alesapin](https://github.com/alesapin)).

## Performance improvements[​](#performance-improvements "Direct link to Performance improvements")
v25.4 Changelog for Cloud

- Add several convenient ways to resolve root metadata.json file in an iceberg table function and engine. Closes [#78455](https://github.com/ClickHouse/ClickHouse/issues/78455). [#78475](https://github.com/ClickHouse/ClickHouse/pull/78475) ([Daniil Ivanik](https://github.com/divanik)).
- Support partition pruning in delta lake. [#78486](https://github.com/ClickHouse/ClickHouse/pull/78486) ([Kseniia Sumarokova](https://github.com/kssenii)).
- Support password based auth in SSH protocol in ClickHouse. [#78586](https://github.com/ClickHouse/ClickHouse/pull/78586) ([Nikita Mikhaylov](https://github.com/nikitamikhaylov)).
- Add a dynamic warning to the `system.warnings` table for long running mutations. [#78658](https://github.com/ClickHouse/ClickHouse/pull/78658) ([Bharat Nallan](https://github.com/bharatnc)).
- Drop connections if the CPU is massively overloaded. The decision is made based on the ratio of wait time (`OSCPUWaitMicroseconds`) to busy time (`OSCPUVirtualTimeMicroseconds`). The query is dropped with some probability, when this ratio is between `min_os_cpu_wait_time_ratio_to_drop_connection` and `max_os_cpu_wait_time_ratio_to_drop_connection`. [#78778](https://github.com/ClickHouse/ClickHouse/pull/78778) ([Alexey Katsman](https://github.com/alexkats)).
- Allow empty value on hive partitioning. [#78816](https://github.com/ClickHouse/ClickHouse/pull/78816) ([Arthur Passos](https://github.com/arthurpassos)).
- Fix `IN` clause type coercion for `BFloat16` (i.e. `SELECT toBFloat16(1) IN [1, 2, 3];` now returns `1`). Closes [#78754](https://github.com/ClickHouse/ClickHouse/issues/78754). [#78839](https://github.com/ClickHouse/ClickHouse/pull/78839) ([Raufs Dunamalijevs](https://github.com/rienath)).
- Do not check parts on other disks for MergeTree if disk= is set. [#78855](https://github.com/ClickHouse/ClickHouse/pull/78855) ([Azat Khuzhin](https://github.com/azat)).
- Make data types in `used_data_type_families` in `system.query_log` canonical. [#78972](https://github.com/ClickHouse/ClickHouse/pull/78972) ([Kseniia Sumarokova](https://github.com/kssenii)).
v25.4 Changelog for Cloud

- This update corrects a bug where a new named session would inadvertently close at the scheduled time of a previous session if both sessions shared the same name and the new one was created before the old one's timeout expired. [#78698](https://github.com/ClickHouse/ClickHouse/pull/78698) ([Alexey Katsman](https://github.com/alexkats)).
- Don't block table shutdown while running CHECK TABLE. [#78782](https://github.com/ClickHouse/ClickHouse/pull/78782) ([Raúl Marín](https://github.com/Algunenano)).
- Keeper fix: fix ephemeral count in all cases. [#78799](https://github.com/ClickHouse/ClickHouse/pull/78799) ([Antonio Andelic](https://github.com/antonio2368)).
- Fix bad cast in `StorageDistributed` when using table functions other than `view()`. Closes [#78464](https://github.com/ClickHouse/ClickHouse/issues/78464). [#78828](https://github.com/ClickHouse/ClickHouse/pull/78828) ([Konstantin Bogdanov](https://github.com/thevar1able)).
- Fix formatting for `tupleElement(*, 1)`. Closes [#78639](https://github.com/ClickHouse/ClickHouse/issues/78639). [#78832](https://github.com/ClickHouse/ClickHouse/pull/78832) ([Konstantin Bogdanov](https://github.com/thevar1able)).
- Dictionaries of type `ssd_cache` now reject zero or negative `block_size` and `write_buffer_size` parameters (issue [#78314](https://github.com/ClickHouse/ClickHouse/issues/78314)). [#78854](https://github.com/ClickHouse/ClickHouse/pull/78854) ([Elmi Ahmadov](https://github.com/ahmadov)).
- Fix crash in REFRESHABLE MV in case of ALTER after incorrect shutdown. [#78858](https://github.com/ClickHouse/ClickHouse/pull/78858) ([Azat Khuzhin](https://github.com/azat)).
- Fix parsing of bad DateTime values in CSV format. [#78919](https://github.com/ClickHouse/ClickHouse/pull/78919) ([Pavel Kruglov](https://github.com/Avogar)).