We are using Clickhouse combined with GDPR's Data Deletion Requests. We store the user-ids in a separate system, and run the ALTER/DELETE statements once per week. Works pretty smooth, though I would prefer some more automation within Clickhouse for them.
Data for in-active users gets deleted because our clickhouse retention policy is lower than the in-active-user timeout
ClickHouse does allow delete and update operations. They are just asynchronous functions.
I use them every now and then, but I prefer working with partition strategies when I have to these programmatically.
You are correct, the proper way to do deletions in ClickHouse is to use partitions, and drop partitions. That is probably good enough for most analytical use cases, but YMMV.
This question is becoming critical right now, as nonrecoverable deletes are required within 30 days for both GDPR and CCPA.
Most products do the asynchronous rewrite, especially if they're based on immutable storage. That's fine, but it should be tested to verify that it's not triggering on every delete, for example, and that it's resource-efficient.
We've recently been working through a detailed benchmark of TimescaleDB and Clickhouse. The DELETE/UPDATE question has been an intriguing story to follow - and I honestly hadn't considered the GDPR angle.
ATM, Clickhouse is still OLAP focused and their MergeTree implementation does not allow direct DELETE (or UPDATE) of any data. All DELETE/UPDATE requests are applied asynchronously by (essentially) re-writing/merging the table data (it's referred to as a "mutation") without whatever data was referenced in the DELETE/UPDATE. [1]
[1]: https://clickhouse.com/docs/en/sql-reference/statements/alte...