Updates in ClickHouse

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")

Stemming from the idea that updates are expensive but inserts can be leveraged to perform updates, `CollapsingMergeTree` uses a `Sign` column to tell ClickHouse how to handle rows during merges. If `-1` is inserted for the sign column, the row will be collapsed (deleted) when paired with a matching `+1` row. Rows to update are identified based on the sorting key used in the `ORDER BY` clause when creating the table.

```
CREATE TABLE user_activity
(
    UserID UInt64,
    PageViews UInt8,
    Duration UInt8,
    Sign Int8
)
ENGINE = CollapsingMergeTree(Sign)
ORDER BY UserID

-- Initial state
INSERT INTO user_activity VALUES (4324182021466249494, 5, 146, 1)

-- Cancel old row and insert new state
INSERT INTO user_activity VALUES (4324182021466249494, 5, 146, -1)
INSERT INTO user_activity VALUES (4324182021466249494, 6, 185, 1)

-- Query with proper aggregation
SELECT
    UserID,
    sum(PageViews * Sign) AS PageViews,
    sum(Duration * Sign) AS Duration
FROM user_activity
GROUP BY UserID
HAVING sum(Sign) > 0

┌──────────────UserID─┬─PageViews─┬─Duration─┐
│ 4324182021466249494 │         6 │      185 │
└─────────────────────┴───────────┴──────────┘
```

Unlike `ReplacingMergeTree`, `CollapsingMergeTree` allows you to modify sorting key values. It's well-suited for reversible operations with cancellation semantics, such as financial transactions or game state tracking.

Note

The approach above for updating requires your application to maintain state client-side in order to insert the cancellation row. While this is most efficient from ClickHouse's perspective, it can be complex to work with at scale. Queries also need aggregation with sign multiplication to produce correct results.

Read more: [CollapsingMergeTree](/docs/engines/table-engines/mergetree-family/collapsingmergetree)

## Declarative updates[​](#sql-style-updates "Direct link to Declarative updates")

These methods work with tables using [MergeTree family](/docs/engines/table-engines/mergetree-family) engines.

|  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Method Syntax Best for Trade-offs|  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | | [Mutations](#mutations) `ALTER TABLE ... UPDATE` Infrequent bulk updates, well suited for when an update aligns with your table partitioning. Heavy I/O; rewrites columns|  |  |  |  | | --- | --- | --- | --- | | [Lightweight updates](#lightweight-updates) `UPDATE ... SET ... WHERE` Small updates (~0.1-10% of rows); frequent updates needing performance Adds SELECT overhead; patch parts count toward limits | | | | | | | | | | | |

### Mutations[​](#mutations "Direct link to Mutations")

Mutations (`ALTER TABLE ... UPDATE`) rewrite all parts containing rows that match the `WHERE` expression.

```
ALTER TABLE posts UPDATE AnswerCount = AnswerCount + 1 WHERE AnswerCount = 0
```

Mutations are I/O-heavy, rewriting all parts that match the `WHERE` expression.
There is no atomicity to this process.
Parts get substituted for mutated parts as soon as they are ready, and a `SELECT` query that starts executing during a mutation will see data from parts that have already been mutated along with data from parts that have not been mutated yet.
You can track the state of progress via the [`system.mutations`](/docs/operations/system-tables/mutations) table.

Note

Mutations are I/O intensive and should be used sparingly as they can impact cluster `SELECT` performance. If mutations queue faster than they process, query performance will degrade. Monitor the queue via `system.mutations`.

Read more: [ALTER TABLE UPDATE](/docs/sql-reference/statements/alter/update)

#### On-the-fly mutations[​](#on-the-fly-mutations "Direct link to On-the-fly mutations")

With mutations via `ALTER TABLE ... UPDATE` you may have to wait for your mutations to be applied via a background process to see the changed values reflected in your queries.
ClickHouse offers a way to change this behavior through "on-the-fly mutations".
When on-the-fly mutations are enabled, updated rows are marked as updated immediately and subsequent `SELECT` queries will automatically return with the changed values.

On-the-fly mutations can be enabled for `MergeTree`-family tables by enabling the query-level setting `apply_mutations_on_fly`.

```
SET apply_mutations_on_fly = 1;
```

Example

Let's create a table and run some mutations:

```
CREATE TABLE test_on_fly_mutations (id UInt64, v String)
ENGINE = MergeTree ORDER BY id;

-- Disable background materialization of mutations to showcase
-- default behavior when on-the-fly mutations are not enabled
SYSTEM STOP MERGES test_on_fly_mutations;
SET mutations_sync = 0;

-- Insert some rows in our new table
INSERT INTO test_on_fly_mutations VALUES (1, 'a'), (2, 'b'), (3, 'c');

-- Update the values of the rows
ALTER TABLE test_on_fly_mutations UPDATE v = 'd' WHERE id = 1;
ALTER TABLE test_on_fly_mutations DELETE WHERE v = 'd';
ALTER TABLE test_on_fly_mutations UPDATE v = 'e' WHERE id = 2;
ALTER TABLE test_on_fly_mutations DELETE WHERE v = 'e';
```

Let's check the result of the updates via a `SELECT` query:

```
-- Explicitly disable on-the-fly-mutations
SET apply_mutations_on_fly = 0;

SELECT id, v FROM test_on_fly_mutations ORDER BY id;
```

Note that the values of the rows haven't yet been updated when we query the new table:

```
┌─id─┬─v─┐
│  1 │ a │
│  2 │ b │
│  3 │ c │
└────┴───┘
```

Let's now see what happens when we enable on-the-fly mutations:

```
-- Enable on-the-fly mutations
SET apply_mutations_on_fly = 1;

SELECT id, v FROM test_on_fly_mutations ORDER BY id;
```

The `SELECT` query now returns the correct result immediately, without having to wait for the mutations to be applied:

```
┌─id─┬─v─┐
│  3 │ c │
└────┴───┘
```

##### Performance impact[​](#performance-impact "Direct link to Performance impact")

When on-the-fly mutations are enabled, mutations aren't materialized immediately but will only be applied during `SELECT` queries. However, please note that mutations are still being materialized asynchronously in the background, which is a heavy process.

If the number of submitted mutations constantly exceeds the number of mutations that are processed in the background over some time interval, the queue of unmaterialized mutations that have to be applied will continue to grow. This will result in the eventual degradation of `SELECT` query performance.

We suggest enabling the setting `apply_mutations_on_fly` together with other `MergeTree`-level settings such as `number_of_mutations_to_throw` and `number_of_mutations_to_delay` to restrict the infinite growth of unmaterialized mutations.

##### Support for subqueries and non-deterministic functions[​](#support-for-subqueries-and-non-deterministic-functions "Direct link to Support for subqueries and non-deterministic functions")

On-the-fly mutations have limited support with subqueries and non-deterministic functions. Only scalar subqueries with a result that have a reasonable size (controlled by the setting `mutations_max_literal_size_to_replace`) are supported. Only constant non-deterministic functions are supported (e.g. the function `now()`).

These behaviors are controlled by the following settings: