Get started with Propel's Serverless ClickHouse forever-free plan today. Propel is the only Serverless ClickHouse with a true pay-per-query pricing and instant auto-scaling.

ClickHouse version 24.9 brings a variety of enhancements, new features, and some key fixes that continue to make the ClickHouse more powerful, adaptable, and efficient.

In this post, we'll highlight the major updates, covering changes you need to be aware of as a developer building on ClickHouse. Let's dive right in!

Backward-incompatible changes

1. Named Tuple Access: Expressions like a[b].c are now supported for named tuples and named subscripts in arbitrary expressions. This allows easier JSON processing but introduces a change in how expressions are parsed. If you previously relied on storing tuple element names in columns or aliases, this update may introduce incompatibility issues in very rare edge cases.
2. Replicated Database Restrictions: Creating tables in replicated databases now has new restrictions. Explicit UUIDs and custom Keeper paths are no longer allowed for *MergeTree tables. The setting database_replicated_allow_explicit_uuid was added to enable these controls.
3. Pretty Tuple Formatting Expansion: The print_pretty_type_names setting now also affects the output of SHOW CREATE TABLE, the formatQuery function, and interactive sessions in clickhouse-client and clickhouse-local.

New features

1. Multiple Authentication Methods per User: Users can now have multiple authentication methods, making managing access easier and more flexible. To avoid compatibility issues, set max_authentication_methods_per_user = 1 if you're running a mix of ClickHouse 24.8 and 24.9 instances.
2. Overlay String Functions: The new overlay and overlayUTF8 functions allow you to replace parts of strings more easily. For example, SELECT overlay('Hello New York', 'Jersey', 11) returns "Hello New Jersey".
3. Partition-Level Lightweight Deletes: A new DELETE FROM [db.]table [ON CLUSTER cluster] [IN PARTITION partition_expr] WHERE expr syntax has been added for lightweight deletes within partitions, providing more efficient partition-level data management.
4. Support for Azure and Local Iceberg Tables: Version 24.9 now makes it possible to read Iceberg tables in both Azure and local storage, expanding data lake compatibility.

Performance improvements

1. Optimized JOINs: The performance of JOIN operations has been significantly improved, especially when dealing with dense keys in LEFT or INNER hash joins. This makes joining large datasets faster and more resource-efficient.
2. Filesystem Cache Optimization: Metadata for the filesystem cache is now loaded asynchronously during the boot process, which speeds up restart times, especially for larger deployments.
3. Parallel Merge for uniq Aggregate Function: Aggregations involving the uniq function with a GROUP BY key will now be parallelized, leading to better CPU utilization and faster query execution times.
4. Improved Handling of S3 Queries: Speed improvements have been made for querying public S3 buckets where credentials aren’t available, eliminating the need for unnecessary retries.

Bug fixes

1. Replication and Metadata Consistency: Fixes address several replication issues, including problems with metadata consistency after altering replicated tables and resolving sync issues for replica settings.
2. Corrected JSON Parsing for Nullable Columns: JSON type parsing has been improved, particularly regarding how null values are handled, ensuring proper inference and reducing parsing errors.
3. Parallel Replica Handling: Improvements were made in how parallel replicas handle deduplication to reduce complexity, resulting in faster performance on tables with many partitions.
4. Crash Fixes and Stability: Multiple stability improvements, including fixing crashes in specific Parquet operations and ensuring lag/lead functions work as intended, have been implemented.

Experimental features worth trying

1. Dynamic JSON Path Introspection: New aggregate functions like distinctDynamicTypes, distinctJSONPaths, and distinctJSONPathsAndTypes make it easier to explore the content of JSON columns and understand their type distributions.
2. Improved Refreshable Materialized Views: Enhancements to materialized views include an append mode (REFRESH EVERY 1 MINUTE APPEND) for adding rows incrementally, which avoids the performance penalty of replacing the entire dataset. We like where the refreshable materialized views are going.
3. min_max* Experimental Statistics Type**: A new experimental min_max statistics type helps estimate range predicates over numeric columns, which is especially useful for optimizing query planning.

Summary

ClickHouse version 24.9 delivers significant improvements to security, usability, and performance, alongside new features that enhance the developer experience. If you’re upgrading from 24.8, be sure to pay attention to potential backward-incompatible changes, particularly around tuple expressions and replicated databases.

Try out these new features and let us know how they impact your use cases. And as always, stay tuned for the next round of updates!

****Get started with Propel's Serverless ClickHouse forever-free plan today. Propel is the only Serverless ClickHouse with a true pay-per-query pricing and instant auto-scaling. Contact us to learn more about our volume-based discounts. Visit our pricing page for details.

‍