ClickHouse 26.4: SQL, Speed, and Smarter Indexing

SQL Evolution and Performance Leaps

The ClickHouse 26.4 release continues its trajectory of enhancing SQL compatibility and performance, which is crucial for broader adoption and developer comfort. The inclusion of VALUES as a table expression and PostgreSQL-style EXTRACT units are particularly welcome steps towards making ClickHouse feel more familiar to developers coming from traditional relational databases. The performance optimizations, especially for COUNT DISTINCT on high-core-count machines and the LIKE query acceleration via text indexing, are significant for users dealing with large datasets and complex analytical workloads. The improved EXPLAIN output also adds much-needed clarity for query tuning.

However, while the focus on SQL compatibility is commendable, it's important to remember ClickHouse's core strengths lie in its columnar architecture and analytical processing capabilities, which sometimes diverge from traditional OLTP RDBMS patterns. The effectiveness of features like the LIKE optimization is highly dependent on specific data characteristics and index configurations, requiring careful implementation. The JSONAllValues feature combined with text indexing is a powerful advancement for semi-structured data, but managing and indexing JSON can still introduce complexity. The performance gains for COUNT DISTINCT are explicitly noted to be most impactful on high-core-count machines, meaning users on smaller setups might not see the same dramatic improvements. Overall, this release solidifies ClickHouse's position as a high-performance analytical database, with a continuous effort to bridge the gap with standard SQL practices.

Key Points

• ClickHouse 26.4 introduces 39 new features, 45 performance optimizations, and 238 bug fixes.
• Key SQL compatibility improvements include VALUES as a table expression, EXTRACT with PostgreSQL-style units, and SET TIME ZONE.
• Performance enhancements include faster COUNT DISTINCT (especially on high-core machines) and optimized LIKE queries using text indexes.
• The EXPLAIN command is now more human-readable, showing output columns and join details.
• New functionality like JSONAllValues combined with text indexing allows for more efficient querying of JSON subcolumns.

---

📖 Source: ClickHouse Release 26.4