Support `delete` sql

[ShiKaiWi]

Describe This Problem

Currently, the delete sql is not supported yet. However, this is a very important feature in some cases.

Proposal

Feature Details

CeresDB's model is LSM, that is to say, we can't do deletion in place and any way to support deletion must be processed when querying or compaction.

There are two cases where delete may happen:

• Delete the rows specified by primary keys;
• Delete the rows specified by a predicate;

By predicate for deletion

A sound solution is to save the delete predicate somewhere, and filter the results with this predicate when querying or compaction. However, it will cost too much in this way if user deletes massive rows by massive primary keys.

By marking rows deleted

An alternative solution is to persist all the deleted rows in the sst just like the normal rows but with an extra marker for deletion. When querying or compaction, dedup procedure will filter out the deleted rows. This way is good at handling the first case, but for the second case, the rows to delete are not provided directly by the user, that is to say, we have to collect all the primary keys according to the delete predicate and then mark them all deleted.

Conclusion

I'm in favor of the first solution because I guess in the first cases, the number of the provided primary keys won't be too large and it is easy to convert into a simple predicate.

Additional Context

No response

[fredrikIOT]

Any plan when this will be implemented?

[jackbit]

+1 We need something like this: DELETE FROM tableName WHERE price != 100