ALTER TABLE: Modifying Table Data
- Exercise caution when doing delete, update, and mutation operations.
The update and delete of standard SQL statements are synchronous operations. That is, the client needs to wait for the server to return the execution results (usually an int value). In contrast, the update and delete of ClickHouse are asynchronous operations. When an update statement is processed, the server immediately returns the request status: success or fail, while the operation is not complete. At that time, the update request is accepted and queued in the background. As a result, the operation may be overwritten, and atomicity of operations cannot be ensured.
To solve this problem, you are advised to use the ReplacingMergeTree, CollapsingMergeTree, and VersionedCollapsingMergeTree engines to update and delete data. For details, see https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/collapsingmergetree.
- Try to avoid adding or deleting data columns.
Plan the number of columns for future use, reserve enough columns to avoid a large number of alter table modify operations during service running in the production system. Otherwise, unpredictable performance problem and data inconsistency may occur.
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.
