Altinity Stable® Build for ClickHouse® 24.3
Here are the detailed release notes and upgrade instructions for version 24.3.5.47. There are a lot of new features, but also many things that changed their behavior. Read these carefully before upgrading.
- Detailed release notes and upgrade instructions for version 24.3 - Read these first!
- 24.3.5.48 FIPS 2024-09-04 - A FIPS-compatible release of version 24.3.5.
Major new features in 24.3 since the previous Altinity Stable release 23.8
Released 2024-07-23
A new release introduces a lot of changes and new functions. Please refer to the full list in the following sections. The following major features are worth mentioning on the front page:
- Analyzer is enabled by default:
- Many complex queries start to work properly.
- Brings consistency and completeness of features.
- Multiple
ARRAY JOIN
in a single query. SAMPLE
can be specified for any table expression inJOIN
.FINAL
can be specified for any table expression inJOIN
.- Up to 5x faster for complex queries with joins.
- Multiple
- As of version 24.3, the analyzer does not support the following experimental features: window views, annoy and usearch indices, and hypothesis constraints.
- Brings consistency and completeness of features.
- Many complex queries start to work properly.
If you experience failed queries that worked in earlier versions, try disabling the analyzer with the allow_experimental_analyzer=0
setting.
- Refreshable Materialized Views: Run the
SELECT
query in the background and atomically replace the table with its result #56946. - Features that are not marked as experimental anymore:
- Query Cache
alter_materialized_view_structure
- undrop_table_query
- Variant Data Type: See
allow_experimental_variant_type
& #58047.
Major Changes that Require Attention
-
Analyzer is enabled by default
- If you experience failed queries that used to work in earlier versions, try disabling the analyzer with
allow_experimental_analyzer=0
setting.
- If you experience failed queries that used to work in earlier versions, try disabling the analyzer with
-
Server settings changed their defaults
max_concurrent_queries
is now 1000 (previously 100). We highly recommend keeping the old value if it worked well before, as the new default can impact server responsiveness and stability if the limit was often reached.concurrent_threads_soft_limit_ratio_to_cores
is now 2. While the new value is generally better, some queries may suffer from reduced parallelism.background_schedule_pool_size
increased from 128 to 512. This new value is generally better, especially when the number of replicated tables is high. But you can observe more thread working in the system.background_fetches_pool_size
increased from 8 to 16. The new default can work better, especially for scenarios with a high number of replicated tables and inserts. However, it can create more pressure on the network card if it has poor bandwidth.index_mark_cache_size
is now enabled and set to 5GB. This change greatly impacts query speed performance if you use skipping indexes, but it may increase ClickHouse memory usage.
-
MergeTree settings changed their defaults
replicated_deduplication_window
increased from 100 to 1000. This may lead to some growth in the Zookeeper data size, especially if there are many replicated tables. You can keep the old default.
Backward Incompatible Changes
avgWeighted
aggregate function no longer supportsDecimal
type arguments. Convert them toFloat
.- IPv6 bloom filter indexes created prior to March 2023 are incompatible with the current version and must be rebuilt.
- Support for experimental Meilisearch was removed due to protocol changes.
- Non-deterministic functions in TTL expressions are now forbidden by default (see
allow_suspicious_ttl_expressions = 1
). ReplacingMergeTree
withis_deleted
flag:clean_deleted_rows
is deprecated. TheCLEANUP
keyword forOPTIMIZE
is not allowed by default (unlessallow_experimental_replacing_merge_with_cleanup
is enabled).- Support for in-memory data parts was fully dropped. If you have used this before, you may need to perform extra steps before the upgrade (check #61127).
extract_kvp_max_pairs_per_row
renamed toextract_key_value_pairs_max_pairs_per_row
.- The order of arguments for the
locate
function changed to match MySQL (seefunction_locate_has_mysql_compatible_argument_order = 0
). SimpleAggregateFunction
is now forbidden in the tableORDER BY
(seeallow_suspicious_primary_key
).geoDistance
,greatCircleDistance
, andgreatCircleAngle
now returnFloat64
instead ofFloat32
(seegeo_distance_returns_float64_on_float64_arguments
).query_cache_store_results_of_queries_with_nondeterministic_functions
marked obsolete and replaced byquery_cache_nondeterministic_function_handling
#56519.
Upgrade Notes
It is always a good idea to test a new release in a dedicated environment, or at least to carefully check the list of backward incompatibilities before installing the new release.
For a rolling upgrade, consider disabling the new analyzer (set allow_experimental_analyzer=0
) to prevent distributed queries from failing .
Known Issues in 24.3.5
The following problems are known to exist in 24.3.5:
- S3Queue (+ parallel and distributed processing) - This was marked as non-experimental in 24.3, but we don’t recommend using it on this version due to a memory leak and other stability issues. Consider using 24.8 or newer if you need S3Queue functionality.
- 24.2 Columns of parameterized views don’t show up in system.columns
- 24.2 Prewhere over engine=Merge over Distributed was broken
- 23.11 window functions can produce unexpected result - fixed in 24.7, is going to be backported
- dictGetOrDefault random result - fixed in 24.6, not backported
- Skip Index ‘set’ is not used with IN operator - fixed in 24.6, not backported
- Size of filter doesn't match size of column - fixed in 24.5, not backported
- ClickHouse may crash if it reaches the thread limit under high concurrency for an extended period - fixed in 24.4; workaround is to avoid excessive concurrency that exceeds the 10,000 thread limit.
A lot of issues have been discovered in the new analyzer after it was enabled by default. Many of them have been fixed, but some are still open:
https://github.com/ClickHouse/ClickHouse/issues?q=is%3Aopen+is%3Aissue+label%3Aanalyzer
If you experience failed queries that used to work in earlier versions, try disabling the new analyzer with the allow_experimental_analyzer=0
setting.
Other Important Changes
- The
default
user now hasaccess_management
(user manipulation by SQL queries) andnamed_collection_control
(manipulation of named collections by SQL queries) settings enabled by default. The new values are more user-friendly, but consider the security effects. - Some safety limits have been introduced (see also Altinity KB):
max_projections
inmerge_tree_settings
- default 25max_database_num_to_warn
inserver_settings
- default 1000max_materialized_views_count_for_table
inserver_settings
- default 0 (i.e., disabled)
- Utility
clickhouse-copier
has been moved to a separate repository on GitHub: ClickHouse/copier. It is no longer included in the bundle but is still available as a separate download. output_format_orc_compression_method
andoutput_format_parquet_compression_method
changed fromlz4
tozstd
#61817.output_format_orc_string_as_string
,output_format_parquet_string_as_string
, andoutput_format_arrow_string_as_string
are enabled #61817.input_format_parquet_allow_missing_columns
,input_format_orc_allow_missing_columns
, andinput_format_arrow_allow_missing_columns
are enabled.log_processors_profiles
can create some extra data insystem.query_log
.- Prefetch settings changed:
filesystem_prefetches_limit
: 0 => 200filesystem_prefetch_min_bytes_for_single_read_task
: 8388608 => 2097152allow_prefetched_read_pool_for_remote_filesystem
: 0 => 1
load_metadata_threads
: 1 => 16. This will make the server start up faster.max_execution_time
is now split into two settings:max_estimated_execution_time
andmax_execution_time
#58402.- The Ordinary database engine is deprecated. You will see a warning in
clickhouse-client
if your server is using it. directory_monitor
settings were renamed todistributed_background_insert
, but old settings will continue to work (exist as aliases).
Let’s Install!
Linux packages can be found at https://packages.clickhouse.com for upstream builds, and at https://builds.altinity.cloud for Altinity Stable builds.
Docker images for the upstream version should be referenced as clickhouse/clickhouse-server:24.3.5.46
.
Altinity Stable build images are available as altinity/clickhouse-server:24.3.5.47.altinitystable
.
For more information on installing ClickHouse from either the Altinity Builds or the upstream builds, see the ClickHouse Altinity Stable Builds Install Guide.
Please contact us at info@altinity.com if you experience any issues with the upgrade.
Appendix - New Features
New Syntax
DROP TABLE IF EMPTY
- Negative Positional Arguments (
ORDER BY -1 DESC
) ORDER BY *
- Sort by all selected columns.SHOW FUNCTIONS
/SHOW MERGES
/SHOW SETTING
/SHOW SETTING max_block_size
ALTER TABLE test ADD INDEX ix (col) TYPE minmax, MATERIALIZE INDEX ix
- can be done in one query now.ATTACH PARTITION ALL
CHECK ALL TABLES
PASTE JOIN
- join two tables by position-wise (1st row on the left with the first row on the right, etc.).ALTER TABLE {name:Identifier} ATTACH PARTITION {x:String}
(now accept query parameters).- Plural form in intervals, i.e.,
INTERVAL 2 HOURS
=INTERVAL 2 HOUR
.
ORDER BY mortonEncode(x, y)
can now properly work (analyze_index_with_space_filling_curves=1
) #55642.
Security Features
- Definer for View/Materialized View: This allows executing selects/inserts from views without explicit grants for underlying tables.
- Temporary User Credentials:
IDENTIFIED BY 'foo' VALID UNTIL '2025-01-01'
- SSH Keys for Authentication.
- SSO for AWS S3 according to
AWS_PROFILE
#54347. - External HTTP Basic authenticator #55199.
- Safer named collections. It is now possible to define which fields can not be overwritten in function calls #55782.
MergeTree-Related Features & Improvements
primary_key_lazy_load=1
enabled by default; loads the primary key on first access. It speeds up server startup #60093.- Memory usage for primary key is reduced. Avoid loading in RAM the columns after a cardinal one (
primary_key_ratio_of_unique_prefix_values_to_skip_suffix_columns=0.9
) #60255. - Long column names can be used now. In part Avoid ‘File name too long,’ especially for projections (
replace_long_file_name_to_hash = 1
). - Automatic conversion of merge tree tables of different kinds to replicated engine #57798.
- Columns statistics (
allow_statistic_optimize = 1
) to orderprewhere
conditions better #53240. - Projections: SET
force_optimize_projection_name = 'foo'
,preferred_optimize_projection_name = 'bar'
. - Table function
mergeTreeIndex
to inspect indexes. - New virtual columns
_part_offset
and_block_number
. - Indices on ALIAS columns.
- Optimizations for FINAL.
Mutations
ATTACH PARTITION
from a different disk: Similar toMOVE PARTITION TO DISK/VOLUME
, but works between tables.apply_deleted_mask=0
: Allows seeing the rows deleted by light-weight delete.APPLY DELETED MASK
: Remove deleted records withoutOPTIMIZE
query.- Exponential backoff logic for mutation retries #58036.
Compression Codecs
- GCD Codec “greatest common denominator” #53149.
min_compress_block_size
andmax_compress_block_size
can now be specified at the column levelCREATE TABLE ... (col String SETTINGS (min_compress_block_size = 81920, max_compress_block_size = 163840)
. #55201.- Support for
LZ4HC(2)
(previously worked asLZ4HC(3)
). ZSTD_QAT
codec for Intel QuickAssist Technology hardware acceleration.
Aggregate Functions
groupArraySorted(n)(value)
: Useful for “top N” queries without full sorting.quantileDD
(+quantilesDD
,medianDD
): Based on the DDSketch #56342.groupArrayIntersect
.approx_top_count
,approx_top_k
,approx_top_sum
: Similar totopK/topKWeighed
functions but include count/error statistics #54508.ArgMin
,ArgMax
as combinators: Apply aggregate function to the set of values where another value is the maximum in a group #54947.- Settings:
anyLast_respect_nulls
,any_respect_nulls
,any_value_respect_nulls
.
Timeseries Functions
largestTriangleThreeBuckets (Or lttb) lttb(n)(x, y)
: Downsampling time-series–like data while retaining the overall shape and variability in the data.seriesPeriodDetectFFT
: Find the main frequency of a signal/series period.seriesDecomposeSTL()
: Decomposes a time series into a season, a trend, and a residual component #57078 #58961.seriesOutliersDetectTukey
: Detect outliers in series data using Tukey’s Fences algorithm #58632.
Array Functions
arrayFold
:SELECT arrayFold((acc, value...) -> new_acc, initial_acc, arr...)
arrayRandomSample(range(1000), 10)
.- Multiply/divide on scalar #54608.
arrayShingles
:['ClickHouse','is','a','good','database'] -> arrayShingles(tok, 3) -> [['ClickHouse','is','a'],['is','a','good'],['a','good','database']]
.
Other Functions
SHA-512/256
.formatQuery
,formatQuerySingleLine
.byteSwap
.SQID
: Short Unique Identifiers from NumbersSELECT sqid(123456789); rDOOFW
.
Strings / JSON Functions
jsonMergePatch
.- Comparing strings:
levenshteinDistance
,damerauLevenshteinDistance
,jaroSimilarity
,jaroWinklerSimilarity
. concat
&format
with arbitrary types (without casting to Strings).
URL & HTML Related Functions
punycodeEncode
,punycodeDecode
,idnaEncode
,idnaDecode
.decodeHTMLComponent
.
Other
- Non-Constant Time Zones.
- Size-Capped Memory Tables:
ENGINE = Memory SETTINGS max_bytes_to_keep = '1G'
(alsomax_bytes_to_keep
,max_rows_to_keep
,min_bytes_to_keep
,min_rows_to_keep
). - Configurable dashboards: Queries for charts are now loaded using a query, which by default uses a new
system.dashboards
table #56771. - Options
partial_result_update_duration_ms
andmax_rows_in_partial_result
to show updates in real-time during query execution #48607. generate_series
table function.- Many improvements in parallel replicas functionality.
- Support for read-only connection to ZooKeeper server #57479.
hostname
column in all system log tables #55894.- Several improvements in (experimental) s3 zero copy feature.
Distributed Queries
distributed_insert_skip_read_only_replicas
: Skip read-only replicas forINSERT
intoDistributed
engine.- Settings for the
Distributed
table engine can now be specified in the server configuration file (similar toMergeTree
settings), e.g.,<distributed> <flush_on_detach>false</flush_on_detach> </distributed>
. - Ability to override initial
INSERT
settings viaSYSTEM FLUSH DISTRIBUTED
#61832.
HTTP Protocol
- Speed up HTTP output.
- Separate metrics of network traffic for each server interface:
InterfaceHTTPSendBytes
,InterfaceHTTPReceiveBytes
,InterfaceNativeSendBytes
. getClientHTTPHeader
function.- Refactoring of the code around HTTP/HTTPS connections (+ introducing some limits), making HTTP connections reusable #58845.
- Allow disabling of
HEAD
request beforeGET
request #54602.
Operational / Maintenance
- Allow overwriting
max_partition_size_to_drop
andmax_table_size_to_drop
server settings in query time #57452. - Asynchronous loading of tables:
async_load_databases
for asynchronous loading of databases and tables. See alsosystem.async_loader
,max_waiting_queries
#49351 #61053. - Adding new disk to storage configuration without restart.
alter_move_to_space_execute_async
allow to start moves in the backgroundmax_mutations_bandwidth_for_server
&max_merges_bandwidth_for_server
#57877.- IO Scheduling support for remote disks #47009 #54618.
- Composable configurations of virtual filesystems on top of object storages and metadata storages.
ALTER TABLE table FORGET PARTITION partition
: Clean up ZooKeeper nodes related to a partition which will not be used.volume_priority
instorage_configuration
.- Allow loading AZ info from a file #59976.
S3 / Object Storage Improvements
- Prefetches enabled by default #53709.
- Adaptive timeouts #56314.
key_template
option to adjust data layout inside the bucket.- Introspection table
system.blob_storage_log
. - S3 Express One Zone support.
SQL & MySQL Compatibility
- MySQL Binlog Client for
MaterializedMySQL
: One binlog connection for many databases #57323. - Allow skipping engine (default_table_engine=MergeTree), Zookeeper
path & replicas (default_replica_name, default_replica_path), or
ORDER BY
(create_table_empty_primary_key_by_default
, no index will be used). - Default parameters for Decimal:
DECIMAL(P)
,DECIMAL
. New functions:TO_DAYS
(
toDaysSinceYearZero), addDate
, subDate
. Function aliases: STD
(stddevPop), current_user
.
information_schema
:table_comment
,table_collation
,data_length
,statistics
.columns.extra
,tables.table_rows
,tables.data_length
,key_column_usage
,referential_constraints
(for QuickSight).- Minimal support for prepared statements (for Tableau).
- String arguments for add/subtract date/time and
toDayOfWeek
. date_trunc
supports case-insensitive unit names.- Enums and strings are cast to a common type if needed (+
substring
supports the Enum data type). - Enable group of settings when clients are connected via MySQL protocol (
prefer_column_name_to_alias = 1
,mysql_map_string_to_text_in_show_columns
, andmysql_map_fixed_string_to_text_in_show_columns
). Helps BI tools like QuickSight to work.
Formats
- NumPy as input format:
SELECT * FROM 'data.npy'
. - Autodetect
JSON/JSONEachRow
. - Valid JSON/XML on exceptions for some formats (especially HTTP).
- ‘Pretty’ formats look better:
output_format_pretty_row_numbers=1
.output_format_pretty_highlight_digit_groups=1
.output_format_pretty_single_large_number_tip_threshold=1000000
.
- Control for compression level:
output_format_compression_level = 6
,output_format_compression_zstd_window_log = 26
. - Inline template format (
format_template_resultset_format
+format_template_row_format
). - Automatic detection of formats when it can not be inferred from a file extension
- Type inference for nested JSONs.
- Support for well-known Protobuf types.
Integrations
_size
virtual column: In external tables: s3, url, file, hdfs, azureBlobStorage, etc.fileCluster
for parallel query across distributed file systems.- Table function
merge
with a single argument (first database argument can be skipped). - Support for
nats_handle_error_mode
/rabbitmq_handle_error_mode
/handle_error_mode
similar tokafka_handle_error_mode
.
Important Performance Improvements
- Reducing lock contention for high concurrent loads, see ClickHouse Lock Contention
- Optimizations for queries with
FINAL
:- Avoid merging data across different partitions if the table’s partition key consists of columns from the primary key.
- Find data parts with non-intersecting ranges of the primary key to avoid merging data from those parts.
- Condition pushdown for ORC row groups (see
input_format_orc_filter_push_down
). - Faster window functions (see
query_plan_preserve_num_streams_after_window_functions = 1
) #50771, #39631. - Parallel flush for buffer tables: Moving data from
engine=buffer
to the target table was sequential even if it had several layers. - Auto-adjustment for asynchronous insert timeouts #58486.
Keeper
- Support for compression in Keeper protocol #54957.
- HTTP endpoint for checking if Keeper is ready to accept traffic #55876.
- Implementation of
reconfig
,sync
, andexists
commands forkeeper-client
#54201. - 4-letter command for yielding/resigning leadership.
- ClickHouse Keeper reports its running availability zone at
/keeper/availability-zone
path when running on AWS environment.
Embedded Backup
backup_log
#53638.- External Python tool to view backups #56268.
- Backup & restore support for AzureBlobStorage #56988.
- Backups as tar archives #59535.
- S3 server-side for
BACKUP/RESTORE
andclickhouse-disks copy
. RESTORE TABLE ON CLUSTER
preserves UUID #53765.- Many other fixes (~50 in total).
Changes in Altinity Stable build Compared to Upstream Build
Altinity Stable builds for ClickHouse are open source and are based on the upstream LTS versions. Altinity.Stable 24.3.5 is based on upstream 24.3.5.46-lts, but we have additionally backported several fixes:
- Fix the crash loop when restoring from backup is blocked by creating an MV with a definer that hasn’t been restored yet. (#64595 by @pufit)
- Fix moving partition to itself #62459 (#62524 by @helifu)
- Fix detection of number of CPUs in containers. In the case when the ‘root’ cgroup was used (i.e. name of cgroup was empty, which is common for containers) ClickHouse was ignoring the CPU limits set for the container. (#66237 by @filimonov)
Also, please refer to the interim release notes from the development team available at the following urls:
- https://github.com/ClickHouse/ClickHouse/blob/master/docs/changelogs/v23.9.1.1854-stable.md
- https://github.com/ClickHouse/ClickHouse/blob/master/docs/changelogs/v23.10.1.1976-stable.md
- https://github.com/ClickHouse/ClickHouse/blob/master/docs/changelogs/v23.11.1.2711-stable.md
- https://github.com/ClickHouse/ClickHouse/blob/master/docs/changelogs/v23.12.1.1368-stable.md
- https://github.com/ClickHouse/ClickHouse/blob/master/docs/changelogs/v24.1.1.2048-stable.md
- https://github.com/ClickHouse/ClickHouse/blob/master/docs/changelogs/v24.2.1.2248-stable.md
- https://github.com/ClickHouse/ClickHouse/blob/master/docs/changelogs/v24.3.1.2672-lts.md
24.3.5.48 Altinity FIPS
Released 2024-09-04
This is a FIPS-compatible release of version 24.3.5.
Changes compared to 24.3.5.47.altinitystable
Improvement
- Building BoringSSL ver fips-20210429 (853ca1ea1168dff08011e5d42d94609cc0ca2e27) according to FIPS-140-2 Security Policy 4407, based on build scripts from Golang version go1.22.5
- Added FIPS_CLICKHOUSE to system.build_options
- Modified ClickHosue keeper to use full range of openSSL options (same as ClickHouse does) for Raft connections using ubuntu:22.04 as a base for clickhouse-server docker images instead of ubuntu:20.04
Build/Testing/Packaging Improvement
- CI/CD for FIPS mode
- Other fixes for CI/CD
Full Changelog: v24.3.5.47.altinitystable...v24.3.5.48.altinityfips
Packages
Available for AMD64 from builds.altinity.cloud as either .deb, .rpm, or .tgz
Docker images
Available for AMD64: altinity/clickhouse-server/24.3.5.48.altinityfips