Skip to main content
Lightweight updates are currently beta. If you run into problems, kindly open an issue in the ClickHouse repository.
The lightweight UPDATE statement updates rows in a table [db.]table that match the expression filter_expr. It is called “lightweight update” to contrast it to the ALTER TABLE ... UPDATE query, which is a heavyweight process that rewrites entire columns in data parts. It is only available for the MergeTree table engine family.
The filter_expr must be of type UInt8. This query updates values of the specified columns to the values of the corresponding expressions in rows for which the filter_expr takes a non-zero value. Values are cast to the column type using the CAST operator. Updating columns used in the calculation of the primary or partition keys is not supported.

​
Examples

​
Lightweight updates do not update data immediately

Lightweight UPDATE is implemented using patch parts - a special kind of data part that contains only the updated columns and rows. A lightweight UPDATE creates patch parts but does not immediately modify the original data physically in storage. The process of updating is similar to a INSERT ... SELECT ... query but the UPDATE query waits until the patch part creation is completed before returning. The updated values are:
  • Immediately visible in SELECT queries through patches application
  • Physically materialized only during subsequent merges and mutations
  • Automatically cleaned up once all active parts have the patches materialized

​
Lightweight updates requirements

Lightweight updates are supported for MergeTree, ReplacingMergeTree, CollapsingMergeTree, VersionedCollapsingMergeTree engines and their Replicated and Shared versions. To use lightweight updates, materialization of _block_number and _block_offset columns must be enabled using table settings enable_block_number_column and enable_block_offset_column.

​
Lightweight deletes

A lightweight DELETE query can be run as a lightweight UPDATE instead of a ALTER UPDATE mutation. The implementation of lightweight DELETE is controlled by setting lightweight_delete_mode.

​
Performance considerations

Advantages of lightweight updates:
  • The latency of the update is comparable to the latency of the INSERT ... SELECT ... query
  • Only updated columns and values are written, not entire columns in data parts
  • No need to wait for currently running merges/mutations to complete, therefore the latency of an update is predictable
  • Parallel execution of lightweight updates is possible
Potential performance impacts:
  • Adds an overhead to SELECT queries that need to apply patches
  • Skipping indexes will not be used for columns in data parts that have patches to be applied. Projections will not be used if there are patch parts for table, including for data parts that don’t have patches to be applied.
  • Small updates which are too frequent may lead to a “too many parts” error. It is recommended to batch several updates into a single query, for example by putting ids for updates in a single IN clause in the WHERE clause
  • Lightweight updates are designed to update small amounts of rows (up to about 10% of the table). If you need to update a larger amount, it is recommended to use the ALTER TABLE ... UPDATE mutation

​
Concurrent operations

Lightweight updates don’t wait for currently running merges/mutations to complete unlike heavy mutations. The consistency of concurrent lightweight updates is controlled by settings update_sequential_consistency and update_parallel_mode.

​
Update permissions

UPDATE requires the ALTER UPDATE privilege. To enable UPDATE statements on a specific table for a given user, run:

​
Details of the implementation

Patch parts are the same as the regular parts, but contain only the updated columns, the columns of the sorting key of the table and the following system columns:
  • _part - the name of the original part
  • _block_number - the block number of the row in the original part
  • _block_offset - the block offset of the row in the original part
  • _data_version - the data version of the updated data (block number allocated for the UPDATE query)
The system columns help to find rows in the original part which should be updated. They are related to the virtual columns in the original part, which are added for reading if patch parts should be applied. Patch parts are sorted by the sorting key of the table with the _block_number and _block_offset columns appended to it. The storage overhead per updated row depends on the size of the values of the sorting key columns. Patch parts belong to different partitions than the original part. The partition id of the patch part is patch-<hash of the patch part structure>-<original_partition_id>. Therefore patch parts with different columns are stored in different partitions. For example three updates SET x = 1 WHERE <cond>, SET y = 1 WHERE <cond> and SET x = 1, y = 1 WHERE <cond> will create three patch parts in three different partitions. Patch parts can be merged among themselves to reduce the amount of applied patches on SELECT queries and reduce the overhead. Merging of patch parts uses the replacing merge algorithm with _data_version as a version column. Therefore patch parts always store the latest version for each updated row in the part. A patch part is applied by merging it with the data part by the sorting key of the table: a row is updated if the values of the sorting key columns and of the _block_number and _block_offset columns are equal in the data part and in the patch part. Applying patch parts requires reading the sorting key columns of the data part even if the query does not select them. The memory usage of applying a patch part is bounded by the largest range of rows with equal sorting key values.

​
Patch parts format in mixed-version clusters

The format of newly written patch parts is controlled by the table-level setting patch_parts_version. ClickHouse versions before 26.8 support only the legacy v1 format and cannot read patch parts in the current v2 format described above. During a rolling upgrade from such a version, keep writing the v1 format until all replicas are upgraded: set patch_parts_version = 'v1' for tables with lightweight updates, or set the compatibility setting to the version you are upgrading from. After the upgrade is complete, remove the override to start writing patch parts in the v2 format. Patch parts of both formats remain readable and applicable regardless of the setting value.

​
Related Content

Last modified on August 14, 2026