DELETE: Lightweight Deleting Table Data

Basic Syntax

DELETE FROM [db.]table [ON CLUSTER cluster] WHERE expr

Example

• Create a table.

  CREATE TABLE default.test_ligtwight_delete
  (
  `id`    Int32,
  `pdate` Date,
  `name`  String,
  `class` Int32
  )
  ENGINE = ReplicatedMergeTree('/clickhouse/tables/distributed_tests/{shard}/test_ligtwight_delete', '{replica}')
  PARTITION BY toYYYYMM(pdate)
  PRIMARY KEY id
  ORDER BY id
  SETTINGS index_granularity = 8192, vertical_merge_algorithm_min_rows_to_activate = 1, vertical_merge_algorithm_min_columns_to_activate = 1, min_rows_for_wide_part = 1, min_bytes_for_wide_part = 1;

• Insert data.

  insert into default.test_ligtwight_delete select rand(), rand() % 365, rand(), rand() from numbers(10);

• Delete data.

  delete from default.test_ligtwight_delete where id > 0;

Precautions

• Deleted rows are immediately marked as deleted and automatically filtered out from all subsequent queries. Data cleanup occurs asynchronously in the background. This function is only available for the MergeTree table engine series.
• Currently, only lightweight deletion is supported for local tables and replication tables, but not for distributed tables.
• The lightweight deletion performance depends on the number of merge and mutation (alter table update/delete) tasks. Mutation tasks in a queue have the lowest priority (mutation tasks in the same table are executed serially). The number of concurrent delete tasks is directly affected by the execution of the merge tasks.
• The number of parts in the table also determines the lightweight deletion performance. The more parts, the slower the deletion.
• Data parts in Wide format can be deleted quickly, and those in compact files can be deleted slowly because all column data is stored in one file.