How to update data in ClickHouse (2025 edition)

Mark Needham

ClickHouse has just made updating data significantly more straightforward with a new approach that uses familiar SQL syntax. Instead of rewriting entire data parts like the traditional ALTER TABLE method, this update mechanism creates lightweight "patch parts" that store only the changed values as deltas. These patches are applied at query time and eventually merged in the background, making small, frequent updates much more efficient than before.

• Hands-on demonstration using the UK price paid dataset with 30 million rows
• Understanding how patch parts work under the hood and how they differ from traditional mutations
• Exploring virtual columns like _part, _block_number, and _block_offset that make this possible
• Querying system.parts and system.parts_columns to see exactly what's stored in each part
• Performance considerations: when to use UPDATE syntax vs ALTER TABLE mutations
• Practical examples of single-row and multi-row updates with immediate query results

The key takeaway is knowing when to use each approach. The new UPDATE syntax shines for frequent changes affecting roughly 10% or less of your table, while ALTER TABLE mutations remain the better choice for large-scale updates where you want optimal baseline query performance after the change completes.

Blog posts:

• https://clickhouse.com/blog/updates-in-clickhouse-3-benchmarks
• https://clickhouse.com/blog/updates-in-clickhouse-2-sql-style-updates
• https://clickhouse.com/blog/update-performance-clickhouse-vs-postgresql