Company
Date Published
Author
Tom Schreiber
Word count
4059
Language
English
Hacker News points
None

Summary

ClickHouse, a columnar database known for its high performance in analytical queries, has introduced standard SQL UPDATE capabilities that leverage lightweight patch-part updates to significantly enhance speed, achieving up to 1,000× faster performance than traditional mutations. This innovation is part of a series exploring fast updates in ClickHouse, detailing how the system efficiently implements row-level changes using engines like ReplacingMergeTree and CoalescingMergeTree, and now, through declarative SQL-style UPDATEs that utilize patch parts. Extensive benchmarking reveals ClickHouse's new SQL UPDATEs not only match PostgreSQL in point updates but can be up to 4,000× faster in bulk changes. By storing only the differences in small, quickly applied patches, these updates maintain query speed and are highly suitable for high-frequency, small-scale changes, contrasting with the slower, full-column modifications of classic mutations. The benchmarks, conducted on a large-scale dataset with reproducible scripts, demonstrate ClickHouse's capability to handle update patterns traditionally seen in row-store databases, all while maintaining its core analytical strengths.