13 ms·

(Timescale DevRel here) We've recently been working through a detailed benchmark of TimescaleDB and Clickhouse. The DELETE/UPDATE question has been an intrigui

(Timescale DevRel here) 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/alter/#synchronicity-of-alter-queries https://clickhouse.com/docs/en/sql-reference/statements/alte...

nezirus 5y ago
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.
hkolk 5y ago
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
kwillets 5y ago
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.
- ec109685 5y ago
  The other option is to use row level encryption and throw away the keys when a user requests a delete.
DevKoala 5y ago
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.

nezirus 5y ago

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.

hkolk 5y ago

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

kwillets 5y ago

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.

ec109685 5y ago
The other option is to use row level encryption and throw away the keys when a user requests a delete.

DevKoala 5y ago

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.