Skip to main content
The lightweight DELETE statement removes rows from the table [db.]table that match the expression expr. It is only available for the *MergeTree table engine family.
It is called “lightweight DELETE” to contrast it to the ALTER TABLE … DELETE command, which is a heavyweight process.

​
Examples

​
Lightweight DELETE does not delete data immediately

Lightweight DELETE marks rows as deleted without immediately removing them from storage. By default, it uses a mutation. It can also use patch parts, depending on the lightweight_delete_mode setting. With the default mutation-based mode, DELETE statements wait until marking the rows as deleted is completed before returning. This can take a long time if the amount of data is large. Alternatively, you can run it asynchronously in the background using the setting lightweight_deletes_sync. If disabled, the DELETE statement is going to return immediately, but the data can still be visible to queries until the background mutation is finished. In both modes, deleted rows remain in storage until cleanup. Background merges normally remove them from the affected data parts. To explicitly apply the deletion mask, use ALTER TABLE ... APPLY DELETED MASK, which performs a heavyweight mutation. If you need to guarantee that your data is deleted from storage in a predictable time, consider using the table setting min_age_to_force_merge_seconds. Or you can use the ALTER TABLE … DELETE command. Note that deleting data using ALTER TABLE ... DELETE may consume significant resources as it recreates all affected parts.

​
Choose the delete mode

The lightweight_delete_mode setting selects mutation-based deletes (the default) or lightweight updates that write patch parts containing _row_exists = 0 for deleted rows. Queries apply these patches before physical cleanup. See the setting reference for the available modes and the lightweight update requirements for prerequisites. With patch parts, lightweight_deletes_sync does not wait for other replicas; on ReplicatedMergeTree, other replicas may still return deleted rows until they receive the patch.

​
Deleting large amounts of data

Large deletes can negatively affect ClickHouse performance. If you are attempting to delete all rows from a table, consider using the TRUNCATE TABLE command. If you anticipate frequent deletes, consider using a custom partitioning key. You can then use the ALTER TABLE ... DROP PARTITION command to quickly drop all rows associated with that partition.

​
Limitations of lightweight DELETE

​
Lightweight DELETEs with projections

By default, DELETE does not work for tables with projections. This is because rows in a projection may be affected by a DELETE operation. But there is a MergeTree setting lightweight_mutation_projection_mode to change the behavior.

​
Performance considerations when using lightweight DELETE

Deleting large volumes of data with the lightweight DELETE statement can negatively affect SELECT query performance. The following can also negatively impact lightweight DELETE performance:
  • A heavy WHERE condition in a DELETE query.
  • For mutation-based deletes, a large mutation queue: mutations on a table are executed sequentially.
  • The affected table has a very large number of data parts.
  • When using mutation-based deletes, having a lot of data in compact parts. In a compact part, all columns are stored in one file and must be rewritten together.

​
Delete permissions

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

​
How lightweight DELETEs work internally in ClickHouse

  1. A “mask” is applied to affected rows When a DELETE FROM table ... query is executed, ClickHouse saves a mask where each row is marked as either “existing” or as “deleted”. Those “deleted” rows are omitted for subsequent queries. However, rows are physically removed later, normally during background merges. Writing this mask is much more lightweight than what is done by an ALTER TABLE ... DELETE query. The mask is implemented as a hidden _row_exists system column that stores True for all visible rows and False for deleted ones. This column is only present in a part if some rows in the part were deleted. This column does not exist when a part has all values equal to True.
  2. SELECT queries are transformed to include the mask When a masked column is used in a query, the SELECT ... FROM table WHERE condition query internally is extended by the predicate on _row_exists and is transformed to:
    At execution time, the column _row_exists is read to determine which rows should not be returned. If there are many deleted rows, ClickHouse can determine which granules can be fully skipped when reading the rest of the columns.
  3. DELETE queries update the mask using the selected mode With patch parts, DELETE FROM table WHERE condition is translated into UPDATE table SET _row_exists = 0 WHERE condition. The resulting patch parts store the mask changes for the deleted rows and are applied when reading and merging data. With the default alter_update mode, DELETE FROM table WHERE condition is translated into an ALTER TABLE table UPDATE _row_exists = 0 WHERE condition mutation. Internally, this mutation is executed in two steps:
    1. A SELECT count() FROM table WHERE condition command is executed for each individual part to determine if the part is affected.
    2. Based on the commands above, affected parts are then mutated, and hardlinks are created for unaffected parts. In the case of wide parts, the _row_exists column for each row is updated, and all other columns’ files are hardlinked. For compact parts, all columns are re-written because they are all stored together in one file.
    From the steps above, we can see that lightweight DELETE using the masking technique improves performance over traditional ALTER TABLE ... DELETE because it does not re-write all the columns’ files for affected parts.

​
Related content

Last modified on July 3, 2026