DELETE statement

[BohuTANG]

Summary

Each segment file(parquet) in the underlying storage is immutable; it is not suited for mutable operations (e.g., Delete, Update).
There are many ways to improve that, such as adding a hidden column for each row: 1 mark for deleted, 0 is normal.
The parquet file also needs to read the old and write the new mark flag to the new, the parquet file is compact format(vs. wide format), so it is not well suited for any modification-related operations.
For Databend deletion, the first version must work, then the performance. The initial plan is:

1. Parse the delete statement
2. Check which segments are hit by the statement
3. Create a row-level bitmap in BlockInfo or a single delete file?
4. Commit the new snapshot
5. Done

There is already a draft PR in #4228, but it still needs work.

[BohuTANG]

Welcome to comments, @dantengsky

[sundy-li]

Generally, it's a common way to store an optional extra bitmap inside BlockInfo and apply the filter selection during query.
Deletion is to update this bitmap. Bitmap can be merged during block compaction.

[BohuTANG]

Generally, it's a common way to store an optional extra bitmap inside BlockInfo and apply the filter selection during query. Deletion is to update this bitmap. Bitmap can be merged during block compaction.

This is a good way(preferred it), if we add an extra bitmap, this bitmap should be binding with one snapshot.
@dantengsky may have more comments on it

[cadl]

Perhaps we could introduce the “delete_file“ like iceberg.

By the way，why not just borrow iceberg format, but create fuse? The iceberg looks nice.

https://iceberg.apache.org/spec/#delete-formats

[BohuTANG]

@cadl
Good question, thank you.
The main reason is that there is no Rust Iceberg implementation yet. Fuse engine is a simplified version of Iceberg.

For the delete, as @sundy-li suggested, I think it's the same(idea) as Iceberg did.

[dantengsky]

Let's do copy-on-write first. (replace blocks whose rows have been deleted with new blocks). This is the fundamental model we should implement (GDPR)

Merge-on-read is interesting, let's implement it later if a high volume of mutation is required which the copy-on-write can not meet.