Lightweight DELETE in ClickHouse®: How It Works, When to Use It, and Key Limitations

Deleting data in ClickHouse® requires a different approach compared to traditional row-based databases. Since the MergeTree engine family stores data in immutable parts, removing rows efficiently involves mutation logic rather than direct row removal.

To address this, ClickHouse® provides Lightweight DELETE, a lower-cost alternative to ALTER TABLE ... DELETE.

Lightweight DELETE is a row-level deletion mechanism available only for the MergeTree table engine family in ClickHouse®.

This article explains why it exists, how it works, when to use it, its limitations, and how it differs from other deletion methods.

Why Lightweight DELETE Exists

The standard deletion mechanism:

ALTER TABLE table DELETE WHERE condition;

physically rewrites all affected data parts. For large datasets, this can be resource-intensive because:

Entire parts must be recreated
Column files are rewritten
CPU and disk I/O usage can be significant

Lightweight DELETE was introduced to reduce this overhead. Instead of rewriting all data immediately, it marks rows as deleted and defers physical cleanup to background merges.

How Lightweight DELETE Works

Syntax:

DELETE FROM [db.]table 
[ON CLUSTER cluster] 
[IN PARTITION partition_expr] 
WHERE expr;

Example:

DELETE FROM names WHERE Title LIKE '%hub%';

Internal Behavior

When executed, the statement is internally translated into:

ALTER TABLE table UPDATE _row_exists = 0 WHERE condition;

A hidden system column called _row_exists acts as a mask:

1 → row is visible
0 → row is deleted

Key points:

The _row_exists column exists only in parts where rows were deleted.
Rows are filtered automatically in SELECT queries.
Physical removal occurs only during subsequent merges.

If many rows are deleted within a part, ClickHouse® may skip entire granules during query execution.

Synchronous vs Asynchronous Execution

By default, DELETE waits until rows are marked as deleted before returning.

If the setting lightweight_deletes_sync is disabled:

The DELETE query returns immediately
Deleted rows may still be visible until background mutation completes

This affects when the deletion becomes visible to queries.

When to Use Lightweight DELETE

Lightweight DELETE is suitable when:

Deleting a relatively small portion of rows
Deletes are occasional
Immediate physical disk cleanup is not required
Working with MergeTree tables
Lower I/O cost is preferred over immediate data rewrite

When Not to Use It

Avoid Lightweight DELETE when:

Deleting large portions of a table
Guaranteed physical deletion timing is required
The mutation queue already contains many operations
The table has many compact parts (which require full rewrites)
Deleting all rows (use TRUNCATE TABLE instead)

If deletes frequently target logical data groups, using partitioning and:

ALTER TABLE table DROP PARTITION partition_expr;

is more efficient.

Limitations

Available only for the MergeTree engine family
Does not work with projections by default
(controlled via lightweight_mutation_projection_mode)
Physical data removal is deferred to merges
Mutations execute sequentially
Large deletes can negatively impact SELECT performance

Performance Considerations

Performance may be affected by:

Complex WHERE conditions
Large numbers of data parts
Many compact parts
Large delete volumes
A busy mutation queue

To guarantee predictable physical cleanup, consider:

Setting min_age_to_force_merge_seconds
Using ALTER TABLE ... DELETE instead

Required Permissions

DELETE requires the ALTER DELETE privilege:

GRANT ALTER DELETE ON db.table TO username;

Conclusion

Lightweight DELETE in ClickHouse® provides a lower-cost way to remove rows in MergeTree tables by marking them as deleted and deferring physical cleanup to background merges.

It reduces immediate resource consumption compared to ALTER TABLE ... DELETE, but it is not suitable for large-scale or time-sensitive deletions.

Choosing the appropriate deletion method depends on data volume, performance requirements, and table design.

References

https://clickhouse.com/docs/guides/developer/lightweight-delete

Post Views: 92

Quantrail Data