ApsaraDB for ClickHouse:Release notes for community-compatible edition 26.3

Materialized CTE

Version 26.3 LTS introduces the materialized CTE syntax. By using the MATERIALIZED keyword, ClickHouse stores CTE results in a temporary in-memory table to avoid redundant computation and improve the execution efficiency of complex reporting and multi-dimensional analysis SQL queries.

Example:

SET enable_materialized_cte = 1;

WITH top_users AS MATERIALIZED (
    SELECT user_id, count() AS cnt
    FROM events
    GROUP BY user_id
    ORDER BY cnt DESC
    LIMIT 1000
)
SELECT *
FROM top_users
INNER JOIN (SELECT user_id FROM top_users WHERE ...) ON ...;

Intelligent JOIN reordering for all JOIN types

The cost-based optimizer (CBO) in 26.3 LTS now supports automatic Build/Probe side swapping for LEFT ANTI JOIN, SEMI JOIN, and FULL JOIN, in addition to standard INNER JOIN and LEFT/RIGHT JOIN. The optimizer automatically builds the hash table from the smaller side based on table statistics, preventing out-of-memory (OOM) errors caused by improper manual SQL ordering.

Sharded Map

For scenarios where the Map type stores user profile tags and dynamic features, version 26.3 LTS introduces a bucketed physical serialization layout. Single-key lookups improve by 2x to 49x because data within the Map is hash-bucketed by key.

Example:

CREATE TABLE user_profiles (
    id UInt64,
    attributes Map(String, UInt64)
) ENGINE = MergeTree
ORDER BY id
SETTINGS map_serialization_version = 'with_buckets', max_buckets_in_map = 32;

Wide table TTL cleanup: Vertical merge

Version 26.3 LTS introduces a vertical merge algorithm for TTL DELETE operations. The merge prioritizes primary key and TTL column filtering without reading irrelevant wide columns, which significantly reduces I/O and memory overhead during background data cleanup.

Adaptive lossless floating-point codec (ALP)

A new lossless floating-point compression codec CODEC(ALP, ZSTD) is available. ALP (Adaptive Lossless floating-Point) is optimized for Float32 and Float64 data in industrial monitoring and time-series metrics scenarios, delivering better compression ratios and decompression speed than the classic Gorilla encoding.

Query execution and optimizer

• Query condition cache, expression JIT compilation, and JOIN runtime filters are enabled by default to reduce repeated scans and large table JOIN overhead.

• RIGHT JOIN and FULL JOIN use ConcurrentHashJoin, with up to 2x performance improvement in some scenarios. A new JOIN ordering optimization automatically reorders multi-table JOINs based on statistics.

• Primary key and partition key index pruning is extended: primary keys support arbitrary deterministic expressions for data skipping, and partition keys wrapped in deterministic function chains can still be pruned.

• ORDER BY ... LIMIT N queries can significantly reduce scanned rows through skip indexes and dynamic threshold filters.

Storage engine and data skipping

• Parquet reader v3 is enabled by default with page-level filter pushdown and PREWHERE support. Data lake reads automatically adjust pipelines based on processing threads, achieving approximately 40x improvement on multi-core machines.

• Streaming skip index filtering during reads is enabled by default. Text indexes support more predicate forms and can be used in PREWHERE.

• In heavy partition pruning scenarios, SELECT queries on tables with 10,000+ parts are up to 8x faster. Iceberg tables support PREWHERE optimization and async metadata prefetching.

Distributed and parallel execution

• Parallel distributed INSERT SELECT is enabled by default and executed independently on each shard. Distributed IN subqueries automatically add DISTINCT to reduce cross-shard temporary data transfer.

• Distributed index analysis supports SharedMergeTree and shared storage scenarios. Parallel replicas support lazy materialization with improved load distribution to reduce long-tail latency.

Functions and indexes

• LIKE and regular expressions are automatically rewritten to more efficient implementations and enabled by default. StringZilla accelerates case-sensitive string search, and SIMD dynamic dispatch accelerates logical functions and boolean column conversion.

Memory, observability, and networking

• Mark, uncompressed, and page caches use independent jemalloc arenas to reduce fragmentation. jemalloc dirty pages are cleaned in independent threads. System log tables add minmax and bloom_filter indexes to accelerate troubleshooting.

• Block serialization and compression in distributed queries can be offloaded to pipeline threads to improve large-volume data transfer efficiency.

Pre-upgrade preparation

1. Clone or test environment verification: Deploy the new version in a cloned or test environment first to evaluate the impact of version changes on existing configurations and queries.

2. Check dependency compatibility: Confirm the compatibility of client drivers and third-party tools with the new version.

Important notes

• Experimental feature validation: Materialized CTE and Polyglot dialects are marked as Experimental in this version. We recommend that you validate these features in a test cluster before using them in production.

• Performance regression testing: After the upgrade, compare the execution time of key queries to ensure that optimizations such as Parquet cache, inverted index, and JOIN reordering take effect.

• Monitoring and alerting adjustment: Adjust monitoring thresholds based on new metrics and changes.