Updates in ClickHouse

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/managing-data/updating-data/overview.mdx)

When it comes to handling updates, analytical and transactional databases take different approaches to how they handle updates due to their underlying design philosophies and target use cases.
ClickHouse is a [column-oriented database](/docs/intro#row-oriented-vs-column-oriented-storage) which is optimized for read-heavy analytics and high throughput append-only operations.
In practice, tables are often restructured to convert deletes and updates into append operations that are processed asynchronously and/or at read time, capitalizing on ClickHouse's strengths for high-throughput data ingestion.
ClickHouse also supports robust update and delete operations.

This guide provides an overview of the available update methods in ClickHouse, and helps you choose the right update strategy for your workload.

## Choosing an update strategy[​](#choosing-an-update-strategy "Direct link to Choosing an update strategy")

There are two fundamental approaches to updating data in ClickHouse:

1. Using **specialized table engines** that handle updates through inserts
2. Using **declarative updates** like [`UPDATE ... SET`](/docs/sql-reference/statements/update) or [`ALTER TABLE ... UPDATE`](/docs/sql-reference/statements/alter/update) statements

Within each of the two categories above, there are several ways to update data.
Each has its advantages and performance characteristics and you should select the appropriate method based on your data model and the amount of data you intend to update.

### When to use specialized table engines[​](#when-to-choose-specialised-table-engines "Direct link to When to use specialized table engines")

Specialized table engines are the better choice when you have large volumes of updates, frequent row-level changes, or need to process a continuous stream of update and delete events.

The engines you will commonly encounter are:

|  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Engine Syntax When to use|  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | | [ReplacingMergeTree](/docs/engines/table-engines/mergetree-family/replacingmergetree) `ENGINE = ReplacingMergeTree` Use when updating large amounts of data. This table engine is optimized for data deduplication on merges.|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | [CoalescingMergeTree](/docs/engines/table-engines/mergetree-family/coalescingmergetree) `ENGINE = CoalescingMergeTree` Use when data arrives in fragments and you need column-level coalescing rather than full row replacement.|  |  |  | | --- | --- | --- | | [CollapsingMergeTree](/docs/engines/table-engines/mergetree-family/collapsingmergetree) `ENGINE = CollapsingMergeTree(Sign)` Use when updating individual rows frequently, or for scenarios where you need to maintain the latest state of objects that change over time. For example, tracking user activity or article stats. | | | | | | | | | | | |

As MergeTree-family table engines merge data parts in the background, they offer *eventual consistency*, and the [`FINAL`](/docs/sql-reference/statements/select/from#final-modifier) keyword needs to be used to ensure proper deduplication in the interim when querying the table.
There are other [engine types](/docs/engines/table-engines/mergetree-family) too, but these are the most commonly used ones.

### When to use declarative updates[​](#when-to-use-declaritive-updates "Direct link to When to use declarative updates")

Declarative UPDATE statements can be more straightforward for simple update operations without the complexity of managing deduplication logic, but they are generally better suited for updating smaller numbers of rows, less frequently, than with specialized engines.

|  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Method Syntax When to use|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | [Lightweight updates](/docs/sql-reference/statements/update) `UPDATE [table] SET ... WHERE` Use this in most scenarios, particularly when running frequent, small UPDATEs (up to ~10% of table) as part of your application or workflows. For example, a user wants to delete their event history, and events are spread across a multi-tenant table with many users. This approach creates patch parts for immediate visibility without rewriting entire columns. It adds overhead to `SELECT` queries but has predictable latency.| [Update mutation](/docs/sql-reference/statements/alter/update) `ALTER TABLE [table] UPDATE` Use this when performing larger scale data management, particularly when an update aligns with your table partitioning. For example, you need to update a column of all rows within a month, in a table partitioned by month. | | | | | | | | |

## Updates using specialized table engines[​](#insert-based-updates "Direct link to Updates using specialized table engines")

### ReplacingMergeTree[​](#replacingmergetree "Direct link to ReplacingMergeTree")

The `ReplacingMergeTree` deduplicates rows with the same sorting key during background merges, keeping only the latest version.

```
CREATE TABLE posts
(
    Id UInt32,
    Title String,
    ViewCount UInt32,
    Version UInt32
)
ENGINE = ReplacingMergeTree(Version)
ORDER BY Id
```

This engine is ideal for high-frequency updates to individual rows where updates are identified by a stable key.
Benchmarks show it can be up to 4,700x faster than mutations for single-row updates.

To update a row, simply insert a new version with the same sorting key values and a higher version number. Older versions are removed during background merges. Since deduplication is eventual (it only happens during merges), you should use the `FINAL` modifier or equivalent query logic to get correct, deduplicated results. The `FINAL` modifier adds query overhead ranging from 21-550% depending on the data.

`ReplacingMergeTree` can't update sorting key values. It also supports a `Deleted` column for logical deletes.

Read more: [ReplacingMergeTree guide](/docs/guides/replacing-merge-tree) | [ReplacingMergeTree reference](/docs/engines/table-engines/mergetree-family/replacingmergetree)

### CoalescingMergeTree[​](#coalescingmergetree "Direct link to CoalescingMergeTree")

CoalescingMergeTree consolidates sparse records by keeping the latest non-null value for each column during merges. This enables column-level upserts rather than full row replacements.

```
CREATE TABLE electric_vehicle_state
(
    vin String, -- vehicle identification number
    last_update DateTime64 Materialized now64(), -- optional (used with argMax)
    battery_level Nullable(UInt8), -- in %
    lat Nullable(Float64), -- latitude (°)
    lon Nullable(Float64), -- longitude (°)
    firmware_version Nullable(String),
    cabin_temperature Nullable(Float32), -- in °C
    speed_kmh Nullable(Float32) -- from sensor
)
ENGINE = CoalescingMergeTree
ORDER BY vin;
```

This engine is designed for scenarios where data arrives in fragments from multiple sources, or where different columns are populated at different times. Common use cases include IoT telemetry from fragmented subsystems, user profile enrichment, and ETL pipelines with delayed dimensions.

When rows with the same sorting key are merged, CoalescingMergeTree keeps the latest non-null value for each column rather than replacing the entire row. Non-key columns should be `Nullable` for this to work as intended. As with ReplacingMergeTree, use `FINAL` for correct, coalesced results.

This engine is available from ClickHouse 25.6.

Read more: [CoalescingMergeTree](/docs/engines/table-engines/mergetree-family/coalescingmergetree)

### CollapsingMergeTree[​](#collapsingmergetree "Direct link to CollapsingMergeTree")