Thanks!

I was also playing yesterday with CollapsingMergeTree.

    CREATE TABLE cmt
    (
      whatever Date DEFAULT '2000-01-01',
      key String,
      value String
      sign Int8
    ) ENGINE = CollapsingMergeTree(whatever, (key, value), 8192, sign)

Now you can 'delete' a row by sending the same row again but with a sign of -1:

    insert into cmt (key, value, sign) values ('k1', 'v1', 1)
    insert into cmt (key, value, sign) values ('k1', 'v1', -1)
    insert into cmt (key, value, sign) values ('k1', 'v1 update', 1)
    insert into cmt (key, value, sign) values ('k2', 'just delete this one', 1)
    insert into cmt (key, value, sign) values ('k2', 'just delete this one', -1)

You have to either add FINAL onto the query or optimise the table as far as I can tell for this to work, or hope it's done it in the background.

I thought you had to use the sign in the query otherwise it wouldn't work, but creating this example this morning works fine. If you're not getting the response you expect after optimising, try adding the sign column to the query.

    :) select key, value, sign from cmt

    SELECT
        key,
        value,
        sign
    FROM cmt
    
    ┌─key─┬─value─────┬─sign─┐
    │ k1  │ v1 update │    1 │
    └─────┴───────────┴──────┘
    
    1 rows in set. Elapsed: 0.002 sec.
    
    :)

[disclaimer - I know very little about how to make high performance things in clickhouse, tbh the Log format has been easily fast enough for my data so far]