ClickHouse Release 26.4

Summary

ClickHouse Release 26.4 introduces 39 new features, 45 performance optimizations, and 238 bug fixes, focusing on broader SQL compatibility and indexing improvements. The update expands standard SQL support by allowing VALUES clauses as table expressions with column aliases, PostgreSQL-style EXTRACT units, NATURAL JOIN, and SET TIME ZONE commands. For text search, LIKE and ILIKE pattern queries using splitByNonAlpha tokenizers scan inverted index dictionaries instead of executing full-table scans when the dedicated evaluation setting is active. The release also provides the JSONAllValues function to extract all leaf values from a JSON column into a string array, enabling text skip indexes that significantly reduce scanned data and improve query latency.

Takeaways

1. 1

   ClickHouse 26.4 supports using VALUES as a table expression with aliased columns and adds PostgreSQL-style unit support to the EXTRACT operator.

2. 2

   Queries using LIKE or ILIKE with splitByNonAlpha tokenizers can scan the inverted index dictionary directly to avoid full-table scans.

3. 3

   The JSONAllValues function extracts all leaf values of a JSON column as Array(String), allowing n-gram text skip indexes to accelerate JSON subcolumn filtering.