ClickHouse 26.3: Materialized CTEs & Async Inserts Go Live

Materialized CTEs Power Up Performance

ClickHouse's 26.3 release continues its rapid development pace, bringing substantial improvements that underscore its commitment to performance and developer experience. The introduction of materialized CTEs is a standout feature, directly addressing performance bottlenecks in complex queries by ensuring subqueries are evaluated only once and stored. This, coupled with JOIN reordering for ANTI, SEMI, and FULL joins, signals a mature approach to query optimization, moving beyond basic indexing and toward sophisticated query planning. The decision to enable async inserts by default is a pragmatic move, simplifying adoption and immediately benefiting users who previously had to configure this manually, thereby abstracting away a common complexity for high-throughput ingestion.

While the release is packed with positive advancements, the reliance on explicit settings like enable_materialized_cte=1 for certain features, even if enabled by default in the future, might still lead to initial confusion for users upgrading. The documentation clearly explains the benefits and how to use these features, but a more seamless integration or clearer upgrade path for these settings would be beneficial. The vertical merge optimization for TTL DELETEs is a highly specialized but important improvement for specific workloads, particularly those dealing with wide tables and time-series data, highlighting ClickHouse's deep understanding of its target use cases. The overall direction points towards ClickHouse becoming an even more robust and user-friendly analytical database, capable of handling increasingly complex data scenarios with greater efficiency.

Key Points

• Materialized CTEs are now available, allowing common table expressions to be materialized into temporary tables for improved performance on repeated use.
• JOIN reordering has been extended to ANTI, SEMI, and FULL joins, further optimizing query execution plans.
• Async inserts are now enabled by default, simplifying high-throughput data ingestion by handling batching on the server-side.
• The pretty and compact settings for the EXPLAIN clause offer enhanced visualization of query plans.
• A new naturalSortKey function provides human-friendly sorting for version numbers and similar string formats.
• JSONExtract functions now natively support the JSON data type, not just strings.
• Vertical merge optimization is now available for TTL DELETE operations, improving memory efficiency for wide tables.
• The release includes 27 new features, 40 performance optimizations, and 202 bug fixes, reflecting ongoing development.

---

📖 Source: ClickHouse Release 26.3