Updates in ClickHouse

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:

|  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Setting Description Default|  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `mutations_execute_nondeterministic_on_initiator` If true, non-deterministic functions are executed on the initiator replica and are replaced as literals in `UPDATE` and `DELETE` queries. `false`| `mutations_execute_subqueries_on_initiator` If true, scalar subqueries are executed on the initiator replica and are replaced as literals in `UPDATE` and `DELETE` queries. `false`| `mutations_max_literal_size_to_replace` The maximum size of serialized literals in bytes to replace in `UPDATE` and `DELETE` queries. `16384` (16 KiB) | | | | | | | | | | | |

### Lightweight updates[​](#lightweight-updates "Direct link to Lightweight updates")

Lightweight updates use "patch parts"—special data parts containing only the updated columns and rows—rather than rewriting entire columns like traditional mutations.

```
UPDATE posts SET AnswerCount = AnswerCount + 1 WHERE Id = 404346
```

This approach uses the standard `UPDATE` syntax and creates patch parts immediately without waiting for merges. Updated values are immediately visible in `SELECT` queries through patch application, but are physically materialized only during subsequent merges. This makes lightweight updates ideal for updating a small percentage of rows (up to ~10% of the table) with predictable latency. Benchmarks show they can be up to 23x faster than mutations.

The trade-off is that `SELECT` queries incur overhead when applying patches, and patch parts count toward part limits. Beyond the ~10% threshold, patch-on-read overhead grows proportionally, making synchronous mutations more efficient for larger updates.

Read more: [Lightweight UPDATE](/docs/sql-reference/statements/update)

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

On-the-fly mutations provide a mechanism to update rows such that subsequent `SELECT` queries automatically return the changed values without waiting for background processing. This effectively addresses the atomicity limitation of normal mutations.

```
SET apply_mutations_on_fly = 1;

SELECT ViewCount FROM posts WHERE Id = 404346

┌─ViewCount─┐
│     26762 │
└───────────┘

-- Increment the count
ALTER TABLE posts UPDATE ViewCount = ViewCount + 1 WHERE Id = 404346

-- The updated value is immediately visible
SELECT ViewCount FROM posts WHERE Id = 404346

┌─ViewCount─┐
│     26763 │
└───────────┘
```

Both the mutation and subsequent `SELECT` queries need the `apply_mutations_on_fly = 1` setting enabled. The mutation conditions are stored in ClickHouse Keeper, which keeps everything in memory, and applied on-the-fly during queries.

Note that a mutation is still used to update the data—it is just not materialized immediately. The mutation will still be applied in the background as an asynchronous process and incurs the same heavy overhead as a regular mutation. The expressions that can be used with this operation are also limited (see [details](#support-for-subqueries-and-non-deterministic-functions)).

Note

On-the-fly mutations should only be used for a small number of operations—perhaps a few dozen at most. Keeper stores conditions in memory, so excessive use impacts cluster stability. Heavy Keeper load can cause session timeouts that affect unrelated tables.

Read more: [On-the-fly mutations](#on-the-fly-mutations)

## Comparison summary[​](#comparison-summary "Direct link to Comparison summary")

The following table summarizes query performance overhead based on [benchmarks](https://clickhouse.com/blog/updates-in-clickhouse-3-benchmarks). Mutations serve as the baseline since queries run at full speed once the mutation completes and data is physically rewritten.

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Method Query slowdown Memory overhead Notes|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | [Mutations](/docs/sql-reference/statements/alter/update) Baseline Baseline Full speed after completion; data physically rewritten|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | [On-the-fly mutations](#on-the-fly-mutations) Variable Variable Instant visibility; performance degrades if many updates accumulate|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | [Lightweight updates](/docs/sql-reference/statements/update#lightweight-update-requirements) 7–18% (avg ~12%) +20–210% Most efficient for queries; best for updating ≤10% of table|  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | [ReplacingMergeTree](/docs/guides/replacing-merge-tree) + `FINAL` 21–550% (avg ~280%) 20–200× baseline Must read all row versions; heaviest query overhead|  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | | [CoalescingMergeTree](/docs/engines/table-engines/mergetree-family/coalescingmergetree) + `FINAL` Similar to ReplacingMergeTree Similar to ReplacingMergeTree Column-level coalescing adds comparable overhead|  |  |  |  | | --- | --- | --- | --- | | [CollapsingMergeTree](/docs/engines/table-engines/mergetree-family/collapsingmergetree) Aggregation dependent Aggregation dependent Overhead depends on query complexity | | | | | | | | | | | | | | | | | | | | | | | | | | | |

## More resources[​](#more-resources "Direct link to More resources")

If you're interested in a deep-dive of how updates in ClickHouse have evolved over time, along with benchmarking analysis, see:

- [Updates in ClickHouse Part 1: Purpose-Built Engines](https://clickhouse.com/blog/updates-in-clickhouse-1-purpose-built-engines)
- [Updates in ClickHouse Part 2: SQL-Style Updates](https://clickhouse.com/blog/updates-in-clickhouse-2-sql-style-updates)
- [Updates in ClickHouse Part 3: Benchmarks](https://clickhouse.com/blog/updates-in-clickhouse-3-benchmarks)

- [Choosing an update strategy](#choosing-an-update-strategy)
  - [When to use specialized table engines](#when-to-choose-specialised-table-engines)- [When to use declarative updates](#when-to-use-declaritive-updates)- [Updates using specialized table engines](#insert-based-updates)
    - [ReplacingMergeTree](#replacingmergetree)- [CoalescingMergeTree](#coalescingmergetree)- [CollapsingMergeTree](#collapsingmergetree)- [Declarative updates](#sql-style-updates)
      - [Mutations](#mutations)- [Lightweight updates](#lightweight-updates)- [On-the-fly mutations](#on-the-fly-mutations)- [Comparison summary](#comparison-summary)- [More resources](#more-resources)

Was this page helpful?