Notes from ClickHouse release webinars, 22.9 to 23.8

The ClickHouse team runs a monthly release call: a walk through the new version’s features, usually with a few community projects and a Q&A at the end. These are my notes from the twelve calls between 22.9 and 23.8, a year of releases that included parameterized views, lightweight deletes, the query result cache, parallel replicas and the first lakehouse readers.

They are notes, not a changelog. They keep the headline items of each call, say in a sentence what each is for, and leave out the integrations news and the cloud announcements. The official release posts and changelogs, linked per release, are the authority for exact behaviour and settings. When an item is marked experimental it was experimental in that release; check the current docs before relying on it.

22.9 (September 2022)

• UNION, INTERSECT and EXCEPT with DISTINCT. The set operators accept the explicit DISTINCT and ALL forms, closing a gap with standard SQL.
• Insert quorum. Work on quorum inserts into replicated tables, which wait for several replicas to confirm a write.
• Settings that read-only users may change. A way to mark individual settings as changeable even for users restricted to reading.
• Better JSON formats and saving of parsing errors. Input and output formats for JSON received a round of improvements, and rows that fail to parse can be kept for inspection instead of only aborting.
• Composable cache. A caching layer that sits on top of virtual filesystems such as object storage disks. This is also where the call explained how virtual filesystems are put together.
• Experimental vector search with Annoy, and the KeeperMap table engine, which stores key-value data in Keeper.
• Release: v22.9 on GitHub.

22.10 (October 2022)

• Control over merging and resetting settings to their default values.
• Fuzzy history search in clickhouse-client, much like the reverse search of a shell.
• Faster reconnection to Keeper, Keeper snapshots stored in S3, and backups to S3. Together with the write-once-read-many S3 disks, this was the storage-and-recovery release.
• Composable protocols. The server’s listeners (native, HTTP, with or without TLS, proxy protocol) are defined as combinable layers in configuration.
• system.asynchronous_insert_log, a log for asynchronous inserts, and more Accept-Encoding values for HTTP.
• mortonEncode and mortonDecode, space-filling-curve functions that help when ordering by several dimensions at once.
• Release: v22.10 on GitHub.

22.11 (November 2022)

• Composite time intervals. Intervals of different units can be combined.
• ** glob for recursive path matching in file and object-storage readers.
• Functions for Spark compatibility and a number of convenience functions.
• Retries on INSERT (the keeper-related insert_keeper_max_retries family of settings), which makes inserts survive short Keeper outages.
• Data lakes: SELECT support for Apache Hudi and Delta Lake tables.
• Read-only S3 disk.
• Release: ClickHouse 22.11 release post.

22.12 (December 2022)

• SQL syntax: numeric literals with underscores (1_000_000), FROM table SELECT columns, GROUP BY ALL, concat_ws, and SQL UDFs usable in CREATE queries.
• Grace hash join, a join algorithm that spills partitions to disk so a join larger than memory can still complete, as an alternative to the memory-bound hash join and to the slower partial_merge.
• Operations: a system.moves table, a Prometheus endpoint for ClickHouse Keeper, and constraints on MergeTree settings.
• Security: password complexity rules and cleansing of credentials in logs.
• BSON import and export.
• Release: ClickHouse 22.12 release post.

23.1 (January 2023)

• Parameterized views. A view can take parameters, which turns it into something close to a stored query template.
• Query result cache (experimental in this release), regexp tree dictionaries and inverted full-text indices (also experimental).
• ARRAY JOIN with Map, the age function, SQL-standard hex and binary literals.
• Format work: header autodetection for TSV and CSV, reading structs from Parquet and ORC, extended streaming formats.
• Faster loading of data parts at startup and memory-mapped reads for the File engine.
• Release: ClickHouse 23.1 release post.

23.2 (February 2023)

• NTILE window function and corrMatrix for correlation matrices.
• generateULID, arrayShuffle and arrayPartialSort.
• Multi-stage PREWHERE, where conditions are evaluated in several filtering steps so that expensive columns are read for fewer rows, enabled through a setting in this release.
• Out-of-band FINAL, a partition key usable for GROUP BY optimisation, and new metrics in system.part_log.
• io_uring as a read method, Apache Iceberg reads, and dynamic disks defined in the query rather than the configuration.
• Release: ClickHouse 23.2 release post.

23.3 LTS (March 2023)

• UNDROP TABLE. A dropped table can be brought back within a limited time window.
• Lightweight deletes. DELETE FROM that marks rows instead of rewriting parts at once, now promoted for general use.
• Parallel replicas (the replicas of one shard cooperate on one query), and parallel replicas with dynamic shards that split the work with a custom key.
• Temporary tables with an engine, server settings introspection, nested dynamic disks, compressed marks in memory and faster backups.
• MySQL compatibility improvements, parseDateTime, SSL certificate authentication in the native protocol, and partial results on query cancellation.
• Release: ClickHouse 23.3 release post.

23.4 (April 2023)

• Trailing comma before FROM, so lists of columns are easier to edit.
• Settings on the command line can be spelled with dashes instead of underscores.
• New functions: quantileGK, kolmogorovSmirnovTest, SOUNDEX, extractKeyValuePairs, and replace with non-constant needles.
• SHOW COLUMNS and the ParquetMetadata format, plus faster Parquet reading.
• Asynchronous connections to replicas, which shortens the time to connect when some replicas are slow.
• Release: v23.4 on GitHub.

23.5 (June 2023)

• CASE compatibility, INTO OUTFILE ... APPEND, ATTACH from temporary tables, SHOW INDEX, and a generateRandom that needs no structure argument.
• Azure Blob Storage table function and urlCluster.
• clickhouse-keeper-client, an interactive client for Keeper.
• New system tables, a batch of security improvements, and lower memory use in background operations.
• Release: ClickHouse 23.5 release post.

23.6 (June 2023)

• All data types in transform, INTO OUTFILE ... TRUNCATE, skipping empty files on import, and renaming files after processing.
• Connection strings for clickhouse-client and the session_timezone setting.
• MongoDB integration for newer server versions and Redis access.
• Relaxed “too many parts” handling, fewer background threads in ReplicatedMergeTree, and faster sorting of almost-sorted data.
• Release: ClickHouse 23.6 release post.

23.7 (July 2023)

• Functions: initcap, hasSubsequence, firstLine, arrayJaccardIndex.
• Operations: SYSTEM STOP LISTEN, custom disks for Keeper and dynamic reconfiguration of Keeper.
• Import and export: CSV with a variable number of columns and the RowBinaryWithDefaults format.
• Parquet writing improvements, sparse columns enabled by default, and further work on multi-stage PREWHERE.
• Release: ClickHouse 23.7 release post.

23.8 LTS (August 2023)

• Arithmetic on vectors (tuples) and concatenation of tuples.
• Defaults for cluster and clusterAllReplicas, hints for mistyped database and table names, TRUNCATE DATABASE, azureBlobStorageCluster.
• File reading performance: counting rows from metadata without scanning, skipping files by filtering on their names, and data skipping inside files.
• Direct import from archives and streaming consumption from S3 with a queue-like table.
• SharedMergeTree in ClickHouse Cloud, a reworked advanced dashboard in the web UI, and JIT compilation for ARM.
• Release: ClickHouse 23.8 release post.

What stands out looking back

Reading the twelve lists together shows three currents.

• SQL catching up with itself. GROUP BY ALL, INTERSECT/EXCEPT DISTINCT, NTILE, parameterized views, SHOW COLUMNS, hex literals, trailing commas. None of these is a performance feature, and together they remove daily friction.
• Files and object storage as first-class. Glob improvements, archives, metadata-based counts, Parquet reading and writing, Hudi, Delta Lake and Iceberg readers, Azure and S3 queue-style access. ClickHouse became a query engine for data it does not own.
• Operations getting safer. Backups to S3, UNDROP, Keeper reconnect and reconfiguration, insert retries, Prometheus endpoints, new system tables for moves and async inserts.

The release calls are recorded and published on ClickHouse’s video channel; the official changelogs are in the ClickHouse docs.