Best practices to avoid dangerous mutations

[FedericoCeratto]

Sometimes it is necessary to update the value of a column across a large number of row.
The ALTER TABLE ... UPDATE command presents some risks:

• Data corruption due to bugs (e.g. CORRUPTED_DATA after ALTER TABLE UPDATE on nested data structure. #46345)
• Stuck or failed mutations (e.g. mutations stuck #39885)
• In absence of transaction, incorrect queries can lead to loss of data

Often this needs to be done on tables that are receiving new inserts during the update.

Are there recommended ways to do so safely while minimizing I/O load? Could you please comment on alternatives e.g.
a) Copy the whole table and run a mutation on the copy, then exchange the tables
b) Select+insert the affected rows into a new table, then copy them back and run OPTIMIZE TABLE ... FINAL assuming deduplication can be used
c) Select+insert the affected rows into a new table, run a DELETE mutation on the original table and copy the rows back

Thanks!

[den-crane]

I test all update/delete in a stage environment with the same tables (DDL) and the same version of Clickhouse.

[FedericoCeratto]

@den-crane FWIW we do the same but we are not confident that data corruption bugs or stuck mutations are 100% reproducible across testbeds and production, hence my question.